Jonathan, Wouldn't bind variable issue that prevents cursor from sharing be visible in bind_mismatch? How can one simulate this?
var v varchar2(1) begin select count(5) into :v from dual; end; / select address, sql_text from v$sql where sql_text like '%count(5)%'; ADDRESS SQL_TEXT -------- --------------------------------------------------------------------------- 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; -- Change a bind variable size: var v varchar2(30) begin select count(5) into :v from dual; end; / -- same output, no change, both sql and pl/sql wrapper cursors are still shared -- Change a bind variable type: var v number begin select count(5) into :v from dual; end; / ADDRESS SQL_TEXT -------- ---------------------------------------------------------------------------- 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; 6DE960D0 begin select count(5) into :v from dual; end; -- ok, here pl/sql parent (dep=0) cursor is no longer shared [EMAIL PROTECTED]> select * from v$sql_shared_cursor where kglhdpar = '6DE960D0'; ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F -------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 2 rows selected. -- yep, bind variables mismatch Thanks, Boris Dali. --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > 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). ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
