Re: How to trace what is happening inside the stored procedure

Tanel P=F5der apparently said,on my timestamp of 7/02/2005 1:51 PM:
> This is actually perfectly reasonable, because if there was a common me=
mory=20
> location for MODULE and ACTION for both sessions and library cache, the=
n=20
> what should happen to module and action values in cached cursors when=20
> session sets a new module/action and parses a new query? The old=20
> module/action values must not change, thus have to be stored independen=
tly=20
> in different locations, for each child cursor separately.

See, that is what I'm not getting. Why do old values have to be stored independently? When I call set_action, I want a new action for my package variable, in one session. That is what is needed, not a historical value. Nothing wrong with that, whatever the size may be.

To me what you described above in the multiple sqlplus rows is that each gets its own application_info and only once. It's got nothing to do with number of cursors open per session or not. It's all got to do with library cache, which you get anyways on first call to *any* package.

Of course we get one copy of the variables for each session that invokes dbms_application_info. That is perfectly acceptable and if the variables=

had reasonably sized values, it would not be much more overhead than any other package, considering source code size, parsing, etcetc.

>=20
> Now this overhead is much more important than the session array effect =
I=20
> thought of at first :)

I still don't see it that way.

--=20
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au

--
http://www.freelists.org/webpage/oracle-l