Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id hBMLYUK28710 for ; Mon, 22 Dec 2003 15:34:30 -0600 X-ClientAddr: 66.27.56.212 Received: from www3.fatcity.com (rrcs-west-66-27-56-212.biz.rr.com [66.27.56.212]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id hBMLYT428705 for ; Mon, 22 Dec 2003 15:34:29 -0600 Received: (from root@localhost) by www3.fatcity.com (8.11.6/8.11.6) id hBMKaNe02016 for oracle-l@orafaq.com; Mon, 22 Dec 2003 12:36:23 -0800 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005DAA7B; Mon, 22 Dec 2003 12:39:24 -0800 Message-ID: Date: Mon, 22 Dec 2003 12:39:24 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Thomas Jeff" Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Thomas Jeff" Subject: STATSPACK interpretation Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C3C8CB.7AF4139B" ------_=_NextPart_001_01C3C8CB.7AF4139B Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable STATSPACK interpretation

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: pmdtm@ip68001 (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: pmdtm@ip68001 (TNS = V1-V3)
 BEGIN    = GENERATE_PRODUCT_KEYS (:1,:2,:3,:4)  ;   END;

           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =             &= nbsp;           &n= bsp;         

--------------------------------------------
Jeffery D Thomas
DBA
Thomson Information = Services
Thomson, Inc.

Email: = jeff.thomas@thomson.net

Indy DBA Master Documentation = available at:
http://gkmqp.tce.com/tis_dba
--------------------------------------------


------_=_NextPart_001_01C3C8CB.7AF4139B-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: jeff.thomas@thomson.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).