We recently experienced a crash on our prod datewarehouse running 9.2.0.2 on
AIX 4.3.3. The cause of the crash was 4031 errors generated by background
processes (Oracle support has confirmed there is a bug involved), however,
since that crash occurred, a certain nightly batch job has slowed to a crawl.
Trying to recreate what has happened, I came across this in the STATSPACK report.
The interval for this report is 30 minutes.
Is it telling me that I have 746 versions of this call eating up 400+ mb at
the time of the snapshot? Why would that be? The procedure in question
uses bind variables.
SQL ordered by Sharable Memory for DB: DSSP Instance: DSSP Snaps: 3309 -3310
-> End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
483,580,268 57 411.8 539672786
Module: [EMAIL PROTECTED] (TNS V1-V3)
BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END;
-------------------------------------------------------------
SQL ordered by Version Count for DB: DSSP Instance: DSSP Snaps: 3309 -3310
-> End Version Count Threshold: 20
Version
Count Executions Hash Value
-------- ------------ ------------
746 57 539672786
Module: [EMAIL PROTECTED] (TNS V1-V3)
BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END;
--------------------------------------------
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.
Email: [EMAIL PROTECTED]
Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
--------------------------------------------
