Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: November 07, 2002 - 3:36 pm UTC

Last updated: January 11, 2022 - 3:36 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

The client wants to find out how many times each package is called in a day to make decisions on database server capacity.
a) Is there a way to find out how many times a package has been called ?
b) Is there any database server capacity metric associated with how many times a package is called ?

Someone suggested to sum up the execution counts for each package from v$sqlarea and divide by the number of days the database has been up. Is this the right way ?


and Tom said...

a) you would need to enable auditing in order to do that accurately. Using executes in v$sql (not sqlarea, that is an aggregate of v$sql and slower) would be a very good second choice. You would want to "snapshot" v$sql every day at the beginning of the day so you can subtract day from day to get a picture of daily use.

b) NO, not even a teeny tiny little bit. I cannot imagine how I would use this information to begin "sizing"


  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Crystal clear answer as ever...

Arun Gupta, November 07, 2002 - 10:43 pm UTC

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 Kyte
November 08, 2002 - 7:44 am UTC

Not really -- I mean -- if I tell you PACKAGE X was executed 1000 times does that tell you anything? What conclusion can you draw from that?

You would need lots more details.

Statspack can help you determine capacity planning.
OEM (enterprise manager) and the packs it has can really do that.

How many times you executed package X? Not sure what I would do with that particular number.

One more problem

Arun Gupta, November 09, 2002 - 12:12 pm UTC

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?

Tom Kyte
November 09, 2002 - 2:58 pm UTC

could be -- yup.


Mark J. Bobak, November 11, 2002 - 2:26 pm UTC

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!

A follow up question

Arun Gupta, December 10, 2002 - 1:31 pm UTC

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?

Tom Kyte
December 10, 2002 - 2:06 pm UTC

it won't be 100% accurrate 100% of the time.

It is however more then accurate enough. Consider -- the TOP SQL is being used heavily. Odds are it won't age out.

Consider also that a statspack window should be 15 minutes or so. Not hours. So, if an important query is getting aged out in 15 minutes and it materially affects your report -- it'll show up as a massively slow system with 100% of the shared pool being used and tons of waits on the library cache! that is, you'll have so many other things wrong, that the omission of this information isn't relevant. When you fix the obviously wrong things, this will be self correcting.

what package does sql belong

A reader, April 30, 2003 - 8:05 am UTC


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 :(

Tom Kyte
April 30, 2003 - 8:29 am UTC

sql statements do not "belong" to a package or procedure -- they can be shared across a plsql package, a vb program and a java client.

fortunately, you should only need to do that infrequently so brute force is ok...

Re:what package does sql belong

Dusan, January 15, 2008 - 10:17 am UTC

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



Tom Kyte
January 15, 2008 - 2:57 pm UTC

How to get how many times a package was called - Orace Audit does not capture the count

A reader, December 17, 2021 - 4:38 pm UTC

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.

Connor McDonald
January 11, 2022 - 3:36 am UTC

v$db_object_cache may help a little in terms of trends as opposed to exact counts.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library