Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Steve.

Asked: May 03, 2005 - 3:11 pm UTC

Last updated: May 06, 2005 - 11:44 am UTC


Viewed 10K+ times! This question is

You Asked

I need some confirmation/clarification on the use of COST in an explain plan. I just came from the IOUGA seminar Jonathan Lewis gave, "Understanding and Assisting the Cost-Based Optimizer". He indicated that, in principle, cost is a measure of execution time. Furthur implying also that when evaluating queries (that produce the same result set), the one with the lower cost will result in less PIO.
This runs contrary to information I've previously read from your site (which now I cannot find the topics) that indicate we cannot derive anything from looking at the cost between two queries, and the cost value is a number calculated by Oracle that is meaningful only to that query and to not use it as an measure of which query will be more efficient.

Perhaps I misunderstood your comments, or the post was older and cost can be compared with the newer versions of Oracle (e.g. 9.2 and up).

Can you clarify?


and Tom said...

in principle

keywords -- in principle.

In a perfect world. If the numbers were always dead on accurate. Yes.

But they aren't. They get better and better -- but they are't.

And the real confusion comes in with something like this:

ops$tkyte@ORA9IR2> /*
DOC>drop table t;
DOC>create table t as select * from all_objects;
DOC>create index t_idx on t(object_id);
DOC>exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> alter session set optimizer_index_caching =0;

Session altered.

ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj =100;

Session altered.

ops$tkyte@ORA9IR2> select * from t where object_id < 100;

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=66 Bytes=6138)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=61 Card=66 Bytes=6138)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=66)

ops$tkyte@ORA9IR2> alter session set optimizer_index_caching =90;

Session altered.

ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj =10;

Session altered.

ops$tkyte@ORA9IR2> select * from t where object_id < 100;

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=66 Bytes=6138)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=7 Card=66 Bytes=6138)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=66)

ops$tkyte@ORA9IR2> set autotrace off

Did we really make this query run faster by setting those parameters? No, we changed a bunch of numbers fed into a mathematical model and came out with different numbers.

If the optimizer was perfect in its guesses and assumptions (as it gets closer and closer to that), the cost will be a more accurate predictor of time. 10g with cpu costing gets a step closer yet.

But for every case where you give me a pair of semantically equivalent queries that the lower cost one runs faster, I'll give you a pair where the converse is true (and since the set of SQL queries is inifinite.... I cannot prove that :)

(jonathan knows a *lot* about that CBO doesn't he, scary sometimes...)


  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment


So now what?

Loz, May 03, 2005 - 8:33 pm UTC

So how are we supposed to interpret the plan?
Are you saying we can only look at it to make sure it's doing something vaguely sensible or are you saying that given the same session options (i.e. model settings) you can in fact compare costs between semantically identical queries?
What do you personally look for in a query plan, or do you focus more on tkprof?

Tom Kyte
May 03, 2005 - 8:51 pm UTC

I look at plans when something goes wrong generally.

I look for changed plans.

I look at plans that are performing poorly.

I rarely, if ever, look at a plan before actually running the query (except to show a concept)

The plan is just "facts", information. Bad plan -- I'm looking really hard at the card= values to see if the optimizer got it wrong. The plan is what the optimizer came up with, it is just "a fact"

So, I normally use the plan when things are bad. I ask for:

autotrace traceonly explain (what the optimizer *thought*)
tkprof (what actually happened)

and start from there. But for me, plans are mostly a post mortem thing or a learning tool (the "see, when we set this, it affects the cost like that, and this happens as a result" -- like above, showing the effect of setting some optimizer parameters on the costing of a plan)

I myself do not often look at the cost. That is me though! My general approach is to look at the card's and figure out why the guess was so wrong. Understanding the cost however is part of the total equation.

the cbo is getting closer and closer to the "lower the cost, the faster it goes" and my example above was extremely artificial.


AB, May 04, 2005 - 4:48 am UTC

>>(jonathan knows a *lot* about that CBO doesn't he, scary sometimes...)
I thought I read a rumour that he was writing a book on the CBO and that it was supposed to be out around about now. I can't see any reference to it on his site...

Alberto Dell'Era, May 04, 2005 - 5:10 am UTC

Probably Jonathan Lewis was saying that cost is the CBO *estimate* of elapsed time, whose unit of measure is "time to read a single block":

Cost = time to completion / sreadtim
</code> <code>
[sreadtim is a measure itself, but it's constant if you don't regather system statistics]

Anyway the keyword is "estimate" ;) regardless of the unit of measure ... and it's interesting to know that *currently* the cost is related to elapsed time only (instead of, say, being a weighted average of, say, elapsed time, required load on cpu, disk, memory, etc).

explain plan

Parag Jayant Patankar, May 04, 2005 - 7:03 am UTC

Hi Tom,

I am trying to understand explain plan for your book effective oracle design 

create table t
collection_year int,
data              varchar2(25)
partition by range (collection_year) (
partition p1 values less than (2000),
partition p2 values less than (2001),
partition p3  values less than (2002),
partition p4  values less than (2003),
partition rest values less than (maxvalue)

15:44:23 SQL> explain plan for
15:44:31   2  select * from t where collection_year = 2002;


16:15:08 SQL> @u


| Id  | Operation            |  Name     | Rows  | Bytes | Cost  | Pstart| Pstop |
|   0 | SELECT STATEMENT     |           |     1 |    27 |     2 |       |       |
|*  1 |  TABLE ACCESS FULL   | T         |     1 |    27 |     2 |     4 |     4 |

Predicate Information (identified by operation id):

   1 - filter("T"."COLLECTION_YEAR"=2002)

Note: cpu costing is off

Q1. What is "CPU costing is off" means ? I have done analyze of the table but still it is same.

Q2. What is pstart and pstop ? ( is it partition start and stop ? ) why these columns are  showing number 4 it should show number 3 because it is referring 3rd partition
regards & thanks


Tom Kyte
May 04, 2005 - 9:18 am UTC

q1) in 9i -- cpu costing (new) is disabled by default, in 10g it is enabled by default. in 9i, it should be off, in 10g, it should be on.

q2) pstart/pstop are partition start/stop ranges. This shows that the table access full will really only hit partition 4. it hits partition 4 -- not three. p3 has values LESS THAN 2002, 2002 is not LESS THAN so it is in p4 which has values 2002 <= X < 2003


Steve, May 04, 2005 - 8:56 am UTC

Yes, Jonathan knows alot about the CBO. I couldn't keep up with several topics in his seminar, especially concerning the calculations the CBO does - whosh, right my head! The seminar was great, I did learn plenty.

To paraphrase my overall understanding of cost:
The cost number can/will flucuate depending on parameters we can modify directly (e.g. optimizer_index_cost_adj) or the parameters we can modify indirectly (e.g. gather recent statistics). In some cases when the cost decreases the query will run faster (be more efficient), in other cases it may not. So just because I can decrease the cost, doesn't mean I'm decreasing work (PIO). And just because one query may have a lower cost then another doesn't mean it works less. But what was true yesterday isn't true today, and what's true today won't be true tomorrow; with each new release the cost becomes a better indicator of work to be done, but today it isn't perfect enough to rely on exclusively.

(Note to the poster mentioning a new book from Jonathan, he indicated he is working on volume 1 (of 5); if I remember correctly, it should be available this fall.)


Tom Kyte
May 04, 2005 - 9:57 am UTC

your overall understanding coincides with mine 100%.

cpu costing

Connor McDonald, May 04, 2005 - 10:05 am UTC

Just wondering on your motivation for the statement:

"in 9i, it should be off"

we're using it with good benefit at a site here


Tom Kyte
May 04, 2005 - 10:45 am UTC

I don't like to change defaults unless I have to...


Jonathan Lewis, May 04, 2005 - 12:39 pm UTC

To pick up a couple of comments.
First a correction, cpu_costing is not "OFF" as such in 9i: like the old 'optimizer_mode=choose' option in 7 and 8 there is a parameter set to 'choose' for cpu costing. The effect of this parameter is that the CBO will use I/O costing if you do not have valid system statistics (a.k.a. cpu costing information), and CPU costing if the system statistics have been gathered and are valid. CPU costing appears to be OFF in 9i, because there are no default system stats, CPU costing appears to be ON in 10g because there seem to be some 'no workload' system statistics that can be used in the absence of deliberately gathered statistics (so the choice is always to use cpu costing).

Second, I am writing a book on Oracle's cost based optimisation mechanisms. I hope to have volume one finished in time for September publication. At the rate I am going, there will have to be at least three volumes to cover everything - although 'everything' includes lots of peripheral stuff like explaining the opportunities available through dbms_stat; how to read execution paths; how to make best use of indexes; partitioning, parallel query (which will be in volumes 2 or 3 - but I reserve the right to go as far as volume 5).

Third, the "COST" of a query is Oracle's estimate of the completion time for a query. But the estimate for the completion time is based on Oracle's statistical guesses about your data (which may be wrong), the model that Oracle uses for your hardware resource availability (which may be wrong) and the assumptions that Oracle makes about the actions of the run-time execution (which, surprisingly, may be wrong). Consequently, the predicted cost is not always a good estimate of the execution time of the query. Just to add a little confusion, the unit of measurement used is not seconds (or centi-seconds), but an abstract unit equivalent to the assumed time to perform a single block read on your platform.

Tom Kyte
May 04, 2005 - 1:47 pm UTC

Jonathan -- Thanks much for the followup, appreciate it.


Steve, May 04, 2005 - 2:21 pm UTC

Jonathan, thanks for your input. Everything you wrote I remember you mentioning in your seminar on Sunday - some of the details I just couldn't recollect with much clarity.

I almost hesitated to ask the question, because I didn't it to become a "he said, he said" exchange, I just needed to tie my previous understanding from Tom's site to the information you gave.

Thanks again for all the really good and helpful information.


Tom Kyte
May 04, 2005 - 2:26 pm UTC

I don't think we are saying anything too different here at all...

I asked Jonathan to come and comment, I don't think there is a problem with "he said, he said" -- we all just want to come to a common understanding. Don't ever be afraid of that. And I'm always glad to be "clarified upon", thats why I try to talk him into reviewing my books every time ;)

Computable Numbers

DaPi, May 04, 2005 - 3:40 pm UTC

Just to say this geek appreciated this:
"(and since the set of SQL queries is inifinite.... I cannot prove that :)"

See Alan Turing (1936)

Costs when combining two queries with union all

Georg, May 04, 2005 - 4:49 pm UTC

If i have two alternatives for a problem (Query1 and Query2)
and i generate a plan for

union all

Can i compare the two costs prior to the union all step in the plan?

PS: Is this a new question or a true follow-up?

Tom Kyte
May 04, 2005 - 6:19 pm UTC

no, i would not compare the cost of two different queries.

what would you hope to gain from doing so in this case? (although here you might reasonably expect cost of query 1 + cost of query 2 to be the outcome)

Alberto Dell'Era, May 04, 2005 - 5:50 pm UTC

> I reserve the right to go as far as volume 5

"The Art of CBO Programming" :)


A reader, May 05, 2005 - 1:10 am UTC

"(and since the set of SQL queries is inifinite.... I cannot prove that :)"

Get 1 query where cost going down results in elapsed time going down (Query A) and 1 query where cost going down results in elapsed time going up (Query B)



Recurse as necessary :-) No one ever said the queries had to be semantically different.

jonathan database scientist

hrishy, May 05, 2005 - 3:48 am UTC

Hi Jonathan

I am just wundering whether you are a database scientist.Why such a esoteric book on optimizer alone ?I have your book on practical 8i i liked it very much would have preferred a similar kind of book on 10g rather something esoteric as the internals of optimizer.



DaPi, May 05, 2005 - 4:17 am UTC

"Recurse as necessary :-)"

That's the geek point: the number of possible SQL statements is UNCOUNTABLY infinite - so any looping scheme will miss some - see Turing. (Some infinities are bigger than others).

Esoteric Book

Jonathan Lewis, May 05, 2005 - 10:15 am UTC


Tom let me know that you had asked this question in case I wanted to make any comment.

The most important answer is that the optimizer is not esoteric, and if you have a reasonable idea of the basic methods you will know WHY your SQL is doing strange things and be able to solve problems with resource-intensive queries much more easily.

As far as 'Practical Oracle 10g' is concerned - there really isn't much in 10g that isn't covered in the 8i book. A couple more features worth mentioning, and a few examples that need changing. It would be very boring simply rewriting the book with a few pages of updates.

Tom Kyte
May 05, 2005 - 10:30 am UTC

Thanks Jonathan

My 2cents is -- I'm getting this book when it comes out. Understanding how the optimizer works is crucial to being able to diagnose "why it isn't going quite right". Not only that, but you'll appreciate the complexity of the beast alot more...


A reader, May 05, 2005 - 10:42 am UTC

"That's the geek point: the number of possible0 SQL statements is UNCOUNTABLY infinite - so any looping scheme will miss some - see Turing. (Some infinities are bigger than others)."

The cardinality of possible SQL statements is irrelevant. The situation was for Tom (a mere mortal, contrary to popular belief) to create a pair of SQL statements showing that cost and elapsed time were inversely related for each pair that someone showed him they were proportionally related. After finding one pair of queries showing the relationship, he can just wrap one, the other, or both in a "select * from (query)" and have a "new" counter case. Tom cannot possibly exhaust that set in his lifetime (even with his handy dandy laptop)

The new OPT_ESTIMATE hint

kerry, May 05, 2005 - 11:07 am UTC

I've found that when the optimizer miscalculates cardinality (despite proper stats gathering and everything else we can do systematically) I am often better off tuning the cardinality estimates, rather than trying to hint the execution plan directly.

Cardinality, and in 9i, selectivity have become my favorite hints. Unfortunately, selectivity went away in 10g. Apparently both are to be replaced with the new OPT_ESTIMATE hint. This paper, </code>, <code>from our friend Jonathan Lewis, shows these forms of the OPT_ESTIMATE hint:


I was also fed this in a TAR from Oracle Support:


QUESTION: Is complete documentation on this hint forthcoming? Anyone discovered any other variants?


Jonathan Lewis, May 05, 2005 - 2:59 pm UTC

The version of the presentation that was to be published for Open World was not supposed to contain the clue that opt_estimate was a hint. At present, it is an internal-only option, and there have been changes in my test cases when moving from to - so it is not stable

Tom Kyte
May 05, 2005 - 3:00 pm UTC

(and notes seem to indicate that it won't be made public - but metalink always accepts feature requests, this would be a good hint to have.......)

quality over quantity ...

Gabe, May 06, 2005 - 10:49 am UTC


<quote>It would be very boring simply rewriting the book with a few pages of updates.</quote>

Nonetheless, the 8i moniker is likely keeping some people away from getting the book and that’s certainly a shame.

Have you ever considered getting yourself a coauthor who could contribute the few extra 10g features <quote>worth mentioning</quote> and also (or just) do the _boring_ part of updating/re-working the examples? After all, what is boring for you might be interesting stuff for someone else … it is a matter of perspective.

Personally I think it would be great to rejuvenate the book … may also stave off some of the onslaught of dubious or average quality, but certainly 10g-labeled material out there. Think of it as a good cause, a way to fight back the prolific authors whose marketing strategy is to flood the shelves with as many latest-Oracle-version books as possible.

Tom Kyte
May 06, 2005 - 11:44 am UTC

I'll send Jonathan an email to look over here.

Good idea...

Scot, May 06, 2005 - 2:47 pm UTC

I'd like to second Gabe's suggestion, so long as it did not interfere with the optimizer specific book mentioned above due out later this year. I don't see anything esoteric about it, and I'm looking forward to it.

But as Gabe mentioned, it would be a great opportunity for another Oracle author. Plus there are bound to be some new things that you have learned since you wrote the 8i version.

Besides, you could target it for 2006, and the marketing folks would get to put one of those fancy "covers 10gR2!" label thingys on the cover :)

Book(s), the next volumes / edition

Parthiban Nagarajan, October 16, 2009 - 1:00 am UTC

Hi Tom (and Jonathan)

-- A lot of question marks down there. Answer ALL ;)

The previous post in this thread happened a way back in the year of 2005. Its now 4 years since that.

The Oracle community got only "CBO fundamentals" from Jonathan. Where are those other 4 or 5 volumes about "CBO"? Why Jonathan stopped there...? Stopped teaching us...? "FundaMental" arithmetic is good and but its not enough to learn about the beast.

As Oracle evolving faster, I think, its really very difficult to consolidate and write about CBO. Jonanthan did mention in the 1st volume itself about the fluctuating CBO.

And 11g R2 is on the stage now. Do you think, "Practical Oracle (8i)" is still valid?

And ... "Expert Oracle One on One: 9i and 10g Database Architecture and Programming Techniques" is also missing the "2nd volume" ...

Vow - at least - Steven Feuerstein's "Oracle Pl/Sql programming" 5th edition has been released on October 1st, 2009.


Parthiban Nagarajan, November 13, 2009 - 3:12 am UTC

Hi Tom ...

Happy to see you that you are working on the next book ...

[[ Regarding Partition ]]

And, is there anything similar from Jonathan Lewis?

And please clarify whether "Practical Oracle 8i" is still holding its position in 11g era.

Thanks in Advance ...

More to Explore


Get all the information about database performance in the Database Performance guide.