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: [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
> --------------------------------------------
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
