Hi All,
I know index monitoring in 12.1.0.2 is not that great out of the box. I have a query that runs against the DBA_HIST_SQL_PLAN view, as below. Would this be a suitable way to get index usage? (We have the Diag+Tuning packs available to us)
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 = '&&OWNER'
AND I.OWNER = '&&OWNER'
AND INDEX_NAME = SEGMENT_NAME
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
)
SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
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
FROM Q,
DBA_HIST_SQL_PLAN d
WHERE
D.OBJECT_OWNER(+)= q.A_OWNER AND
D.OBJECT_NAME(+) = q.A_INDEX_NAME
GROUP BY
A_OWNER,
A_TABLE_NAME,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB,
DECODE (OPTIONS, null, ' -',OPTIONS)
ORDER BY
A_OWNER,
A_TABLE_NAME,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB DESC,
NR_EXEC DESC
/
Regards,
Vic
The AWR tables only capture details about "top" SQL statements. e.g. longest running, most CPU, most I/O, etc.
So fast, infrequently run statements won't appear. The kind that probably use an index you want to keep!
So I'd be wary of using this method as a way to identify unused indexes.
Remember: even with the 12.2 improvements to index monitoring, you can only prove that an index is used. It's hard to verify an index is never used.