Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
how to get usage rate of indexes

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
Answers
-
Hi,
If your database is 10g or above, you could use Index Monitoring. https://oracle-base.com/articles/10g/index-monitoring
-
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
-
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';
-
Thanks for your reply.
But we don't come to get a good plan for the index usage.
-
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.
-
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
-
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
-
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
-
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.
-
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