Re: STATSPACK in 10g
- From: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 14 Feb 2010 20:29:32 -0800
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 <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (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;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
