Re: excess rows processed

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Tue, 5 Jun 2007 01:41:27 -0700 (PDT)
Message-ID: <514132.91538.qm@web58710.mail.re1.yahoo.com>

Dear Stalin

(there's a greeting I never expected to use :-)

Any idea why rows processed is more than executions for dual query? Taken from from 10gR2 statspack snapshot for 1 hour period.

                                              CPU per    Elap per
Executions   Rows Processed  Rows per Exec   Exec (s)   Exec (s)     SQL Id
------------ --------------- -------------- ---------- ----------- -------------
   1,741,546       1,741,682            1.0       0.00        0.00 xnvyr270ynf25

SELECT 1 FROM DUAL ... yes we use weblogic application.

we got the same issue on Oracle OC4J - zillions of idiotic selects from dual "just to check the connection is alive" - see http://preferisco.blogspot.com/2006/04/is-this-oracle-wtf.html.
Are you simply seeing the occasional select which is in flight (eg the execute phase has been counted, but the fetch hasn't yet) - at the BEGINNING snap of the period - so when the delta is calculated, you include a few fetches which don't have a corresponding execute during the period; and/or is this just a figment of the fact that V$ views aren't read consistent? I would expect execute-and-fetch to be a single SQL*Net round trip - but I guess it's possible they could be counted separately.

If it's just a timing thing, you would expect that reporting across several periods to show small differences - sometimes up, sometimes down; if you report from start of day (assuming you're not 24/7) to end - ie from one quiet period through to the next - the difference should be (very close to) zero.

Regards Nigel

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

Received on Tue Jun 05 2007 - 03:41:27 CDT