Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 8 2015
Added on Aug 5 2015
17 comments
26,483 views