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
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.
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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"
> <ORACLE-L_at_fatcity.com>
> 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: 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 - 14:44:25 CST
