RE: Monitoring CBO degrading Switches in xplans?

From: fairlie rego <>
Date: Thu, 25 Jun 2009 01:09:19 -0700 (PDT)
Message-ID: <>

I have on a system which does more than 1000 transactions per second and have been sleeping very well since then  :-)  
Fairlie Rego
Senior Oracle Consultant
M: +61 402 792 405

  • On Wed, 24/6/09, Fmhabash <> wrote:

From: Fmhabash <>
Subject: RE: Monitoring CBO degrading Switches in xplans? To: "Andrew Kerber" <>, Cc: "Oracle-L Group" <> Received: Wednesday, 24 June, 2009, 4:49 AM

Oracle recommended it, but with the disclaimer that you do your own testing.. The test setup is currently in progress. I would like to hear from DBA'S who actually implemented it and how did it impact performance?

Thank You.

-----Original Message-----
From: Andrew Kerber <> Sent: Thursday, June 18, 2009 6:04 PM
Cc: Oracle-L Group <> Subject: Re: Monitoring CBO degrading Switches in xplans?

Did you try turning off bind variable peeking or histograms?

On Thu, Jun 18, 2009 at 4:51 PM, Fmhabash <> wrote:

> 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.
> 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.
> With all this info, we alert if ET and plan cost exceed thresholds.
> How do you cope with this issue and what have you done to monitor it (logic
> wise).
> ---------------
> Thank You.
> --

Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


      Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how:

Received on Thu Jun 25 2009 - 03:09:19 CDT

Original text of this message