| Would my SQL work to record which indexes are being used [message #206545] |
Thu, 30 November 2006 08:34 |
aidi-h
Messages: 45
Registered: November 2005
|
Member |
|
|
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
