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" <[EMAIL PROTECTED]> 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: [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).
