RE: Monitoring CBO degrading Switches in xplans?
Date: Thu, 25 Jun 2009 01:09:19 -0700 (PDT)
I have on a system which does more than 1000 transactions per second and have been sleeping very well since then :-)
Senior Oracle Consultant
M: +61 402 792 405
- On Wed, 24/6/09, Fmhabash <fmhabash_at_gmail.com> wrote:
From: Fmhabash <fmhabash_at_gmail.com>
Subject: RE: Monitoring CBO degrading Switches in xplans? To: "Andrew Kerber" <andrew.kerber_at_gmail.com>, fmhabash_at_gmail.com Cc: "Oracle-L Group" <oracle-l_at_freelists.org> 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?
From: Andrew Kerber <andrew.kerber_at_gmail.com> Sent: Thursday, June 18, 2009 6:04 PM
Cc: Oracle-L Group <oracle-l_at_freelists.org> 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 <fmhabash_at_gmail.com> 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
> 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
> Thank You.
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-l Access Yahoo!7 Mail on your mobile. Anytime. Anywhere. Show me how: http://au.mobile.yahoo.com/mail -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 25 2009 - 03:09:19 CDT