Re: STATSPACK interpretation

I recall James Morle saying something about code not being sharable if the declared sizes of the bind variables don't match. If Informatica is using a 3GL to call anonymous pl/sql blocks with different bind variables every time, perhaps it is causing a bind variable mismatch.

As for the 400MB - I've often noticed oddities where a new entry is created, but "carries forward" a report of the memory requirements of earlier variants, so if you have 10 cursors, they don't report 10 units of memory, but 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is possible that you are seeing some effect like this.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person   who can answer the questions, but the   person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 23, 2003 5:09 PM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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