In this conclusion to a three-part series, you will learn how to monitor index usage and use Oracle Text in Oracle Database XE. This article is excerpted from chapter 38 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Monitoring Index Usage
Too many indexes on a table is bad for two reasons. First, an index occupies disk space that might otherwise be better used elsewhere. Second, indexes must be updated whenever you add, delete, or modify rows in a table. So how can you be sure that an index is even used? As of Oracle9i, you can use the dynamic performance view V$OBJECT_USAGE (see Chapter 35 for more information on dynamic performance views) to track whether an index has been used for a given time period.
To turn on monitoring, use the ALTER INDEX <index name> MONITORING USAGEcommand on the new index, as follows:
ALTER INDEX employees_idx1 MONITORING USAGE;
Right after running this command, check the viewV$OBJECT_USAGEto make sure the index is being monitored:
SELECT index_name, table_name, monitoring, used, start_monitoring FROM v$object_usage WHERE index_name = 'EMPLOYEES_IDX1';
The results from this query are shown in Figure 38-7. Notice that the columnUSEDwill be set toYES whenever Oracle uses the index to access rows in theEMPLOYEEStable when you useSALARYin theWHEREclause; initially, this column is set toNOuntil the index is used.
Figure 38-7.Querying V$OBJECT_USAGE for index status
Now that the index is being monitored, wait a day, or long enough for the regular business cycles to complete at least once, and query this view again. If theUSEDcolumn has a value ofYES, as in Figure 38-8, you should probably keep the index.
Figure 38-8.Querying V$OBJECT_USAGE for index status after index usage
In any case, once you determine the index usage statistics, turn off the monitoring of the index using theNOMONITORINGkeyword:
ALTER INDEX employees_idx1 NOMONITORING USAGE;
Oracle incurs a slight overhead for every access to theEMPLOYEEStable if one of its indexes is being monitored; so if you do not need to monitor it, turn it off. Note also that sinceV$OBJECT_USAGEis a dynamic performance view, its contents are not retained after the database is shut down and restarted.
