Re: STATSPACK in 10g

From: Karl Arao <karlarao_at_gmail.com>
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-l

Received on Sun Feb 14 2010 - 23:08:20 CST