Re: Costs in explain plan

>> costs that are displayed in explain plan

Jonathan Lewis has an interesting set of examples of explain plans and discusses the Cost of each step in his book, Practical Oracle, starting on page 131.

If a step for an index has (Cost=3 Card=5) this means Oracle expects to access 3 blocks (header block, branch block and leaf block of the index) and is looking to identify 5 rows. Oracle is predicting that all 5 rows will be in a single index leaf block.

If the step just above this one, for the table access, has (Cost=8 Card=5) this means Oracle expects to access a total of 8 blocks, consisting of the original 3 for the index step plus another 5 from the table. Oracle is predicting that the 5 table rows will be in 5 different table blocks.

Lewis presents this material using phrases like "I am inclined to believe" and "I assume" so apparently this description is based on his examination of many plans and his deductions about how to interpret these numbers. Still, he provides several examples that are clearly explained and he concludes that it is simple math and that's it's possible to understand the calculations Oracle is making.

It's up to you to know whether the blocks are likely to be in memory or not (index header and branch blocks on a frequently accessed index - probably yes).

These are Oracle's best estimates based on the statistics available. It may turn out differently, e.g., all the table rows may be in a single block.

The interesting point about his explanation is how different it is from what appears elsewhere. Some authors are quite vague, implying that Cost is a number with no meaning, and they conclude that you can't compare the cost for two different SQL's. Lewis seems to disagree 100%.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Moore
  INET: sqlgreg_at_pacbell.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).