FAQ
Hello Listers.

On the database I support - a reasonably big, about 4TB in size DSS
Database, which is fairly busy - I am able to resolve most of the
performance related issues. But at times, a job might run longer than usual.
The plan appears to be good. No unwanted wait events. But, it may turn out
that plan is sub-optimal. Recently one of the monthly jobs was making index
scan but won't complete. It had almost 100% on Sequential read on ASH.
Finally I figured out that, with a full scan the query would finish very
fast.

Now the question I have is this: While I think I know the system reasonably
well, how do I keep a base line for performance? How can I notice that, this
job which has been in production for a while now should have gone in for
Full scan? Generally speaking a production DB will have many jobs. And how
do we get a base line?

To trace each job to get an accurate picture while doing good may not be a
possible - because there will be too many jobs. each jobs will have hundreds
of SQLs, if not thousands. To dump the plan of all the SQLs in memory for
one month to get all of them also would be too tidy, considering the volume.

I was wondering how are you addressing this issue?

Thanks for your input in advance.
Shiva

Search Discussions

  • Allen, Brandon at Jan 5, 2007 at 7:29 pm
    You mentioned ASH, so it sounds like you're on 10g, which keeps all the
    history you need in ASH/AWR - you should be able to see there when an
    execution plan changed and the resulting performance stats.


    If you were on 9i, I'd recommend regular (every hour at least) statspack
    snapshots with occasionaly level 6 snapshots to capture the explain
    plans as well.


    With the explain plan problem you describe below, it sounds like you're
    encountering the same problem I battle on a daily basis these days -
    bind variable peeking causing explain plans to be shared when they
    shouldn't be, i.e. between one execution for a very selective (i.e.
    unpopular) value and a later execution for a very unselective/popular
    value. The first should use an index and the latter should use FTS, but
    Oracle only peeks the first time and then uses the same plan for both
    executions. The solution is to force the preferred method with a hint
    or stored outline, or break the two executions into two syntactically
    different statements so they don't share the same cursor, e.g. with
    comments like:


    SELECT /*+ popular */ . . .


    SELECT /*+ unpopular */ . . .


    Or, disable bind variable peeking by setting the hidden parameter.


    Regards,
    Brandon

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Shivaswamy Raghunath at Jan 5, 2007 at 7:43 pm
    Brandon,

    Thanks for your input.

    Yes we are on 10.1.0.5. I don't think we can get plan from ASH do we? Even
    if we did, it could not have helped in monthly job, last time it ran was
    early Dec - in this case. We do take snapshots.. but I don't know how I
    could have used it in this case.

    Thanks for drawing attention toward bind variable peeking. I will look into
    it.

    Thanks,
    Shiva
    On 1/5/07, Allen, Brandon wrote:

    You mentioned ASH, so it sounds like you're on 10g, which keeps all the
    history you need in ASH/AWR - you should be able to see there when an
    execution plan changed and the resulting performance stats.

    If you were on 9i, I'd recommend regular (every hour at least) statspack
    snapshots with occasionaly level 6 snapshots to capture the explain plans as
    well.

    With the explain plan problem you describe below, it sounds like you're
    encountering the same problem I battle on a daily basis these days - bind
    variable peeking causing explain plans to be shared when they shouldn't be,
    i.e. between one execution for a very selective (i.e. unpopular) value and
    a later execution for a very unselective/popular value. The first should
    use an index and the latter should use FTS, but Oracle only peeks the first
    time and then uses the same plan for both executions. The solution is to
    force the preferred method with a hint or stored outline, or break the two
    executions into two syntactically different statements so they don't share
    the same cursor, e.g. with comments like:

    SELECT /*+ popular */ . . .

    SELECT /*+ unpopular */ . . .

    Or, disable bind variable peeking by setting the hidden parameter.

    Regards,
    Brandon

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do not
    consent to Internet email for messages of this kind. Opinions, conclusions
    and other information in this message that do not relate to the official
    business of this company shall be understood as neither given nor endorsed
    by it.
    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Jan 5, 2007 at 7:49 pm
    Yes AWR does keep the explain plans - check dba_hist_sql_plan (or just
    look at it through the DB Console/Grid Control GUI), and yes it could've
    helped for last month, assuming you set your AWR retention
    appropriately. IIRC, it defaults to only 1 week, but I bumped mine up
    to 45 days specifically for the purpose of covering monthly queries.


    HTH,

    Brandon

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Shivaswamy Raghunath at Jan 5, 2007 at 8:06 pm
    Thank you.. That is helpful.
    On 1/5/07, Allen, Brandon wrote:

    Yes AWR does keep the explain plans - check dba_hist_sql_plan (or just
    look at it through the DB Console/Grid Control GUI), and yes it could've
    helped for last month, assuming you set your AWR retention appropriately.
    IIRC, it defaults to only 1 week, but I bumped mine up to 45 days
    specifically for the purpose of covering monthly queries.

    HTH,
    Brandon

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do not
    consent to Internet email for messages of this kind. Opinions, conclusions
    and other information in this message that do not relate to the official
    business of this company shall be understood as neither given nor endorsed
    by it.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 5, '07 at 7:07p
activeJan 5, '07 at 8:06p
posts5
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase