Want to know what indexes are used or unused in an Oracle schema?
The list of indexes provided by d.side Indexes usage feature reports the usage of all indexes, based on capture content (d.side Replay or Oracle AWR).
It is related to tables that have been used during the selected period.
If a table has not been used within the period, there is no report on its indexes.
Warning 1
Oracle AWR keeps mainly consuming SQL statements.
It’s likely that efficient indexes are not visible, because not part of the [potentially] slow queries kept by AWR.
The same way, Replay capture behaviour can be tuned using ‘sqllimit’ option, to keep a more or less comprehensive set of queries.
Warning 2
If a table has been dropped between the selected period and this report, result won’t be consistent or exhaustive.
Warning 3
Some Oracle internal queries make it seems like the table has been used.
In that case, indexes list can be wrong, as it’s not based on a “true” application activity.
Samplecreate table "OWNER".CMP3$299509 nocompress tablespace "TBS" nologging as
select /*+ DYNAMIC_SAMPLING(0) FULL("OWNER"."TABLE_NAME") */ *
from "OWNER"."TABLE_NAME" sample block( 17.755) mytab ;
