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.
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
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
November 09, 2002 - 2:58 pm UTC
could be -- yup.
Statistics
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
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
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
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 :(
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
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
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.
Thanks,
AB
January 11, 2022 - 3:36 am UTC
v$db_object_cache may help a little in terms of trends as opposed to exact counts.