Re: STATSPACK in 10g

  • From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 10 Feb 2010 05:59:46 -0700

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 ran Snapshots at level 6 without any would become unusable until you restarted the instance.

Daniel Fink

Oracle Blog

Lost Data?

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 <kjped1313@xxxxxxxxx> 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!

Greg Rahn

Other related posts: