Re: SPM again

  • From: Job Miller <jobmiller@xxxxxxxxx>
  • To: sacrophyte@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx, John Kanagaraj <john.kanagaraj@xxxxxxxxx>
  • Date: Fri, 12 Mar 2010 04:57:07 -0800 (PST)

>Seriously, you are going to allow the optimizer to pick the lower cost
> plan when you *know* that the lower cost plan (as calculated) is 
> sometimes not the "right thing" to do?

letting the optimizer pick the plan with the lowest cost is disconnected from 
the auto-evolve process.

the new plan might be the right thing for you, and after the auto-evolve 
process, the new plan will be executed as is the next time the CBO picks that 
path.

auto-evolve adds the new plan to the baseline of available acceptable choices 
only after actual verification by execution that the new plan is 3x better 
(defined by an algorithm not published).

Without SPM, this new plan would have been executed automatically anyway.  
I guess that's what you are referring to above.  You don't want the CBO to pick 
a verified plan with the lowest cost, you want the new better plan right now.  
Is that what you mean?  If that is the case, then Plan Stability probably isn't 
what you are looking for.

With SPM, the plan is determined as usual, and if it is in the baseline as an 
acceptable plan, it is used.  If not, then "plan stability" kicks in and picks 
a plan you have seen before based on cost for your query given current stats.

thoughts and corrections welcome.

Job






      
--
//www.freelists.org/webpage/oracle-l


Other related posts: