FAQ
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.

Search Discussions

  • Andrew Kerber at Jun 18, 2009 at 10:04 pm
    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.
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Andrew W. Kerber

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Milen Kulev at Jun 18, 2009 at 10:33 pm
    What about using SQL Profiles and generating them automatically (the hints
    for the good/bad execution plans are in dba_hist_sqlplan anyway). You will
    end up with many SQL Profiles, but at least the execution plans will be
    stable.
    Bear in mind that the efficiency (elapsed time per execution) may greatly
    vary depending on the values of the bind variables

    HTH. Milen

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Fmhabash
    Sent: Donnerstag, 18. Juni 2009 23:52
    To: Oracle-L Group
    Subject: Monitoring CBO degrading Switches in xplans?

    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.
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Saibabu Devabhaktuni at Jun 19, 2009 at 8:08 am
    One of the ways to monitor it is by plan_hash_value and get the corresponding bind_data from awr; this will not only tell you when the plan changed and possibly why also.

    If the data distribution is uniform for the most part, i.e., not much skewness in the data, and the change in data size is in general not going to influence data distribution or relative to each other table changes....then you can

    turn off auto stats gathering job
    turn off bind peeking (but this can generate suboptimal execution plan for LIKE operations in join queries or even for index look ups due to default bind value assumptions made by optimizer, make sure to test your queries before you make this change)
    turn off optimizer_dynamic_sampling (again make sure that statistics were gathered for all objects)
    gather stats only if the data distribution pattern is changed (including system statistics)

    We did all of the above and never have any problems with execution plan; for us consistency and predictability is far more important than tiny change in run time performance.

    Thanks,
    Sai
    http://sai-oracle.blogspot.com
  • Fmhabash at Jun 19, 2009 at 1:44 pm
    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
    To: fmhabash_at_gmail.com
    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.
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Andrew W. Kerber

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

    --
    http://www.freelists.org/webpage/oracle-l
  • John Kanagaraj at Jun 19, 2009 at 4:12 pm
    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
  • Fmhabash at Jun 23, 2009 at 6:49 pm
    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
    To: fmhabash_at_gmail.com
    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.
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Andrew W. Kerber

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Fairlie rego at Jun 25, 2009 at 8:09 am
    I have on a system which does more than 1000 transactions per second and have been sleeping very well since then  :-)

    Thanks

    Fairlie Rego
    Senior Oracle Consultant

    http://el-caro.blogspot.com/
    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", fmhabash_at_gmail.com
    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
    To: fmhabash_at_gmail.com
    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.
    --
    http://www.freelists.org/webpage/oracle-l

    --
    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-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 18, '09 at 9:51p
activeJun 25, '09 at 8:09a
posts8
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase