Re: Monitoring CBO degrading Switches in xplans?

  • From: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
  • To: fmhabash <fmhabash@xxxxxxxxx>
  • Date: Fri, 19 Jun 2009 09:12:19 -0700


> This is an issue that has made sleepless over and over again. Critical 
> application sql changes its xplans causing elapse time to go unacceptably 
> worst.

Without version and related information, it is difficult to even guess
what the problem could be. If this is 10g, one of the more obvious
possibilities is the inbuilt, out of box statistics gathering that
uses the defaults. The two worst things that happens in this well
intentioned feature is that the default METHOD_OPT is "FOR ALL COLUMNS
SIZE AUTO" that generates histograms willy-nilly, resulting in
excessive bind peeking (which itself can add some instability as
evidenced by horror stories in this list)

> We eventually wanted to monitor this kind of event proactively. To this 
> effect, we query top n sql from awr order by elapsed time. We then compare 
> most recent ET value to the previous n samples. We then calculate cost of 
> current plan and compare it to existing awr ones cost.

This is the right approach, but this begs the question: What next?

John Kanagaraj <>< (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **

Other related posts: