Un-used Indexes on MDP_MATRIX Consuming Resources
By JeffG-Oracle on Dec 19, 2012
Disable un-used Indexes:
As much as it is recommended to create relevant indexes, it is advised not to have too many indexes on the mdp_matrix table. Too many indexes will cause long waits on the table as indexes needs to get updated every time the table is updated. There are many seeded indexes on mdp_matrix, every out of the box data model level has an index on the matrix table. If a level is unused in the specific data model of the implementation, it is advisable to disable that index. If the customer is not sure if and how indexes are utilized, the DBA can monitor all indexes. After a few cycles of operation, the DBA should go over that list and see which indexes have not been used. Consider disabling them.
There are scripts on the net to monitor indexes or use the monitoring usage clause in the alter index statement.