Thanks, Jonathan. Of course you are right :-) Playing with this a little longer, I can get up to 4 versions (child_number from 0 to 3) of the same pl/sql cursor by changing bind variable sizes. It ceases being sharable when bv size changes from 32 to 33, from 128 to 129 and from 2000 to 2001:
1-32 33-128 129-2000 2001-4000 I wonder if this behavior can be changed by some init settings? Thanks, Boris Dali. --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > Notes in-line. > > 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 8:44 PM > > > > Jonathan, > > > > Wouldn't bind variable issue that prevents cursor > from > > sharing be visible in bind_mismatch? > > I would certainly hope so - but I remember playing > around with v$sql_shared_cursor when it first came > out and find cases where un-shared cursors came up > with a full set of N's in the view. > > > 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 > > > > Nicely done. I think I'd run event 10046 > at level 4 as well to get the bind variable dumps > and check if the the SQL (or pl/sql) environment > was ignoring the MAXLEN value for your > variables. There are a few places where 'special > optimisations' exist in Oracle's internal coding. > > You might also try it with the most extreme > case - it may be (for example) that Oracle > rounds up varchar2() variables to 32 bytes - > I'd go for 1 and 4000 - just in case. > > > > > -- 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. > > > > -- > 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).
