Re: STATSPACK in 10g

From: John Kanagaraj <>
Date: Sun, 14 Feb 2010 20:29:32 -0800
Message-ID: <>

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 <>< (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; --
Received on Sun Feb 14 2010 - 22:29:32 CST

Original text of this message