Home » RDBMS Server » Performance Tuning » Would my SQL work to record which indexes are being used
Would my SQL work to record which indexes are being used [message #206545] Thu, 30 November 2006 08:34
Messages: 44
Registered: November 2005
I would like to monitor the amount of times that my indexes are used.
( I tried the 'alter index <index_name> monitoring usage' command but this will only give me a used yes or no value.)

I have written the following sql to extract indexes from v$sql_plan.
select i.owner, i.table_name, s.object_name,count(*) cnt1
from v$sql_plan s join dba_indexes i on s.object_name = i.index_name and s.object_owner = i.owner
group by i.owner,i.table_name, s.object_name
order by cnt1

a) Would this be a suitable method of finding out which indexes are being used and the frequency?
b) How long does the SQL stay in the library cache i.e. are the records in SQL_PLAN, records of the SQLS that are running at this moment in time or could they be old SQL's.
c) When do the records in SQL_PLAN get cleared out?
Previous Topic: Index Rebuild Taking Long
Next Topic: Help Help me plz urgent
Goto Forum:

Current Time: Thu Oct 27 00:56:09 CDT 2016

Total time taken to generate the page: 0.10934 seconds