Re: Missing Data in Statspack Report

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 23 Jun 2004 22:16:16 +0100

Notice that there has been some hard parsing
on the first tkprof output - so the statement
is not sufficient popular to be implicitly locked into
memory.

Is it possible that the cursor was invalidated or
flushed between the last execute in the trace
file and the end snapshot time ? This would be
sufficient to make it lose the significant statistics.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 22, 2004 6:43 AM
Subject: Missing Data in Statspack Report


I'm tracing a query which runs quite slowly at a certain time of day.  Here
are the tkprof details


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------  ------
----
Parse       10      0.48       0.64          0        220        280
0
Execute     10      0.02       0.01          0          0          0
0
Fetch      527     27.73     144.20      11241      13158          0
7685
------- ------  -------- ---------- ---------- ---------- ----------  ------
----
total      547     28.23     144.87      11241      13378        280
7685

Yes the first statement is in need of tuning.  But shouldn't the  select
statement being traced show up between the Last two on the statspack outlet.
It doesn't show up on the physical read list either despite apparently doing
more of them than any other process.

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------  ------
----
Parse       10      0.04       0.04          0          0          1
0
Execute     10      0.01       0.02          0          0          0
0
Fetch      527     26.08      84.62      10602      13114          1
7685
------- ------  -------- ---------- ---------- ---------- ----------  ------
----
total      547     26.13      84.69      10602      13114          2
7685


         13,364           10        1,336.4    1.8    26.27     84.86
1094517247
Module: SQL*Plus
SELECT value, timestamp, nanosecs, stat, sevr, ostat from chanar
ch_pack.archive_data_f_view WHERE pv_id = :CUR_PV_ID AND timesta mp BETWEEN
:START_TIME_ORACLE_DATE AND :END_TIME_ORACLE_DATE AND  ostat <> 1 ORDER BY
timestamp, nanosecs
----------------------------------------------------------------------------
------------------------------
Now the statement shows up.  The two statements are exactly the same
including the supplied bind variables.  Why is it on the second report and
not the first?



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: