RE: STATSPACK interpretation

Thomas,

The version count is the number of child cursors present in the cache for this SQL. The cursor is not being shared for some reason with 456 versions.

The 400m of memory seems a bit excessive.

There is a script at Jonathan's site with some info about v$sqlarea and a script you can run that looks at the current memory requirements for a SQL statement.

http://www.jlcomp.demon.co.uk/sqlarea.html

Does the output match what you see in statspack?

Also, the number of executions is much lower than the version count, which is rather odd. There's a bug in early 9i versions that would cause this, but was supposed to be corrected by 9.2.0.2.

In experimenting with this, I managed to get 4 different sessions to create 2 versions of a cursor. I'm not sure why as it was pl/sql and variables were used for the calling parameters.

A 'select * from v$sql_shared_cursor' did not reveal any reason for it.

After bouncing the database and trying this again, I couldn't duplicate it.

Maybe a couple of things to pursue here, but perhaps not an abundance of help. :(

Jared

On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote:
> 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).
>

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