OraFAQ Forum: Performance Tuning » Need support for Query performance tuning

Home » RDBMS Server » Performance Tuning » Need support for Query performance tuning (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need support for Query performance tuning [message #527816]

Thu, 20 October 2011 05:42

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..

Report message to a moderator

Re: Need support for Query performance tuning [message #527818 is a reply to message #527816]

Thu, 20 October 2011 05:45

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Need support for Query performance tuning [message #527820 is a reply to message #527818]

Thu, 20 October 2011 05:54

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?

Report message to a moderator

Re: Need support for Query performance tuning [message #527826 is a reply to message #527820]

Thu, 20 October 2011 06:28

John Watson
Messages: 8960
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.

Report message to a moderator

Re: Need support for Query performance tuning [message #527829 is a reply to message #527826]

Thu, 20 October 2011 06:40

onlijob
Messages: 6
Registered: October 2011
Location: india

Junior Member

thanx..

pls clarify, What it means for expensive?

Report message to a moderator

Re: Need support for Query performance tuning [message #527830 is a reply to message #527829]

Thu, 20 October 2011 06:43

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Take a guess, I'm sure you can work it out.

Report message to a moderator

Re: Need support for Query performance tuning [message #527831 is a reply to message #527830]

Thu, 20 October 2011 06:46

onlijob
Messages: 6
Registered: October 2011
Location: india

Junior Member

Does it mean, that lot of resource it utilizing to fetch the data?

Report message to a moderator

Re: Need support for Query performance tuning [message #527856 is a reply to message #527831]

Thu, 20 October 2011 07:29

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.

Report message to a moderator

Re: Need support for Query performance tuning [message #527985 is a reply to message #527856]

Fri, 21 October 2011 02:41

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....

Report message to a moderator

Re: Need support for Query performance tuning [message #527989 is a reply to message #527985]

Fri, 21 October 2011 02:55

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.

Report message to a moderator

Re: Need support for Query performance tuning [message #527992 is a reply to message #527989]

Fri, 21 October 2011 02:59

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.

Report message to a moderator

Previous Topic:	To verify if a row exists (index range scan)
Next Topic:	Tune the query..

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 19:51:29 CST 2024