statspack or internal routines peculiar issue!!

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Wed, 14 Jul 2004 12:04:58 -0500
Message-id: <006c01c469c4$b20fe870$212f200a@rshamsudxp>

Attached file included as plaintext by Ecartis --

Hi list

I have a problem with either internal stats collection routine or statspack inserts. During one of our performance test, following SQL was highlighted in the statspack report as a candidate for further research: Statspack reported this SQL with 1.1 Million average buffer gets.

Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------

299,395,238 250 1,197,581.0 135.6 2762274592 select distinct(po_ind) from worklist_vw where source_id = 14000 AND PO_IND IS NOT NULL Running this SQL from the sqlplus results in 11000 buffer gets approximately, with a subsecond response. Luckily, We had tracing on for the duration of the test and grepping through the trace file I can see that the above SQL was in 5 trace files with the OS PIDs: 8868, 8872, 8874, 8876 and 8880.

>From the trace files, we can see that:

Sum(executions) from the above 5 trace files is 250 = 132+25+39+29+25. This is matching with statspack executions. So, we are not missing any executions from the statspack.
Average buffer gets for each execution is 11304 from the tkprof output:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Fetch 132 28.84 29.63 0 1492129 5280 132
Fetch 25 5.58 5.70 0 282601 1000 25
Fetch 39 8.58 8.86 0 440856 1560 39
Fetch 29 6.32 6.44 0 327816 1160 29
Fetch 25 5.57 5.68 0 282600 1000 25

3. Looking at the underlying tables perfstat.stats$summary: snap_id 2692 has 1500 executions, but all of the buffer_gets, disk_reads,parse_calls, rows_processed all set to 0 except the execution. Snap_id 2693 has executions 1725 and buffer_Gets 299111638. So, of course, spreport considered this 225 executions and 299111638 buffer gets as bad and pushed to top of the list. Remaining 25 executions were in the subsequent snaps.

So, bottom-line is that either statspack while inserting to the stats$summary table inserted with 0 values or data from those v$views (v$sql) had 0 values. Have you ever encountered this peculiar behavior ? We are planning to restart the database tonight to see whether this will go away or not..There are 7similar SQLs (with different source_id of course ) with the same problem.

BTW, DB is 8.1.7.4 64 bit on Solaris 8. No special options such as RAC..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA (7,7.3,8,8i and 9i)

Attached file included as plaintext by Ecartis --
Desc: Signature

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

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 Jul 14 2004 - 12:03:46 CDT