Sterling Selling and Fulfillment Foundation schema: Oracle index monitoring and tuning

As we mentioned in Indexes, you may have to adjust the base starting index set to suit your operational environment. You can find out what indexes are used (and by corollary, which ones are not used) through index monitoring. To enable index monitoring, issue the following commands, one for each index:

   ...
   alter index yfs_order_header_pk monitoring usage;
   alter index yfs_order_header_i1 monitoring usage;
   alter index yfs_order_header_i2 monitoring usage;
   ...
   

You can generate the command above by issuing the following query:

   select 'alter index ' || index_name || ' monitoring usage;' 
   from user_indexes;

Periodically, as you run your functionality and system test, you can run the following query to see if which indexes have been used and which have not yet been used:

   select index_name, monitoring, used, start_monitoring
   from v$object_usage;
   
   INDEX_NAME           MONITORING  USED  START_MONITORING     
   -------------------  ----------  ----  -------------------
   YFS_ORDER_HEADER_I1  YES         YES   01/29/2011 01:23:03
   

To turn off index monitoring, issue the following command:

   alter index yfs_order_header_i1 nomonitoring usage;