Re: STATSPACK in 10g

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