Re: STATSPACK interpretation

What is taking place inside GENERATE_PRODUCT_KEYS() ?

Could be dynamic SQL of the worst kind in there. That is, not using bind variables.

A 10046 trace level 4 or 12 will show you what is going on there.

Jared

On Mon, 2003-12-22 at 12:39, Thomas Jeff wrote:
> 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_at_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_at_ip68001 (TNS V1-V3)
> BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END;
>
>
>
>
> --------------------------------------------
> Jeffery D Thomas
> DBA
> Thomson Information Services
> Thomson, Inc.
>
> Email: jeff.thomas_at_thomson.net
>
> Indy DBA Master Documentation available at:
> http://gkmqp.tce.com/tis_dba
> --------------------------------------------
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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_at_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).