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 

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