RE: explain plan, can you explain this?

  • From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
  • To: <john.kanagaraj@xxxxxxxxx>, <dannorris@xxxxxxxxxxxxx>
  • Date: Thu, 10 Jan 2008 14:30:28 +0200

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

... lpad(to_char((e.buffer_gets - nvl(b.buffer_gets,0))
       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?


> The very interesting part is that in a one-hour statspack, this
> generates 30 mil buffer gets, executed 111,388 times (about 273 buffer
> 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 (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 **


Other related posts: