How to find if my Index is used ?
V$OBJECT_USAGE displays statistics about index usage
gathered from the database. You can use this view to monitor index
usage. All indexes that have been used at least once can be monitored
and displayed in this view.To populate V$OBJECT_USAGE view, you have to set monitoring on INDEX in question.
SQL>alter index AJAYT.XIE3HISTORY monitoring usage;
Index altered.
Once the Index monitoring is on, It can be viewed as below
select * from v$object_usage;
Only the owner of Index can use above command to view usage details.
What if I don't have password for schema ?
You can use proxy authentication method to login
OR
Create a view as below and then It can be used for monitoring any schema objects.
CREATE VIEW V$ALL_OBJECT_USAGE
(
OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING
)
AS
SELECT u.name,
io.name,
t.name,
DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),
DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
FROM sys.user$ u,
sys.obj$ io,
sys.obj$ t,
sys.ind$ i,
sys.object_usage ou
WHERE i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#
AND u.user# = io.owner#;
SQL>show user
USER is "SYS"
SQL>select * from v$object_usage;
no rows selected
SQL>select * from V$ALL_OBJECT_USAGE;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ ------------------------------ --- --- ------------------- -------------------
AJAYT XIE3HISTORY HISTORY YES NO 10/15/2014 15:20:47
AJAYT XIE2HISTORY HISTORY YES NO 10/15/2014 15:20:59
You can also use alternate method by querying DBA_HIST_SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL and V$SQL_PLAN
