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