查看oracle索引状态,如何查看所有索引的使用情况，好删除那些不常使用的索引！...

Monitoring

The simplest use for monitoring is to tell if an index is used or not. When an index is monitored as being used the V$OBJECT_ USAGE.USED column will contain a YES value. An index not used after monitoring over a period of time could possibly be dropped; preferably do not drop foreign key indexes.

Tip  Monitoring applies to both tables and indexes. However, when testing on my database I got absolutely no sensible response to monitoring of tables for DML activity.

Let's experiment. This query creates a script allowing monitoring of indexes for all the tables in the Accounts schema.

DECLARE

CURSOR cIndexes IS

SELECT index_name FROM user_indexes

WHERE index_type != 'LOB';

BEGIN

FOR rIndex IN cIndexes LOOP

EXECUTE IMMEDIATE 'ALTER INDEX

'||rIndex.index_name ||' MONITORING

USAGE';

END LOOP;

END;

/

The result will be something like this:

ALTER INDEX XFK_COA# MONITORING USAGE;

ALTER INDEX XFK_SM_STOCK MONITORING USAGE;

ALTER INDEX XPK_GENERALLEDGER MONITORING USAGE;

ALTER INDEX XPK_STOCKMOVEMENT MONITORING USAGE;

¼

Now I change all these tables with the previous script and then execute my DBMS_JOBS package scripting to start up my highly active concurrent OLTP database. After that I query the V$OBJECT_USAGE performance view and see if I can find any unused indexes.

SQL> SELECT COUNT(*) FROM v$object_usage WHERE used = 'NO';

COUNT(*)

-----------

42

Many of the indexes I have in my Accounts schema are not used.

Tip  Foreign key indexes will not be flagged as used when utilized for validation of Referential Integrity. Be careful not to remove foreign key indexes. You will get a lot of performance problems if you do. When foreign keys are not indexed, table exclusive locks are extremely likely. Most of the unused indexes in my Accounts schema are foreign key indexes.

I could use a query such as this to find exact details of monitoring.

SELECT start_monitoring "Start", end_monitoring "End",

used

,index_name "Index" FROM v$object_usage WHERE used = 'NO';

It is important to tuning to drop unused indexes because a DML operation executed on a table results in a DML operation on the table plus all its indexes. The fewer the objects requiring changes the faster DML activity will be. Note that indexing usually speeds up data retrieval so it is sensible to weigh between DML, SELECT activity and the need for foreign key indexes when deciding on the necessity for an index.