Home » RDBMS Server » Performance Tuning » Difference between cost and %CPU? (Oracle 10g)
Difference between cost and %CPU? [message #389308] Sat, 28 February 2009 05:54 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi, I ve attached the plan o/p of 2 different queries giving same results. 1st shows less cost but high %CPU and the second shows the opposite. Which is the best one? Pls suggest.
Thanks,
Senthil
  • Attachment: Query.txt
    (Size: 5.61KB, Downloaded 1335 times)

[Updated on: Sat, 28 February 2009 07:22]

Report message to a moderator

Re: Difference between cost and %CPU? [message #389344 is a reply to message #389308] Sat, 28 February 2009 23:09 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Think about it.

The best is ... well ... the FASTEST one. Right? So how are you going to determine which one is faster?
Ross Leishman

Report message to a moderator

Re: Difference between cost and %CPU? [message #389350 is a reply to message #389344] Sun, 01 March 2009 05:05 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi Ross, Thanks for yr reply. I am sorry. I do not understand whether i should calculate performance based on %CPU or COST? Pl explain!

Report message to a moderator

Re: Difference between cost and %CPU? [message #389364 is a reply to message #389308] Sun, 01 March 2009 10:34 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
You might want to fix this

Note
-----
   - 'PLAN_TABLE' is old version


before reading too much into your explain plans.

Report message to a moderator

Re: Difference between cost and %CPU? [message #389537 is a reply to message #389350] Mon, 02 March 2009 09:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that your server isn't CPU bound (ie isn't running at or near full load on it's cpu), you want to pick the best query on neither of these figures.
As Ross said - the best query is the one that returns the results the fastest, so measure how long they take, and pick the quickest.

Report message to a moderator

Re: Difference between cost and %CPU? [message #389670 is a reply to message #389537] Tue, 03 March 2009 02:07 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Thnx! Leving cost out of equation how do i measure it? On what basis shall i pick the quickest one? Pls explain.

Report message to a moderator

Re: Difference between cost and %CPU? [message #389694 is a reply to message #389670] Tue, 03 March 2009 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
On what basis shall i pick the quickest one


Eh?
What?

We seem to be having a total failure of communication here.

Imagine if I gave you two sticks of different lengths, and, when told to pick the longest one you said 'On what basis shall I pick the longest one?'

Your reply above makes the same amount of sense.

You pick the quickest query on the basis of it taking the least time to execute - what other conceivable answer could there be?

Report message to a moderator

Re: Difference between cost and %CPU? [message #389695 is a reply to message #389308] Tue, 03 March 2009 04:16 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Well think about this concept...

Compressed data uses less space than uncompressed data but a data decompression process uses more CPU than one that just retrieves data.

What could this mean?


Bye Alessandro

[Updated on: Tue, 03 March 2009 04:29]

Report message to a moderator

Re: Difference between cost and %CPU? [message #389741 is a reply to message #389695] Tue, 03 March 2009 07:36 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Thanks JRowbottom & Rossi! Now i can understand it well Smile

Report message to a moderator

Re: Difference between cost and %CPU? [message #389743 is a reply to message #389741] Tue, 03 March 2009 07:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One other point - in your initial post, you are comparing figures from the explain plans of two different queries - albeit ones that return the same results.

This is pointless - the only thing you can compare the Cost of a query's execution plan to is another execution plan for the same query.

Report message to a moderator

Re: Difference between cost and %CPU? [message #389744 is a reply to message #389743] Tue, 03 March 2009 07:47 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
JRowbottom, "This is pointless - the only thing you can compare the Cost of a query's execution plan to is another execution plan for the same query." -- the execution plans of the same query would always be the same right? Waht is the point in comparing them?

[Updated on: Tue, 03 March 2009 07:48]

Report message to a moderator

Re: Difference between cost and %CPU? [message #389773 is a reply to message #389744] Tue, 03 March 2009 09:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
the execution plans of the same query would always be the same right?


Not at all - Hints can change execution plans.
Addition or removal of indexes and constraints can change execution plans.
Changes in the statistics for a table can change an execution plan.
Stored Outlines can change an execution plan.
Several init.ora parameters can change execution plans.

The Cost shown on the Explain plan is a figure that the CBO calculates for each execution path that it looks at when it parses a query. It will pick the execution path with the lowest cost.

Costs are broadly comparable across similar queries - things with very high costs will almost always take longer than things with very low costs, but it's no more accurate than that.

Report message to a moderator

Re: Difference between cost and %CPU? [message #389851 is a reply to message #389773] Tue, 03 March 2009 15:33 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just adding/deleting/updating the data (which is quite normal for a database Smile) will also change the plan.
Just consider it as google/yahoo maps.
CBO will calculate the nearest route to fetch data based on available statistics.
The "available statistics" are a certain fancy information on data, which should be refreshed periodically.
Else, CBO will generate a plan with old statistics and it may not be the optimal one.

Report message to a moderator

Previous Topic: Indexes (merged 3)
Next Topic: updation of 200millions rows
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 30 13:32:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.