Re: Monitoring CBO degrading Switches in xplans?

From: John Kanagaraj <john.kanagaraj_at_gmail.com>
Date: Fri, 19 Jun 2009 09:12:19 -0700
Message-ID: <2ead3a60906190912l60460a93h7e121cbc29e3d5b3_at_mail.gmail.com>



Hi,

> 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 <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (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 **
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 19 2009 - 11:12:19 CDT

Original text of this message