John, are you sure that statspack reports sql statistics cumulatively? As far as I know it lists them as delta values between the end snapshot and the begin snapshot. The package STATSPACK gets the current values from v$sql and stores them in stats$sql_summary and spreport.sql gets the difference between snapshots. From my spreport.sql from a 9.2.0.8 home: ... lpad(to_char((e.buffer_gets - nvl(b.buffer_gets,0)) ,'99,999,999,999') ,15) ... from stats$sql_summary e , stats$sql_summary b ... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of John Kanagaraj Sent: Thursday, January 10, 2008 6:09 AM To: dannorris@xxxxxxxxxxxxx Cc: Oracle L Subject: Re: explain plan, can you explain this? Dan, > The very interesting part is that in a one-hour statspack, this statement > generates 30 mil buffer gets, executed 111,388 times (about 273 buffer gets > per exec). When combined with the facts below, it becomes a puzzler: This is only slightly related to the issue, but STATSPACK's SQL is a capture of V$SQL "as-is" when the snapshot took place. SPREPORT does NOT perform a Diff as it does with other stats (i.e. end_stat - begin_stat). The 111,388 executions is *cumulative* since that SQL was last loaded/parsed. This is apparent if you look at the STATSPACK source at $OH/rdbms/admin/spcpkg.sql. You should look at Tim G's "sphistory.sql" to determine the periodic progression of execution and other stats for a given SQL. -- John Kanagaraj <>< DB Soft Inc 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 ** -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l