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

Hi!

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

This is good for getting execution statistics from different perspective - 
which module or action takes the most resources in database:

SQL> select module, sum(buffer_gets), sum(disk_reads), sum(cpu_time), 
sum(elapsed_time)
  2  from v$sql group by module order by 4 desc;

MODULE               SUM(BUFFER_GETS) SUM(DISK_READS) SUM(CPU_TIME) 
SUM(ELAPSED_TIME)
-------------------- ---------------- --------------- ------------- 
-----------------
                               155725           11300      23365179 
350797102
sqlplus.exe                       568              51        473391 
4271968
EM_PING                           695              22        184576 
734453
SEVERITY EVALUATION               111               1         12963 
79124
                                   11               0          4756 
4756

The problem with this method is of course that if you execute an already 
cached statement with different module and action, it will still be reported 
under the cached cursor with original module and action names, as seen from 
one quick test case I did: 
http://integrid.info/how/library_cache_module_action.txt

It is not that big problem, since normally different applications tend to 
use diffent SQL statements anyway, or the colliding statements could perhaps 
be distinguished by adding a comment into the statement (and making sure 
that Oracle doesn't strip it out from it).

However I found an interesting issue, I was thinking that each child cursor 
can have a different module setting, but at least in my 10.1.0.2 W2k 
instance all the child cursors parsed will get the same module information 
as parent cursor handle, even if the child cursor has different optimizer 
environment or is accessing completely different objects! I wonder whether 
this is excpected behaviour.. some more testing needs to be done.

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

Yep, it doesn't matter how many sessions have this particular cursor open or 
executing a package, but the amount of objects in library cache does matter 
(since every cursor type object stores appinfo in it).

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

Yep, I agree, in novadays reality some extra bytes in session structure 
don't matter (I checked, in 10g one session array record doesn't fit onto a 
single 4kB page anyway ;)

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

I was looking at it purely from technical (and possibly kernel developers) 
perspective, appinfo in library cache has probably more impact on memory 
usage&performance than in session array. But from "business" perspective, I 
agree with you, more bytes in appinfo would be more helpful, especially if 
you're doing fine grained application info, by introducing multilevel 
modules and actions and as one should keep number of different library cache 
object at minimum anyway (by using bind variables and stored code).

I started writing this response about  hours ago, now that I'm finally 
completed after a lot of dumps and experiments, I have realized that module 
& action in Oracle behave way differently than I had *guessed* before - and 
that the library cache is the coolest part in Oracle kernel (not the generic 
heap manager as I had thought before).

So, now I have dumped the heaps and will pin my interest on library cache 
objects ;)

Tanel.

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

Other related posts: