Re: STATSPACK in 10g
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: daniel.fink@xxxxxxxxxxxxxx
- Date: Wed, 10 Feb 2010 22:57:02 +0800
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 <daniel.fink@xxxxxxxxxxxxxx>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 <kjped1313@xxxxxxxxx>
> <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!
>
>
> --
> Regards,
> Greg Rahnhttp://structureddata.org
> --//www.freelists.org/webpage/oracle-l
>
>
--
Tanel Poder
http://blog.tanelpoder.com
Other related posts:
