Re: Which plan is better - what COST really means ?

  • From: ryan_gaffuri@xxxxxxxxxxx
  • To: premj@xxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 13 Jan 2005 15:26:22 +0000

kyte writes taht comparing the cost of two different queries doesnt mean 
anything. if you really want to you can run a 10053 trace and get a calculator 
to figure out how the cost is calculated. its useful occasionally to see how 
oracle calculates with more buckets in hostograms or with different optimizer 
settings, but most of the time its not useful(unless you are really bored). 
i believe jonathan lewis talked about some tests on here where he actually 
believes that comparing two costs 'may' be useful. 

personally if you can run the query the most effective and easiest way to see 
if one query is better than another is by setting autotrace on and looking at 
'consistent gets'. This seems to work about 99% of the time. Occasionally I get 
radical reductions in logical io(consistent gets) with little to know 
performance improvements. one time i actually got a radical reduction in 
logical io and greater response time. that only happened once. 

if you have two long running queries that you are comparing this wont work. I 
dont like to rely on the cost since i dont know enough about it. One good 
indicator that alot of people over look is the 'bytes' column. if oracle thinks 
its going to need to go through less bytes in a bulk query it typically 
performs better... this is not 100% accurate either unfortunately. 


Other related posts: