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

Original text of this message