Home » RDBMS Server » Performance Tuning » Need support for Query performance tuning (Oracle 11g)
Need support for Query performance tuning [message #527816] Thu, 20 October 2011 05:42 Go to next message
onlijob
Messages: 6
Registered: October 2011
Location: india
Junior Member
hello everyone,
pls clarify my doubts regarding the oracle sql tuning.

I got a report which runs for 15 mins, i made the report to be run in 1.30 min in the test instance.
in that i made 2 queries which runs for 25 sec and 27 secs and i made it into a single query and ran in 6 secs.
data

then i came to a fact called COST. the cost is too high in my query nearly 73k. So this query is cant even run in real instance in my office.
I heard the reason is COST. Is it so?
I also the referred that COST is time taken for reading a table. (lik 10ms is a default for a single block read so on....)

if COST is the time, then how my query runs in 6 secs even though the COST is 73k?
Then
I reduced the cost in my query by the use of indexes which are already present in that column.
What if i want to fetch some other column which don't have index and which i should not create index?

In that time Does COST matters? bcz the query runs in milliseconds but the cost is 1600.

What is behind the COST? some says dont consider COST(but my COST is 73K), some says COST is time(but my time is 6sec), Some says COST is resource utilised by database (dats y its taking much time)...

Pls clearify..
Re: Need support for Query performance tuning [message #527818 is a reply to message #527816] Thu, 20 October 2011 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Cost is a scoring system for the amount of work oracle thinks it needs to do. It is not time. It is also only an estimate. If have bad statistics it can be very wrong.
Re: Need support for Query performance tuning [message #527820 is a reply to message #527818] Thu, 20 October 2011 05:54 Go to previous messageGo to next message
onlijob
Messages: 6
Registered: October 2011
Location: india
Junior Member
I believe the statistics are updated and good. But a query runs in 700ms and cost is 1600. Is it odd? Will this cost really impact the performance of my query in real time?

I heard if COST is under 100 then it is good. is it so?

Then how can i reduce the cost of 1600 to under 100 when my query contains non-indexed column?
Re: Need support for Query performance tuning [message #527826 is a reply to message #527820] Thu, 20 October 2011 06:28 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The COST column is not a unit of measurement: it is purely relative, the absolute values have no meaning. So there there is no "good" or "bad". What it tells you is which part of the execution is the most expensive.
Quote:
I heard if COST is under 100 then it is good. is it so?
The person who told you this needs to read the Perfomrance Tuning Guide.
Re: Need support for Query performance tuning [message #527829 is a reply to message #527826] Thu, 20 October 2011 06:40 Go to previous messageGo to next message
onlijob
Messages: 6
Registered: October 2011
Location: india
Junior Member
thanx..


pls clarify, What it means for expensive?
Re: Need support for Query performance tuning [message #527830 is a reply to message #527829] Thu, 20 October 2011 06:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Take a guess, I'm sure you can work it out.
Re: Need support for Query performance tuning [message #527831 is a reply to message #527830] Thu, 20 October 2011 06:46 Go to previous messageGo to next message
onlijob
Messages: 6
Registered: October 2011
Location: india
Junior Member
Does it mean, that lot of resource it utilizing to fetch the data?
Re: Need support for Query performance tuning [message #527856 is a reply to message #527831] Thu, 20 October 2011 07:29 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Your statistics can be good, but the cost can be wrongly high. For ex. there aren't extended statistics in 10g. So if you have several predicates in your query for ex., optimizer has to do assumptions. And they can be wrong regardless of correct statistics for singes predicates.
Re: Need support for Query performance tuning [message #527985 is a reply to message #527856] Fri, 21 October 2011 02:41 Go to previous messageGo to next message
onlijob
Messages: 6
Registered: October 2011
Location: india
Junior Member
When i run the query in test instance(when am the only one using the test database and fetching data) it runs in 6 sec,

but the same query when i run in real time(Company database where 100s of people connected), it taking lot of time... but Those tables are accessed by me oly.


y is it so? If cost is not the factor of this, then what is the reason behind it? How it affect the database and making the fetch slow?

Server machine is high capable and network response is also high....
Re: Need support for Query performance tuning [message #527989 is a reply to message #527985] Fri, 21 October 2011 02:55 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
What does it mean "a lot of time"? The reasons can be different, for ex.
- a different execution plan,
- more disk reads, if you run your sql in business time and so on.

If you sent the both execution plans with the runtime statistics, I can say exactly.
Re: Need support for Query performance tuning [message #527992 is a reply to message #527989] Fri, 21 October 2011 02:59 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Its rare for test/dev databases to have anywhere near the data volumes of productions ones.

If the environments are not mirrored (or near as damnit) then comparisons are of limited value at best.
Previous Topic: To verify if a row exists (index range scan)
Next Topic: Tune the query..
Goto Forum:
  


Current Time: Fri Mar 29 09:57:09 CDT 2024