how to get usage rate of indexes

2690604

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

Arun#

Hi,

If your database is 10g or above, you could use Index Monitoring. https://oracle-base.com/articles/10g/index-monitoring

BeGin

Hi,

You can use the index monitoring.

You start it index by index by using

alter index index_name monitoring usage

then the usage of the index is stored in v$object_usage.

Anyway be careful with these processes, you have to let it run during a long period depending on your applications, if you have some yearly processes you should let it run one year to be sure you have  all the indexes used.

Complete process there : https://oracle-base.com/articles/10g/index-monitoring

Regards,

--

Bertrand

Pini Dibask

Hello,

Although Index Monitoring feature could be a good option, bear in mind that it only tells you whether the index was being used or not (since the time you enabled the monitoring), it doesn't tell you the rate of the index usage, which SQL's used it, how did they use it, etc.

I do agree that DBA_HIST_SQL_PLAN only contains the SQL's which entered into the AWR Repository, so it's not perfect but you can also use V$SQL_PLAN in addition to the DBA_HIST_SQL_PLAN view. V$SQL_PLAN will also show you execution plans for child cursor that are still in the library cache. It may be helpful because in some cases will see execution plans that don't appear in the DBA_HIST_SQL_PLAN  because AWR snapshot didn't capture it. Obviusly, this is also limited because some execution plans may be aged out from the library cache.

You can use the following SQL for example:

select a.sql_id,sql_text,executions,loads, operation, options, projection, last_active_time

from

v$sqlarea a,v$sql_plan b

where a.sql_id=b.sql_id and a.plan_hash_value = b.plan_hash_value

and object_name='INDEX_NAME';

2690604

Thanks for your reply.

But we don't come to get a good plan for the index usage.

Unknown

2690604 wrote:

Thanks for your reply.
But we don't come to get a good plan for the index usage.

please quantify "good".

Either an INDEX is used or  it is not used; which is exactly what Index Monitoring measures.

Richard Foote-Oracle

A little something I always get people to consider is that it's really easy to just create another index, but a lot lot harder to subsequently drop them ...

A useful method of getting a good indication on just how frequently an index is used is to look at the segment statistics of your indexes (e.g. v$segment_statistics so long as statistics_level is not basic). Indexes with very large logical reads in comparison to db block changes are getting accessed for reasons other than simple index maintenance.

Of course an infrequently used index might be critical for infrequent business reasons. SQL using frequently used indexes could potentially use another existing index in an adequately efficient manner.

Cheers

Richard Foote

https://richardfoote.wordpress.com/

2690604

Thanks Richard.

I've looked for sqls with v$segment_statistics for index usage, someone said "physical reads" can indicate whether the index is used or not, but this is not useful to me. "physical reads“ can also change when the corresponding table has INSERT(or TRUNCATE...) operation without any SELECT operation on the table.I cannot rely on  "physical reads".

I also check the indexed with large logical reads as you indicated, some frequently used indexes can have large logical reads. I cannot rely on  "logical reads".

Regards

Li

Jonathan Lewis

Looking at physical reads on index segments is fairly pointless as far as deciding whether or not the index has been used in an execution plan.

Richard's point was that

a) If an index is frequently used it will probably be subject to a large number of LOGICAL reads

b) If an index is only accessed for modification then then its value for "db block changes" will be a large fraction of its logical reads

Hence

- an index with a large value for logical reads and a relatively small value for db block chagnes is probably frequently used for query.

- and index where db block changes is similar (say larger than 50% of) logical reads is probably not used

It's an observation that points you at some suspect indexes, but

1) you may have some indexes that should be used but aren't because there are other indexes that shouldn't be used but are being used in error.

2) it is possible for Oracle to use the statistics (specifically num_distinct) from an index to help pick an execution plan without actually using the index in the plan.

On top of Richard's suggestion another INDICATOR or redundancy is to find indexes where one index is the leading set of columns of another index IN THE SAME ORDER. The shorter index ought to be redundant - though the optimizer might not want to use the larger index if you drop the smaller one.

Regards

Jonathan Lewis

jgarry

There's also a slightly philosophical problem:  How do you know there aren't indices that should be used that aren't being used?  It's the converse of what Richard noted about index accumulation; maybe some were added that should have been, and yet others still should be.  It's a scoping problem - tuning queries or sets of queries for particular objects, versus looking at the usage of all indices.

It's hard if the problems are too subtle for users to complain about and the available aggregate information doesn't spotlight any problem.  You can't prove negative index usage, yet you know excess work can happen if all data access isn't optimized.

2690604

Thanks for your explanation Jonathan.

In our production DB there are some hot big tables having over 100 columns and over 20 indexes for each of them. These tables and indexes were disgined years ago, with times goes by our bussiness has changed, maybe some indexes are redundant and never used, some index are soldem used. We want to know whether there are 2 types indexes(never and seldom used) since last DB startup. If there are, we will drop these redundant indexes for more efficient DML on the big tables.That is where I come from.

-----------------------------

Hence

- an index with a large value for logical reads and a relatively small value for db block chagnes is probably frequently used for query.

- and index where db block changes is similar (say larger than 50% of) logical reads is probably not used

I trying a qurery on the big table as your suggestion as below:

select table_name  "table_name",

object_name "index_name",

(          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('KH_KHDD') AND object_name = INDEX_NAME) "column_name" , sum(DECODE(STATISTIC_NAME, 'db block changes', value, null)) "db block changes", sum(DECODE(STATISTIC_NAME, 'logical reads', value, null)) "logical reads", round(sum(DECODE(STATISTIC_NAME, 'db block changes', value, null)) / sum(DECODE(STATISTIC_NAME, 'logical reads', value, null)) * 100, 2) "d/l(%)"

from v$segment_statistics s, user_indexes i

where 1 = 1

and s.object_name = i.index_name

and s.owner = 'VEASMS'

AND s.STATISTIC_NAME IN ('db block changes', 'logical reads')

AND i.table_name = 'KH_KHDD'

group by table_name, object_name

order by "d/l(%)" desc

From the outcome of snapshot, can we say that all the indexes on table KH_KHDD are used?

Regards

Li