Thanks Tom...Can the information on how many times a package is called, be used in any manner you can think of, for performance analysis, capacity planning or anything like that? The only use I am aware of is for finding parse/execute ratio.

Thanks again.

Tom
I tried to get the execute counts from v$sql over a period of 8 hours. The difference of some counts is showing as negative. Does this mean that the packages were aged out and reloaded?
Thanks

This reminds me of a quote I read when the writer was
trying to convey the uselessness of these types of
statistics. It was something like "If I drive home,
and I stop at 500 red lights, what does that tell you?"
(Either the writer was T.Kyte and the original source was
J.Lewis, or vice versa. I don't remember now.)

Anyhow, the point is, the number of executions, even if
measured accurately, tells you NOTHING about the impact of
those executions on system performance.

PS I just had a great week here w/ Jonathan. He came in,
did the keynote at the local OUG and then did his seminar.
Wow, lots of good stuff!

Tom
If the shared pool items are subject to deallocation, then how does statspack give me a correct report? If a parsed sql statement is aged out of shared pool, then it's dynamic performance statistics are also gone (my assumption). Since the statspack reports are based on the difference between two point in time snapshots, how does statspack maintain accuracy?
Thanks

Hi

I always wondered how do we know which package or procedure or function does the SQL statments we see in v$sql belong to.... I always have to get the sql and do something like

select * from dba_source where text like '%SQL_STMT%'

not very efficient really :(

Hi Tom,
I wonder how EM (Oracle 10gR2) can find to which stored procedure a sql statement belongs.

EM:Performance:Top activity:SQL ID (SQL type=SELECT):Statistics

And there is a section General :

Module w3wp.exe
Action
Parsing Schema ITADTS
PL/SQL Source (Line Number) ETWMD2.PCK_INOUT_ETWMD2_COMM (1418)

It would be extremelly useful to know how to find this without using EM.

Thanks,

Dusan

Hi Team,

First of all, thanks for all the good work you are doing.

Request your help with a question on auditing. Can we track how many times a packaged function was executed using Oracle Auditing or by any other method ?

I am aware that we can set audit_trail parameter and then audit execute on the package. However this does not gives the exact count. For example, consider the following statement:

select pack_secure.func_decryptedvalue(encryptedtext)
from table_100ROWS;

This SQL will invoke the function multiple times. However, DBA_AUDIT_TRAIL will have only a single entry. Any way to know that the function was called 100 times?

One way to do so will be to code the audit logic in the function. However, the sub-routine will be invoked very heavily and will complete in milliseconds. We are looking for a way with minimum performance penalty.

Thanks,
AB