Monitoring Index

- Monitoring an Index determine weather the particular index is used optimally or not, so that you can drop un-necessary indexes.
- Enable Monitoring By : ALTER INDEX MONITORING USAGE ;
- Disable Monitoring By : ALTER INDEX NOMINTORING USAGE;
- Database View to Monitor Index : V$OBJECT_USAGE

 
SQL> desc v$object_usage
 Name
 ----------------------------
 INDEX_NAME          /* Index name */
 TABLE_NAME          /* Table Name */
 MONITORING          /* Monitoring on YES/NO */
 USED                /* INDEX USED YES/NO */
 START_MONITORING    /* DATE AND TIME OF START MONITORING */
 END_MONITORING      /* DATE AND TIME OF END MONITORING */

Example:

(1) To Start Monitoring

  SQL> ALTER TABLE SCOTT.EMP MONITORING USAGE;

(2) Check Status

  SQL> select * from V$OBJECT_USAGE;
 
INDEX_NAME       TABLE_NAME      MON USED START_MONITORING    END_MONITORING
--------------- ---------------- --- --- ------------------- --------------
EMPIDX2           EMP            YES NO  04/17/2003 15:20:03

Note : USED “NO” indicate, index is not used

(3) Run you application for a sufficient time to use the table and associated indexes

(4) check status again

SQL> select * from V$OBJECT_USAGE;
 
INDEX_NAME       TABLE_NAME      MON USED START_MONITORING    END_MONITORING
--------------- ---------------- --- --- ------------------- --------------
EMPIDX2           EMP            YES YES  04/17/2003 15:20:03

Note: USED “YES” indicate your application did you the index.

Switch off monitoring
(5) ALTER TABLE SCOTT.EMP NOMONITORING USAGE;

(6) Check the status

SQL> select * from V$OBJECT_USAGE;
 
INDEX_NAME       TABLE_NAME      MON USE START_MONITORING    END_MONITORING
--------------- ---------------- --- --- ------------------- --------------
EMPIDX2           EMP            YES YES  04/17/2003 15:20:03 04/17/2003 35:30:03

Note : END_MONITORING is populated with the time when you run the NOMONITORING USAGE

Important Points:
- Value is reset for a specific index , every time you specify monitoring usage.
- LOB and IOT cannot be monitored

Row Migration and Chaining

- Row Chaining : When a row is inserted into a table exceeds the size of the database block, the row will spill over into two or more blocks. When row spans into multiple blocks is called row chaining. It cause bad performance because multiple blocks must be read to return a single row
- Row Migration : When previously inserted row is updated and that row causes the to grow larger than the space available, causes row migration.
- dba_tables is used to identify row chaining and migration

Solution for row migration:
- For row migrations can be corrected by merely deleting and then reinserting the migrated rows.
- This can be done by
* export, drop or truncate and then reimport the table
* Use the ALTER TABLE ...MOVE command to rebuild the table
* Identify and then reinsert the migrated rows.

Solution for row Chaining:
Method 1 :
- Analyze the sales table

SQL> ANALYZE TABLE sales COMPUTE STATISTICS;

SQL> SELECT table_name, chain_cnt
     FROM dba_tables
     WHERE table_name = 'SALES';
  
    TABLE_NAME                    CHAIN_CNT
    ------------------------------------------
    SALES                                10

Method 2 :
- use the utlchain.sql in the $ORACLE_HOME/rdbms/admin directory
- ANALYZE TABLE sales LIST CHAINED ROWS;
- Copy chained rows into temporary table and copy it back to original table