Re: Shared pool

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Wed, 01 Feb 2006 22:16:41 GMT
Message-ID: <draEf.5733$J81.2932@trndny01>

Sybrand Bakker wrote:

>>

> The data is reliable. If you can proof otherwise, it would be nice to
> know for everyone of course.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Statspack sql reports do leave things out. The problem is in sprepins.sql. The queries for the top sql reports do a left outer join between the ending snapshot and the beginning snapshot. That means that any SQL not in the ending snapshot will not be reported on. For example if the beginning snapshot is #1 and the ending is #3, anything that was only picked up at snapshot #2 will be ignored, even if the execution time or buffer gets exceeds anything that actually gets onto the report.

This affects all versions of 9i. I haven't installed 10g anywhere to check it but a simple inspection of the queries in sprepins.sql should determine if it's affected as well.

To test:

1. take a snapshot
2. run some buffer intensive sql like... select count(*) from dba_tables, dba_tables;
3. take a snapshot
4. flush shared pool
5. take a snapshot

Run spreport for first and second snapshot. The query will appear in the report.

Run spreport for first and last snapshot. The query will *NOT* appear. :-(

-- 
To reply by email remove "_nospam"

Received on Wed Feb 01 2006 - 16:16:41 CST