Skip to Main Content

how to get usage rate of indexes

2690604Aug 5 2015 — edited Sep 10 2015

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

Comments
Post Details
Added on Aug 5 2015
17 comments
24,458 views