Re: STATSPACK interpretation

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