Re: Missing Data in Statspack Report

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 2004 22:16:16 +0100
Message-ID: <00f601c45967$53e84110$7102a8c0@Primary>

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_at_slac.stanford.edu> To: <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Wed Jun 23 2004 - 16:13:19 CDT