Find the indexes used or not used in query

Performance Tuning

Find the List of index which have been used in last 10 days and the way they used i.e range scan, unique sacn, fast full scan

SELECT
DISTINCT sql_id,object_owner owner, OPTIONs ,
object_name,TIMESTAMP NAME
FROM
dba_hist_sql_plan
WHERE
object_owner NOT LIKE ‘%SYS%’
AND operation = ‘INDEX’
–AND object_NAME=’&INDEX’ ñfor singe index
AND TRUNC(TIMESTAMP) BETWEEN TRUNC(SYSDATE)- &startday AND TRUNC(SYSDATE)- &endday
ORDER BY 1

&owner1,&owner2 — owner name

&startday day from which you start i.e 10

&endday i.e 0

Find the index which have not been used in last 10 days

SELECT owner,table_name,index_name FROM dba_indexes WHERE owner IN(‘&owner1′,’&owner2’) AND
index_name NOT IN
(SELECT
object_name
FROM
dba_hist_sql_plan
WHERE
object_owner NOT LIKE ‘%SYS%’ AND
operation = ‘INDEX’ AND TRUNC(TIMESTAMP) BETWEEN TRUNC(SYSDATE)- &startdays AND TRUNC(SYSDATE)- &enddays
) ORDER BY 1,2