Re: explain plan, can you explain this?

From: Dan Norris <dannorris@xxxxxxxxxxxxx>
To: Oracle L <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 9 Jan 2008 21:52:04 -0800 (PST)

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@xxxxxxxxx>
To: dannorris@xxxxxxxxxxxxx
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
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 **

Re: explain plan, can you explain this?

Other related posts: