Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mohammad.

Asked: May 15, 2017 - 6:18 pm UTC

Last updated: May 15, 2017 - 7:35 pm UTC

Version: 12 C

Viewed 1000+ times

You Asked

I need a query to count a specific procedure calls in each session over all schemas per hour.
this procedure exist in a package which might be called concurrently by different session in different schemas.
what i'm doing now is to collect the users_executing per hour. in case you have a better solution as I expect please provide it.

best regards

and Connor said...

You can get some *approximate* information from:

v$db_object_cache
v$sqlstats

but if you really want exact counts, then you should instrument your code to collect that information. If its just one procedure you could do something like:

- call dbms_application_info.set_client_info each time you call the proc eg.

dbms_application_info.set_client_info(nvl(sys_context('USERENV','CLIENT_INFO'),0)+1)

and then on a logoff trigger, dump that to a table.

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

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