Re: excess rows processed

  • From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
  • To: stalinsk@xxxxxxxxx
  • Date: Tue, 5 Jun 2007 01:41:27 -0700 (PDT)

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.

========

1) 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.

2) 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

Other related posts: