Skip to Main Content
  • Questions
  • Oracle optimizer chooses a more expensive plan

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pramod.

Asked: June 06, 2001 - 2:46 pm UTC

Last updated: May 06, 2010 - 1:46 pm UTC

Version: 8.1.7.0.0

Viewed 1000+ times

You Asked

Tom,

We have a query which when explained chooses the plan with a higher cost. See below. The plan chosen has a cost of 1706, however when we hint the optimizer to perform a full table scan on a particular table the cost is 1480. It was my understanding that the optimizer would always choose the plan with the least expensive cost. Can you please shed any light on why the optimizer would choose the more expensive plan. Please let me know if there is any other info required.


PLAN CHOSEN BY OPTIMIZER
QUERY_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT Cost = 1706
2.1 NESTED LOOPS
3.1 NESTED LOOPS
4.1 NESTED LOOPS
5.1 HASH JOIN
6.1 HASH JOIN
7.1 TABLE ACCESS FULL SPLATE_TEMPLATE
7.2 TABLE ACCESS FULL ASSAY_POOL
6.2 TABLE ACCESS FULL SCREEN_RUN
5.2 TABLE ACCESS BY INDEX ROWID INSTR_RESULT
6.1 INDEX RANGE SCAN PK_INSTR_RESULT UNIQUE
4.2 TABLE ACCESS BY INDEX ROWID SCREEN_PLATE
5.1 INDEX RANGE SCAN XIE1SCREEN_PLATE NON-UNIQUE
3.2 TABLE ACCESS BY GLOBAL INDEX ROWID SCREEN_WELL
4.1 INDEX RANGE SCAN XIE1SCREEN_WELL NON-UNIQUE


QUERY_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT Cost = 1480
2.1 NESTED LOOPS
3.1 HASH JOIN
4.1 NESTED LOOPS
5.1 HASH JOIN
6.1 HASH JOIN
7.1 TABLE ACCESS FULL SPLATE_TEMPLATE
7.2 TABLE ACCESS FULL ASSAY_POOL
6.2 TABLE ACCESS FULL SCREEN_RUN
5.2 TABLE ACCESS BY INDEX ROWID INSTR_RESULT
6.1 INDEX RANGE SCAN PK_INSTR_RESULT UNIQUE
4.2 TABLE ACCESS FULL SCREEN_PLATE
3.2 TABLE ACCESS BY GLOBAL INDEX ROWID SCREEN_WELL
4.1 INDEX RANGE SCAN XIE1SCREEN_WELL NON-UNIQUE

and Tom said...

the cost of two queries cannot be compared.

The way the hint works is to artifically lower the cost associated with a step in the query to make that plan more appealing to the optimizer. It was the act of you HINTING the query that artificially lowered the cost of a particular plan, making that plan more appealing and hence "the plan".

followup to comment two below

Pramod,

your understanding is correct.

you wrote (i added the bolded text):

1)iterating throught the objects in the query

2)try different methods of access.

3)for each iteration calculate a resource cost based on avaiable statistics for the objects in the catalog. Hints will affect the costs associated with steps - this is how hints work. An INDEX hint will artificially lower the cost of using that index and artificially inflate the costs of other access methods

4)once it completes all possible iterations(statisitcal permutations)choose the plan with the lowest cost.



That is correct, thats what happens. That is also why you cannot compare the costs across executions. Everything you do to change a plan influences the costs associated with different steps. The value assigned in step 3 is comparable only to other query plans computed at the same time -- given the same ENVIRONMENT.

by using hints, you changed the environment -- just like you might when computing stats, changing the sort_area_size, and so on.


Rating

  (4 ratings)

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

Comments

Understanding how this works is really important

Harrison, June 06, 2001 - 6:47 pm UTC

Understanding what a tool is doing is a lot better than
just seeing the numbers, as this example points out.
Almost every tool has some limitation, and knowing what
they are for the tools we use is important; knowing a few
tools well would seem to be better than getting tricked
by this kind of stuff.

Does the Optimizer consider every access option for every object for a Query Plan?

Pramod, June 07, 2001 - 10:45 am UTC

Okay your point is that by hinting for the optimizer to do a full table scan we changed the queries cost and the plan. I have used hints extensively and I have added hints that push the cost much higher(typically does not make the cost less) and then run the query which actually runs faster. In effect when you give the optimizer a hint you are telling it you know better how to access the paticular object(Maybe because the statisics collect for the objects are old or not detailed enough). I always have assumed that any hint that I give the optimizer is going to cause the calculated resource cost for the query to be higher then the resource cost the optimizer calculated for the query with no hints. I base my assumptions on how the cost based optimizer finds a plan for a query which as I understand it is on the Lowest Total Cost for the query. I assumed it calculates the lowest cost by using the following simplified algorythm :

1)iterating throught the objects in the query
2)try different methods of access.
3)for each iteration calculate a resource cost based on avaiable statistics for the objects in the catalog
4)once it completes all possible iterations(statisitcal permutations)choose the plan with the lowest cost.

Is my understanding of how the cost based optimzer makes its choice of a queries plan incorrect?

Thanks for the Futher Clarification..

Pramod, June 08, 2001 - 8:37 am UTC

Tom,
Thank you very much for the further clarification on the cost based optimizers interworkings with hints.. I completly understand your point now... BTW I will be purchasing your book from Amazon today... They are still listing at pre-order status can't wait to get my copy...


Index hint

Jonathan Lewis, May 04, 2010 - 5:13 pm UTC

Tom,

I know this it 9 years late - but the follwoing comment has been quoted a couple of times on the OTN database forum and it's wrong:

"Hints will affect the costs associated with steps - this is how hints work. An INDEX hint will artificially lower the cost of using that index and artificially inflate the costs of other access methods"

An index hint tells the optimize that it must use an index (it it's legal) at a certain point in the decision tree - one of the indexes specified in the hint if any ones are specified, or any of the existing indexes; the arithmetic that produces costs is not affected by such hints.



Tom Kyte
May 06, 2010 - 1:46 pm UTC

Jonathan

thank you - you are absolutely correct, I mispoke on that.

I have examples in other places where I use the hint to show what the cost of using the index (or whatever) would be to show that the cost of using that other approach would be more expensive (in direct contradiction to what I wrote here a long time ago)

appreciate the followup.

More to Explore

Performance

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