• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Cost is high, but query is faster. how?

Dear Experts,

In my application, it is full of queries having cost > 2000.

But application is still faster,

which criteria / which query i should consider for tuning.?
Why the cost is high and how the query is fast?
2 Solutions
cost doesn't represent speed

it's an internal number that has no meaning outside of the optimizer.

you could consider it a design bug that the number is exposed at all

also even if you are to try to decipher some meaning from cost, remember that the optimizer is a collection of estimates and sometimes it's wrong so, again, don't rely on it.
Gerwin Jansen, EE MVETopic Advisor Commented:
>> But application is still faster
Faster than what? Before you made some changes? If so, what were the changes? Do you have indexes, do you gather statistics on a regular basis?
sakthikumarAuthor Commented:
Yes statistics are gathered on regular basis, but index might not be available.

So how can I evaluate the performance of the query and consider for tuning.?
where I can get the poor performance queries?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

johnsoneSenior Oracle DBACommented:
The best place to get "poor performance" queries is the users.  If they aren't complaining about it, then it isn't a problem.

What you think is statistically a poor performing query is within an acceptable limit to a user.  Why go wasting time on improving a query that nobody will see or care about the result?

You could go through every query that comes through the database and try to improve it.  Seems like a waste of effort.
In the absence of user input, ask your system admins.  If the system load was to grow, what would break first?  Would you run out of memory, cpu, io?  Then check your awr, statspack or other monitoring tools to find the queries that are the biggest consumers of those constrained resources.

or, rather than looking at specific queries for tuning, look at design issues like parse-to-execute ratios on an oltp system.  If you have a high parse ratio then you probably have developers that need to learn to use bind variables.

but - these are secondary to what johnsone said.  Go by your users first, specifically the processes that are costing your company money.  If you see a query that you think is bad but the customers don't care, then move on to something they do care about.

If you have a shared system, then things might get a little more complicated.  If users in db1 are complaining about a memory intensive query, you might want to look at db2 on the same server and tune some queries there that are memory hogs.
jtriftsMI and AutomationCommented:
Cost is essentially an estimated measure of the resources the Optimizer is expected to use when producing an explain plan for a query. There are no true units to the cost.

The problem with the notion of cost, is that it gives the developer or dba the false illusion that small means fast and large means slow. This is not the case.

Neither is it necessarily the case that small means good and large means bad -- though most developers will aim for a lower number in general.

It is entirely possible and in fact happens all the time, that queries with higher costs will return results in less time than those with smaller costs. However, large cost queries place a greater burden on the system when there are lots of them being executed at the same time. Thus, there is a general practice to seek lower cost queries in general.
As a practice of performance tuning of the wider DB, the practice of attacking high cost queries can have an overall positive impact on your DB performance (but as I say, it might not actually speed up specific queries).

In terms of criteria for tuning, you should consider addressing the problem areas. How do you determine this? Do you have processes that are taking a long time to run? Are they getting longer and longer such that jobs are at risk of not completing within their available time windows?

Do you have users complaining that certain actions take too long (or that they execute something then have to get up and go get a coffee)?

Tuning activities can often be a process of user management -- what the focus areas should be often come down to who is evaluating the benefit.
End users want screens to get data returned to them quickly.  
Accountants / finance department would want you to ensure that they do not have upgrade tin (hardware).  
Managers often want their reports to get to them faster.
Executives want their BI data loaded quickly so they are better able to get their strategic reporting from warehouses.

At the end of the day -- you will get the most value out of addressing the issues that affect the most (or the most *important*) people.
If it is the end users who hate using your system because their most common tasks take ages to complete due to poor performance, that is a good candidate.
If the end users love the system, but managers are frustrated because the batch jobs often don't finish before the batch window slips into office hours, you might need to tackle the batch jobs.
If your BI team is complaining that they cannot load the warehouse because your ODS load takes forever, go there.

Knowing your users and communicating with them to understand what makes them tick (and what pisses them off) is key to your role as a consultant. Happy users mean more business, more $$$, more contracts.

From a tuning point of view, it has been commonly believed that 80% of the issues with performance are found at the application layer. in our case that means in SQL, PL/SQL, etc.

After that, you can look at tuning the DB itself. After that you can look at the OS and then H/W.

With any tuning exercise, you need to have a goal in mind. You can tune until the cows come home -- but if the system is already *good enough* leave it alone and find something to do which makes your client happy.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now