FAQ
Hello list,


We have to remove/disable the AWR packages because we don't have Diagnostic pack licence. Will try to use PERFSTAT / STATSPACK for monitoring performance issues.
Any bad experience with the usage of the STATSPACK in 10g (10.2.0.2.0 and 10.2.0.3.0)?
Thanks in advance.


Regards,
Istvan

Search Discussions

  • kyle Hailey at Feb 6, 2010 at 4:29 am
    You can also get ASH data with the S-ASH scripts
    http://sites.google.com/site/embtdbo/wait-event-documentation/sash-1

    <http://sites.google.com/site/embtdbo/wait-event-documentation/sash-1>Best
    Kyle Hailey
    http://db-optimizer.blogspot.com/
    <http://db-optimizer.blogspot.com/>

    2010/2/5 Toth, Istvan 5 (GE Capital)
    Hello list,

    We have to remove/disable the AWR packages because we don't have Diagnostic
    pack licence. Will try to use PERFSTAT / STATSPACK for monitoring
    performance issues.
    Any bad experience with the usage of the STATSPACK in 10g (10.2.0.2.0 and
    10.2.0.3.0)?
    Thanks in advance.

    Regards,
    Istvan

    **
    --
    http://www.freelists.org/webpage/oracle-l
  • Martin Klier at Feb 8, 2010 at 10:25 am
    Hi Istvan,

    one of the most disturbing factors of STATSPACK is the missing "parsing
    schema name" if you try to monitor multi-purpose-databases.
    Setting the MODULE-information of the session instead is a workaround, but
    not exactly the same.

    Furthermore, you should carefully select the level of information
    gathering, don't collect too verbose, it really might become huge. I often
    collect on a 10min-Basis, so I have to compromise.

    Another thing I miss, is the nonexistent session history, but the S-ASH
    link Kyle provided should help a lot, though I still have to test it for my
    cases.

    Regards and good luck - we'll appreciate any feedback from you.

    --
    Mit freundlichem Gruß

    Martin Klier
    Senior Oracle Database Administrator
    ------------------------------------------------------------------------------

    Klug GmbH integrierte Systeme
    Lindenweg 13, D-92552 Teunz
    Tel.: +49 9671/9216-245
    Fax.: +49 9671/9216-112
    mailto: martin.klier_at_klug-is.de
    www.klug-is.de
    ------------------------------------------------------------------------------

    Geschäftsführer: Johann Klug, Roman Sorgenfrei
    Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
    HRB Nr. 2037, Amtsgericht Amberg
    ------------>
    Von: |
    ------------>
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    "Toth, Istvan 5 (GE Capital)" |
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    ------------>
    An: |
    ------------>
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    ------------>
    Datum: |
    ------------>
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    06.02.2010 02:08 |
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    ------------>
    Betreff: |
    ------------>
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    STATSPACK in 10g |
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    ------------>
    Gesendet |
    von: |
    ------------>
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    oracle-l-bounce_at_freelists.org |
    --------------------------------------------------------------------------------------------------------------------------------------------------|
    Hello list,

    We have to remove/disable the AWR packages because we don't have Diagnostic
    pack licence. Will try to use PERFSTAT / STATSPACK for monitoring
    performance issues.
    Any bad experience with the usage of the STATSPACK in 10g (10.2.0.2.0 and
    10.2.0.3.0)?
    Thanks in advance.

    Regards,
    Istvan
  • Surachart Opun at Feb 9, 2010 at 3:59 am
    If licensed for Diagnostics Pack, does it make sense to run both AWR &
    STATSPACK?

    You have to use AWR and disable STATSPACK.

    and AWR have more feature than STATSPACK.

    Surachart Opun
    http://surachartopun.com
    On Tue, Feb 9, 2010 at 2:45 AM, Jonathan Intner wrote:

    Hi All:

    Not meaning to "hijack" this thread, but I have a question/comment that I
    think is related. The question is:

    If licensed for Diagnostics Pack, does it make sense to run both AWR &
    STATSPACK? I would actually argue that if AWR is available STATPACK is
    redundant. I'd love to hear what other folks think. If I get a number of
    private replies, I'll summarize the results for the list.

    Thanks in advance,

    Jonathan
    On Mon, Feb 8, 2010 at 11:25 AM, Allen, Brandon > wrote:

    Statspack works great in 10g � I�ve used it extensively.







    ------------------------------
    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
  • Kellyn Pedersen at Feb 9, 2010 at 6:07 pm
    "You have to use AWR and disable STATSPACK. "

    Why do you have to disable statspack if you are licensed for AWR?  I had both running at a previous company.  I preferred the AWR reports, being the new DBA and the other DBA was into his statspack reports, (I work with him again here at I-behavior, so he's since converted to AWR... :))  The snapshot ID's are completely different for each, use different views and I never had them interfere with each other in anyway in regards to reporting purposes...
    Â
    Please elaborate, I'm curious... Thanks!

    Kellyn Pedersen
    Multi-Platform DBA
    I-Behavior Inc.
    http://www.linkedin.com/in/kellynpedersen
    www.dbakevlar.blogspot.com
    Â
    "Go away before I replace you with a very small and efficient shell script..."

    On Mon, 2/8/10, Surachart Opun wrote:

    From: Surachart Opun
    Subject: Re: STATSPACK in 10g
    To: jsidba_at_gmail.com
    Cc: Brandon.Allen_at_oneneck.com, "istvan5.toth_at_ge.com", "oracle-l_at_freelists.org"
    Date: Monday, February 8, 2010, 8:59 PM
    If licensed for Diagnostics Pack, does it make sense to run both AWR & STATSPACK?
    You have to use AWR and disable STATSPACK.

    and AWR have more feature than STATSPACK.

    Surachart Opun
    http://surachartopun.com

    On Tue, Feb 9, 2010 at 2:45 AM, Jonathan Intner wrote:

    Hi All:

    Not meaning to "hijack" this thread, but I have a question/comment that I think is related. The question is:

    If licensed for Diagnostics Pack, does it make sense to run both AWR & STATSPACK? I would actually argue that if AWR is available STATPACK is redundant. I'd love to hear what other folks think. If I get a number of private replies, I'll summarize the results for the list.

    Thanks in advance,

    Jonathan

    On Mon, Feb 8, 2010 at 11:25 AM, Allen, Brandon wrote:

    Statspack works great in 10g – I’ve used it extensively.
    Â
    Â

    Â

    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.
  • Greg Rahn at Feb 9, 2010 at 11:12 pm
    In early releases of 10.1 or 10.2 (cant quite recall) there were bugs
    related to running both AWR and STATSPACK that could cause contention
    for certain latches in the sql area. This was exacerbated by the fact
    that both snapshots ran at exactly the same time; at the top of the
    hour. Thus it was advised not to run both of them. I believe that
    these bugs have been all resolved in 11g.
    On Tue, Feb 9, 2010 at 10:07 AM, Kellyn Pedersen wrote:

    "You have to use AWR and disable STATSPACK. "
    Why do you have to disable statspack if you are licensed for AWR?  I had both running at a previous company.  I preferred the AWR reports, being the new DBA and the other DBA was into his statspack reports, (I work with him again here at I-behavior, so he's since converted to AWR... :))  The snapshot ID's are completely different for each, use different views and I never had them interfere with each other in anyway in regards to reporting purposes...

    Please elaborate, I'm curious... Thanks!
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Kellyn Pedersen at Feb 10, 2010 at 4:58 am
    Since I have always upgraded to 10.2 so fast I made other DBA's head's spin and I have had the combined hourly statspack/AWR scenario running in multiple 10g environments, my money is on 10.1 for the bug... :)

    Kellyn

    On Tue, 2/9/10, Greg Rahn wrote:

    From: Greg Rahn
    Subject: Re: STATSPACK in 10g
    To: kjped1313_at_yahoo.com
    Cc: oracle-l@freelists.org
    Date: Tuesday, February 9, 2010, 4:12 PM

    In early releases of 10.1 or 10.2 (cant quite recall) there were bugs
    related to running both AWR and STATSPACK that could cause contention
    for certain latches in the sql area.  This was exacerbated by the fact
    that both snapshots ran at exactly the same time; at the top of the
    hour.  Thus it was advised not to run both of them.  I believe that
    these bugs have been all resolved in 11g.
    On Tue, Feb 9, 2010 at 10:07 AM, Kellyn Pedersen wrote:

    "You have to use AWR and disable STATSPACK. "
    Why do you have to disable statspack if you are licensed for AWR?  I had both running at a previous company.  I preferred the AWR reports, being the new DBA and the other DBA was into his statspack reports, (I work with him again here at I-behavior, so he's since converted to AWR... :))  The snapshot ID's are completely different for each, use different views and I never had them interfere with each other in anyway in regards to reporting purposes...

    Please elaborate, I'm curious... Thanks!
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Daniel Fink at Feb 10, 2010 at 12:59 pm
    There was/is a known bug with a shared pool latch that impacts Statspack
    at level 6 (gathering execution plan info). Gathering snapshots at this
    level would lock up the database faster than you could say "Bob's your
    uncle". The bug was not a Statspack or AWR bug, but an internal one.

    The frustrating part of this bug was that it did not impact every
    database and there did not seem to be a way to determine which database
    might and might not be impacted by the bug. I worked with 2 high
    volume/high transaction databases...same version...one ran Snapshots at
    level 6 without any problem...one would become unusable until you
    restarted the instance.

    --
    Daniel Fink

    OptimalDBA http://www.optimaldba.com
    Oracle Blog http://optimaldba.blogspot.com

    Lost Data? http://www.ora600.be/

    Greg Rahn wrote:
    In early releases of 10.1 or 10.2 (cant quite recall) there were bugs
    related to running both AWR and STATSPACK that could cause contention
    for certain latches in the sql area. This was exacerbated by the fact
    that both snapshots ran at exactly the same time; at the top of the
    hour. Thus it was advised not to run both of them. I believe that
    these bugs have been all resolved in 11g.
    On Tue, Feb 9, 2010 at 10:07 AM, Kellyn Pedersen wrote:

    "You have to use AWR and disable STATSPACK. "
    Why do you have to disable statspack if you are licensed for AWR? I had both running at a previous company. I preferred the AWR reports, being the new DBA and the other DBA was into his statspack reports, (I work with him again here at I-behavior, so he's since converted to AWR... :)) The snapshot ID's are completely different for each, use different views and I never had them interfere with each other in anyway in regards to reporting purposes...

    Please elaborate, I'm curious... Thanks!
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Feb 10, 2010 at 2:57 pm
    I think there are two things,

    One was a bug which was fixed in 9.2.0.7 I think. When a nested loop access
    was used on indexed access path to an underlying X$ under V$SQL_PLAN then
    sometimes that loop went crazy and never returned from FIXED TABLE access -
    which meant that the library cache latch held for that access was never
    released. Anyone who wanted to use that library cache latch would get hung
    and eventually it was almost the whole database.

    Insted of restart, if you killed the process holding that latch, PMON would
    have cleaned up after it, the instance should have resumed...

    The other thing which I think Greg refers to is the introduction of
    V$SQLSTATS in 10.2 - and statspack/AWR are using that now instead of V$SQL.

    If you query top SQL from V$SQL, you have to loop through the entire library
    cache, all cursors structures in there as the execution stats are kept
    inside the cursors themselves.

    To do so, you need to take and hold library cache latches. This means that
    your business activity is throttled by the monitoring activity in the
    system. That's why many DBA "performance" tools in past actually
    *caused*performance problems as they polled through V$SQL too
    frequently. V$SQLAREA
    is as bad as V$SQL btw as it's just a group by on V$SQL.

    And then came V$SQLSTATS - it's a *separate array* and protected by cursor
    stat mutexes instead of latches. But the key is that it is a separate array
    maintained by Oracle's SQL execution engine. So when you query V$SQLSTATS,
    you don't need to walk through the complex structures of library cache, but
    just scan through the separate array of fixed width records, this is much
    cheaper and doesn't need to take any library cache latches (or library cache
    mutexes in 11g+)

    So, if you're on 10.2+, forget V$SQL and V$SQLAREA and query V$SQLSTATS
    only. On the other hand, V$SQLSTATS does not have all the columns you have
    in V$SQL, so there are some special cases where the old views are still
    needed.

    --
    Tanel Poder
    http://tech.e2sn.com
    http://blog.tanelpoder.com
    On Wed, Feb 10, 2010 at 8:59 PM, Daniel Fink wrote:

    There was/is a known bug with a shared pool latch that impacts Statspack
    at level 6 (gathering execution plan info). Gathering snapshots at this
    level would lock up the database faster than you could say "Bob's your
    uncle". The bug was not a Statspack or AWR bug, but an internal one.

    The frustrating part of this bug was that it did not impact every database
    and there did not seem to be a way to determine which database might and
    might not be impacted by the bug. I worked with 2 high volume/high
    transaction databases...same version...one ran Snapshots at level 6 without
    any problem...one would become unusable until you restarted the instance.

    --
    Daniel Fink

    OptimalDBA http://www.optimaldba.com
    Oracle Blog http://optimaldba.blogspot.com

    Lost Data? http://www.ora600.be/




    Greg Rahn wrote:

    In early releases of 10.1 or 10.2 (cant quite recall) there were bugs
    related to running both AWR and STATSPACK that could cause contention
    for certain latches in the sql area. This was exacerbated by the fact
    that both snapshots ran at exactly the same time; at the top of the
    hour. Thus it was advised not to run both of them. I believe that
    these bugs have been all resolved in 11g.

    On Tue, Feb 9, 2010 at 10:07 AM, Kellyn Pedersen wrote:


    "You have to use AWR and disable STATSPACK. "
    Why do you have to disable statspack if you are licensed for AWR? I had both running at a previous company. I preferred the AWR reports, being the new DBA and the other DBA was into his statspack reports, (I work with him again here at I-behavior, so he's since converted to AWR... :)) The snapshot ID's are completely different for each, use different views and I never had them interfere with each other in anyway in regards to reporting purposes...

    Please elaborate, I'm curious... Thanks!


    --
    Regards,
    Greg Rahnhttp://structureddata.org
    --http://www.freelists.org/webpage/oracle-l
    --
    Tanel Poder
    http://blog.tanelpoder.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Yong Huang at Feb 11, 2010 at 5:38 pm
    Speaking of v$sql_plan in 9i, I have in my own notes the following.

    "Don't select * from v$sql_plan in 9i. Selecting certain columns may
    cause ORA-600 [504] [row cache objects]. Filter_predicates column
    should be avoided (see Bug:3545517). That column could also cause
    ORA-7445 (Doc:376923.995) and ORA-3113 (Bug:4035880).
    Access_predicates column may also be bad (inferred from Note:340090.1)"

    Basically, if you avoid selecting filter_predicates and
    access_predicates columns, it should be fine. I have no technical proof.
    Just empirical observation based on many times of query of that view
    on high concurrent databases.

    Yong Huang

    Original message -----

    One was a bug which was fixed in 9.2.0.7 I think. When a nested loop access
    was used on indexed access path to an underlying X$ under V$SQL_PLAN then
    sometimes that loop went crazy and never returned from FIXED TABLE access -
    which meant that the library cache latch held for that access was never
    released. Anyone who wanted to use that library cache latch would get hung
    and eventually it was almost the whole database.
  • John Kanagaraj at Feb 15, 2010 at 4:29 am
    Hi all,

    Coming back to original STATSPACK vs. AWR - One of the major
    differences in the way Top SQL is collected in STATSPACK as compared
    to AWR is this: With STATSPACK, V$SQLSTATS is *scanned* against a
    configured lower limit for Buffer Gets, Disk reads, etc to collect the
    list of SQL_IDs for "Top SQL". Once this is captured, the *difference*
    in buffer gets, etc. for said SQL_IDs is calculated. In this case,
    when you have a *stable* SQL Shared pool with cursors that have
    collected large values of buffer gets, phys reads etc. over a period
    of time, it is possible that this scan misses the *real* SQL culprits
    that appears in a given period, creates enough bur real damage (but
    just not enough to get to the top of chart in this well established
    SQL cursor list) and age out of the Shared pool. AWR on the other
    hand, via ASH should have continually collected the real baddies. I
    believe AWR would still scan V$SQLSTATS to determine the stats
    difference for SQL, but I believe this list should be different, and
    much more closer to the real set of statements that ran in that
    period. (I have pasted a relevant portion of the code from a SP 10.2.
    spcpkg.sql - very revealing read indeed!)

    If anyone has AWR *and* STATSPACK configured to run simultaneously,
    can they verify this? I don't have a live instance where I can verify
    this.

    --
    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 **, max(force_matching_signature) force_matching_signature, max(last_active_time) last_active_time
    from v$sql sql
    where is_obsolete = 'N'
    and sql_id in (select /*+ unnest full (sqlstats) */
    sql_id
    from stats$v$sqlstats_summary sqlstats
    where ( buffer_gets > l_buffer_gets_th
    or disk_reads > l_disk_reads_th
    or parse_calls > l_parse_calls_th
    or executions > l_executions_th
    or sharable_mem > l_sharable_mem_th
    or version_count > l_version_count_th
    )
    )
    group by old_hash_value, address;
    --
    http://www.freelists.org/webpage/oracle-l
  • Karl Arao at Feb 15, 2010 at 5:08 am
    I think AWR uses dba_hist_sqlstat and dba_hist_sqltext...

    The code is under prvtawr.plb under the DBMS_SWRF_REPORT_INTERNAL
    package body...

    Karl Arao
    karlarao.wordpress.com

    On Mon, Feb 15, 2010 at 12:29 PM, John Kanagaraj
    wrote:
    Hi all,

    Coming back to original STATSPACK vs. AWR - One of the major
    differences in the way Top SQL is collected in STATSPACK as compared
    to AWR is this: With STATSPACK, V$SQLSTATS is *scanned* against a
    configured lower limit for Buffer Gets, Disk reads, etc to collect the
    list of SQL_IDs for "Top SQL". Once this is captured, the *difference*
    in buffer gets, etc. for said SQL_IDs is calculated. In this case,
    when you have a *stable* SQL Shared pool with cursors that have
    collected large values of buffer gets, phys reads etc. over a period
    of time, it is possible that this scan misses the *real* SQL culprits
    that appears in a given period, creates enough bur real damage (but
    just not enough to get to the top of chart in this well established
    SQL cursor list) and age out of the Shared pool. AWR on the other
    hand, via ASH should have continually collected the real baddies. I
    believe AWR would still scan V$SQLSTATS to determine the stats
    difference for SQL, but I believe this list should be different, and
    much more closer to the real set of statements that ran in that
    period. (I have pasted a relevant portion of the code from a SP 10.2.
    spcpkg.sql - very revealing read indeed!)

    If anyone has AWR *and* STATSPACK configured to run simultaneously,
    can they verify this? I don't have a live instance where I can verify
    this.

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

    , max(force_matching_signature) force_matching_signature
    , max(last_active_time)       last_active_time
    from v$sql sql
    where is_obsolete = 'N'
    and sql_id in (select /*+ unnest full (sqlstats) */
    sql_id
    from stats$v$sqlstats_summary sqlstats
    where (   buffer_gets   > l_buffer_gets_th
    or disk_reads    > l_disk_reads_th
    or parse_calls   > l_parse_calls_th
    or executions    > l_executions_th
    or sharable_mem  > l_sharable_mem_th
    or version_count > l_version_count_th
    )
    )
    group by old_hash_value, address;
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 6, '10 at 1:06a
activeFeb 15, '10 at 5:08a
posts12
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase