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

Unknown

From my perspective, if INDEX is read then it is being used & you eliminate it at your own peril.

IMO, I think you are on a snipe hunt.

https://en.wikipedia.org/wiki/Snipe_hunt

Why don't you look at a statspack report & spend you efforts on resource intensive SQL reported by it?

Jonathan Lewis

With those figures I'd be curious about all the indexes you've reported at > 30%.

On the other hand I'd also be suspicious that there was something funny going on with the DDBH index - perhaps it's being used in cases where it shouldn't be, perhaps it's suffered a structural disaster.

I'd also note that you have two indexes starting with the same 4 columns and perhaps you could produce a single index that was as effective as the pair.

Then you have three indexes starting with dp_date_time_date, and perhaps with some column re-arrangement one would be sufficient.

I'd also be looking at opportunities for index compression

These things take a lot of time - start with the largest tablees which show the most time spent PER ROW for inserts updates and deletes as the first tables to investigate. Use the "physical reads" as a secondary indicator because time is OFTEN (but not always) due to physical I/O

Regards

Jonathan Lewis

2690604

Thanks Jonathan.

1)indexes you've reported at > 30%

I also use the first query at the begining of this thread, find indexes >30% also have never been written into DBA_HIST_SQL_PLAN. This will make me more confident to drop these indexes.

2)On the other hand I'd also be suspicious that there was something funny going on with the DDBH index

DDBH index(SYS_C007990) is primary index.

3)I'd also note that you have two indexes starting with the same 4 columns and perhaps you could produce a single index that was as effective as the pair.  

We will combine indexes IDX_KH_KHDD_DCCP and IDX_KH_KHDD_DCCD into 1.

4)Then you have three indexes starting with dp_date_time_date, and perhaps with some column re-arrangement one would be sufficient. 

Seemingly index IDX_DP_DATETIME_NEW2 can be drop.

Regards

Li

jgarry

Are these tables being used in an OLTP manner, or are they some kind of aggregated reporting tables loaded in batches?

2690604

these tables are used in an OLTP manner

IonutC

I am also trying to identify indexes that can be dropped.

I have read the Jonathan Lewis posts regarding this and how we may identify possible indexes to drop based on v$segment_statistics and the rate between "db block changes" and "logical reads".

I checked in my environment and noticed something strange. I have indexes that show these situation in statistics :

1/ "logical reads" = 0 and "db block changes" has some value >0:

db block changes  logical reads
16 0 38 0 736 0

2/ "logical reads" = 0 and "db block changes" = 0

3/  "db block changes"= 0 and logical reads has a value > 0

4/  both have values > 0

What I do not really get are points 1 and 2. Basically :

     a/ How can an object show 0 logical reads and some db block changes?

     b/ Both being 0 indicates to me that the index was never touched ? Is this correct?

     c/ Logical reads with a value while db block changes has 0 means to me that the index is only used for queries but never updated ? Is this correct?

Can someone shed some light if I got a good understanding on this?  I want to move on and monitor the indexes with logical reads = 0 first since for me this means that these indexes were never read. I miss something here?

Thanks,

IonutC

Jonathan Lewis

If you check v$segstat_name you'll see that "logical reads" is one of the statistics that is sampled - i.e. it's not 100% accurate - so for objects subject to little activity it's not surprising that you can see small numbers for db block changes and smaller (even zero) values for the logical reads.

If db block changes is zero then the index isn't being modified - which may indicate that that the indexed columns are usually NULL; but a zero in the logical reads doesn't guarantee that there have been no queries against the index there may have been a very small number. Remember that when you use "statistical methods" to reach a decision it's safest to apply them to large data sets, they're generally not safe when applied to small data sets.

Regards

Jonathan Lewis