Re: STATSPACK in 10g

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Wed, 10 Feb 2010 05:59:46 -0700
Message-ID: <4B72ADC2.2030408_at_optimaldba.com>



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 <kjped1313_at_yahoo.com> 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
Received on Wed Feb 10 2010 - 06:59:46 CST

Original text of this message