Home » RDBMS Server » Performance Tuning » to find out how many times a table got queried
to find out how many times a table got queried [message #224159] Tue, 13 March 2007 05:37 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Hi,
i want to know how many times a specific table "Eg: EMP" is queried in my database till now.

This is required as i want to monitor if someone is querying an important table many times.

May i know how to find it (from any v$tables or in any other way).
Thanks in advance.
Re: to find out how many times a table got queried [message #224273 is a reply to message #224159] Tue, 13 March 2007 12:28 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Query V$SQL_PLAN to get the list of statements refrencing your table. Join it to V$SQL/V$SQLAREA to get a number of executions for each statement.

HTH.
Michael
Re: to find out how many times a table got queried [message #224285 is a reply to message #224273] Tue, 13 March 2007 13:43 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
For aggregate information, look at V$SEGMENT_STATISTICS.

Using this view you could see how many logical and physical reads were done against each table in your schema.

JR
Re: to find out how many times a table got queried [message #224387 is a reply to message #224285] Wed, 14 March 2007 02:38 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks.I am able to query with your inputs.
Re: to find out how many times a table got queried [message #225372 is a reply to message #224159] Mon, 19 March 2007 16:45 Go to previous message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi gkodakalla,

I think you would be better of with using audit. Thereby you insure that you don't miss a select on the table and you don't have to guess from number of reads, which is not the same as how many times select was done against your table.

Put
 audit_trail=true in you init/spfile

do
audit select on <table_name> by access whenever successful ;

and you will get a row in sys.aud$ for each time someone succesfully selects from your table, and with information about who did it, at what time and even more.
Look in the manual about audit/audit_trail and aud$

And please remember to clean up in sys.aud$ Cool

best regards
Carl Bruhn
Previous Topic: how to rewrite the outer join statement
Next Topic: STATSPACK Report
Goto Forum:
  


Current Time: Wed Dec 11 22:56:28 CST 2024