RE: STATSPACK interpretation

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Tue, 23 Dec 2003 04:44:25 -0800
Message-ID: <F001.005DAE68.20031223044425@fatcity.com>

Jared,

Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows:

For each row in (some view)

   Call generate_product_keys
   MERGE (upsert) into product table
end loop

CREATE OR REPLACE PROCEDURE generate_product_keys

(v_marketing_model_id IN VARCHAR2,
 v_model_country_cd   IN VARCHAR2,
 v_product_seq_id     IN OUT NUMBER,
 v_product_id         IN OUT NUMBER) IS

BEGIN
IF v_product_seq_id IS NOT NULL THEN

   NULL;
ELSE
   SELECT seq_product_seq_id.nextval

     INTO v_product_seq_id
     FROM dual;
   BEGIN
     SELECT product_id
        INTO v_product_id
        FROM product
        WHERE marketing_model_id = v_marketing_model_id 
          AND model_country_cd = v_model_country_cd;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        SELECT seq_product_id.nextval
          INTO v_product_id
          FROM dual;

   END;
END IF;
END generate_product_keys;
/

Thanks.

-----Original Message-----
Jared Still
Sent: Monday, December 22, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Jeff
  INET: jeff.thomas_at_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_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).

Received on Tue Dec 23 2003 - 06:44:25 CST