Re: explain plan, can you explain this?

From: Dan Norris <>
Date: Wed, 9 Jan 2008 21:52:04 -0800 (PST)
Message-ID: <>

Thanks to all that have responded--I'm learning a lot and some of it sounds familiar. I neglected to mention that part of the reason for my request for help from this group instead of doing 10046 and more testing is that my access to this system is somewhat restricted, so obtaining traces and the like is challenging. Anyway, I'll get back in the trenches and put some of the suggestions to use for more analysis.

If I find anything enlightening, I'll follow up here with a summary.


  • Original Message ---- From: John Kanagaraj <> To: Cc: Oracle L <> Sent: Wednesday, January 9, 2008 10:08:49 PM 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 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 (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 **

Received on Wed Jan 09 2008 - 23:52:04 CST

Original text of this message