Re: STATSPACK in 10g
Date: Mon, 15 Feb 2010 13:08:20 +0800
Message-ID: <12ee65601002142108o3be7fd10pb3e1c242d8f6b32c_at_mail.gmail.com>
I think AWR uses dba_hist_sqlstat and dba_hist_sqltext...
The code is under prvtawr.plb under the DBMS_SWRF_REPORT_INTERNAL package body...
- Karl Arao karlarao.wordpress.com
On Mon, Feb 15, 2010 at 12:29 PM, John Kanagaraj
<john.kanagaraj_at_gmail.com> wrote:
> 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;
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 14 2010 - 23:08:20 CST