Hi everyone
In our production system, there are numbers of tables containing dozens of indexes on them. I want to find the usage rate of indexes, if some indexes is never used or seldom used, I will get to drop these indexes.
I used to rely on DBA_HIST_SQL_PLAN to find the usage rate of indexes for drop index purpose, but I found DBA_HIST_SQL_PLAN just contains the sqls in AWR report, for the sqls (which is running fast with good indexes) may not be included in the AWR and DBA_HIST_SQL_PLAN. So querying DBA_HIST_SQL_PLAN is not an accurate way to analyse all the sqls and all the indexes also well.
Could some guys give me a good way to find usage rate for all indexes? Thanks!
my query:
WITH Q AS
(SELECT S.OWNER A_OWNER,
TABLE_NAME A_TABLE_NAME,
INDEX_NAME A_INDEX_NAME,
INDEX_TYPE A_INDEX_TYPE,
SUM(S.bytes) / 1048576 A_MB
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('&INPUT_OWNER')
AND I.OWNER = UPPER('&INPUT_OWNER')
AND INDEX_NAME = SEGMENT_NAME
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE)
SELECT A_OWNER OWNER,
A_TABLE_NAME TABLE_NAME,
A_INDEX_NAME INDEX_NAME,
A_INDEX_TYPE INDEX_TYPE,
A_MB MB,
DECODE(OPTIONS, null, ' -', OPTIONS) INDEX_OPERATION,
COUNT(OPERATION) NR_EXEC,
(WITH TMP AS (SELECT TABLE_NAME,
INDEX_NAME,
TO_CHAR(WM_CONCAT(COLUMN_POSITION)
OVER(PARTITION BY INDEX_NAME ORDER BY
COLUMN_POSITION)) COLUMN_POSITION,
TO_CHAR(WM_CONCAT(COLUMN_NAME)
OVER(PARTITION BY INDEX_NAME ORDER BY
COLUMN_POSITION)) COLUMN_NAME
FROM USER_IND_COLUMNS)
SELECT MAX(COLUMN_NAME) AS COLUMN_NAME
FROM TMP
GROUP BY INDEX_NAME
HAVING MAX(TABLE_NAME) = UPPER('&INPUT_TBNAME')
AND A_INDEX_NAME = INDEX_NAME) COLUMN_NAME,
'DROP INDEX ' || A_INDEX_NAME || ';' SQL_DROP
FROM Q, DBA_HIST_SQL_PLAN d
WHERE D.OBJECT_OWNER(+) = q.A_OWNER
AND D.OBJECT_NAME(+) = q.A_INDEX_NAME
AND Q.A_TABLE_NAME = UPPER('&INPUT_TBNAME')
GROUP BY A_OWNER,
A_TABLE_NAME,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB,
DECODE(OPTIONS, null, ' -', OPTIONS)
ORDER BY NR_EXEC
Regards
Li