Re: explain plan, can you explain this?

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.

Thanks!
Dan

• Original Message ---- From: John Kanagaraj <john.kanagaraj_at_gmail.com> To: dannorris_at_dannorris.com Cc: Oracle L <oracle-l_at_freelists.org> Sent: Wednesday, January 9, 2008 10:08:49 PM 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 **




--
http://www.freelists.org/webpage/oracle-l