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