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 <jonathan_at_jlcomp.demon.co.uk>
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"
> <ORACLE-L_at_fatcity.com>
> 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
> >
> > sys_at_tvis> 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: 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).
Post your free ad now!
http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
INET: boris_dali_at_yahoo.ca
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).
Received on Tue Dec 23 2003 - 17:44:25 CST
