I think what you are looking for the is the column "EXECUTIONS" in
v$sqlarea.
What you will need to find out is the SQL_TEXT is you are looking for.
Manipulate the following query to get what you want:
select t.piece, t.sql_text, a.executions, a.loads
from v$sqlare a, v$sqltext t, v$session s
where a.address = t.address
and a.address = s.sql_address(+)
order by s.username, buffer_gets, piece;
You have to order by buffer_gets. The assumption is that the SQL query
running by your package/procedure will generate a certain amount of buffers
gets (hopefully this value will be unique) and when then also sorted by
piece, you will then be displayed the SQL_TEXT associated with the unique
buffer_get and hence also obtaining the number of executions.
Later after you have "discovered" what the first line of text is, you should
be able to rewrit ethe query as somthing like the following assuming the
sql_text for piece (0) is unique:
select t.piece, t.sql_text, a.executions, a.loads
from v$sqlare a, v$sqltext t, v$session s
where a.address = t.address
and a.address = s.sql_address(+)
and t.sql_text like '<text>';
Also not these are the "IN-MEMEORY" V$ views and are constantly updated
after instance startup, my point being you will have to run this twice: Get
executions at 6am on morning or what ever, Get execution the next morning
and then subtract to get the number of execution during the 24 hours.
Good luck.
Jeff
-----Original Message-----
From: oracledba-return-122481-JEFFERY.L.SCHRENK=saic.[Email address protected]
[mailto:oracledba-return-122481-JEFFERY.L.SCHRENK=saic.[Email address protected]
n Behalf Of Udi
Sent: Tuesday, December 27, 2005 10:20 AM
To: LazyDBA Discussion
Subject: RE: counting hits in an application
That's an idea - but doesn't auditing involve inserting rows to the AUD$
table? is that substantially more efficient that just adding an INSERT
into your own code?
This is a very intensively used stored procedure that does read-only, and
I was hoping to avoid executing DML altogether. Looking for a way to get
Oracle to track it internally through some use-count counter in the buffer
where the stored procedure is cached, or something of that nature.
?????????????????
Thanks,
Udi
This message and any files or text attached to it are intended only for
the recipients named above, and contain information that may be
confidential or privileged. If you are not an intended recipient, you
must not read, copy, use, or disclose this communication. Please also
notify the sender by replying to this message, and then delete all copies
of it from your system. Thank you.
"Patterson Joel "
<oracledba-ezmlmshield-x20783580.[Email address protected]
12/26/2005 06:33 AM
To: "LazyDBA Discussion" <[Email address protected]
cc:
Subject: RE: counting hits in an application
Oracle auditing?
-----Original Message-----
From: Udi
[mailto:oracledba-ezmlmshield-x4549695.[Email address protected]
Sent: Friday, December 23, 2005 6:02 PM
To: LazyDBA Discussion
Subject: counting hits in an application
What are the recommended options of counting how many times a stored
procedure is called from an application during a 24-hour period (other
than adding an INSERT statement into the stored procedure that will
record
the DATE/TIME) ???
Thanks,
Udi
This message and any files or text attached to it are intended only for
the recipients named above, and contain information that may be
confidential or privileged. If you are not an intended recipient, you
must not read, copy, use, or disclose this communication. Please also
notify the sender by replying to this message, and then delete all
copies
of it from your system. Thank you.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page