How to monitor ORACLE index usage

How to monitor ORACLE index usage
During database management and maintenance, we always encounter a problem: will the indexes we create be used by some SQL statements? Change the general expression: is the index I created an unused index (unused Indexes) Valuable? If the created index is Unused Indexes, especially the systems that do not properly plan the index or those that do not have standardized management control. It is possible that N indexes are created. In fact, some indexes are not used by any SQL statements. In this case, these redundant indexes actually cause two problems: 1. A waste of storage space, especially for large table indexes, the waste of storage space is particularly impressive; 2: Increase the overhead of DML operations (UPDATE, INSERT, DELETE. ORACLE provides the ability to monitor index usage. Alter index <index_name> monitoring usage; let's test and verify it. Create a table TEST as an example of experiment TEST.

CREATE TABLE TEST(    ID    NUMBER(10),    NAME  VARCHAR2(32));CREATE INDEX IDX_TEST_ID ON TEST(ID);INSERT INTO TEST SELECT 1001, 'Kerry' FROM DUAL UNION ALLSELECT 1002, 'Ken'   FROM DUAL UNION ALLSELECT 1003, 'Jimmy' FROM DUAL UNION ALLSELECT 1004, 'Jack'  FROM DUAL;COMMIT;execute dbms_stats.gather_table_stats(ownname => 'ETL', tabname =>'TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

Enable monitoring alter index IDX_TEST_ID monitoring usage for INDEX IDX_TEST_ID. Observe the changes in table data in V $ OBJECT_USAGE, as shown in the following figure. The value of the MONITORIN field is changed to YES, indicating that the INDEX IDX_TEST_ID has been placed in MONITORING. If the USED field is NO, this index is not USED by SQL.

SQL> COL INDEX_NAME FOR A20       SQL> COL TABLE_NAME FOR A10SQL> COL MONITORING FOR A10SQL> COL USED FOR A10SQL> COL START_MONITORING FOR A20SQL> COL END_MONITORING FOR A20SQL> SELECT * FROM V$OBJECT_USAGE;INDEX_NAME   TABLE_NAME MONITORING USED  START_MONITORING     END_MONITORING------------ ---------- ---------- ---- -------------------- ----------------IDX_TEST_ID   TEST       YES        NO   11/28/2015 14:57:41

 

In this case, run the following SQL statement. Because full table scan is used, the index IDX_TEST_ID is still not used. You can check V $ OBJECT_USAGE for verification.

SQL> SET AUTOTRACE ON;SQL> SELECT * FROM TEST WHERE ID =1001;        ID NAME---------- --------------------------------      1001 KerryExecution Plan--------------------------------------------------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost  |----------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     9 |     2 ||   1 |  TABLE ACCESS FULL| TEST |     1 |     9 |     2 |----------------------------------------------------------Note-----   - 'PLAN_TABLE' is old versionStatistics----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        578  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

 

Clip_image001 is shown below. The index IDX_TEST_ID is still not used. Clip_image002 we use the INDEX prompt to force the following SQL statement to use the INDEX IDX_TEST_ID SELECT/* + INDEX (TEST IDX_TEST_ID) */* FROM TEST WHERE ID = 1001; at this time, you will find that the USED value is changed to YES. Alter index IDX_TEST_ID nomonitoring usage; after executing the preceding command, the END_MONITORING and MONITORING values of the TEST records will be updated in the V $ OBJECT_USAGE table. If you enable index monitoring again, the system updates the value of the START_MONITORING and END_MONITORING fields (the value of END_MONITORING is NULL ). If you delete the table TEST, you will find that the table TEST record in the V $ OBJECT_USAGE object is also missing. Note: SELECT * from v $ OBJECT_USAGE; only the index information monitored by the current user can be viewed. Even sys and system users cannot view information of other users. There is a small question during the test, that is, when preparing the test environment, if you do not collect statistics on the table, even if the SQL statement performs a full table scan, you still find that the index in V $ OBJECT_USAGE is marked for use. As shown below

DROP TABLE TEST PURGE;CREATE TABLE TEST(    ID    NUMBER(10),    NAME  VARCHAR2(32));CREATE INDEX IDX_TEST_ID ON TEST(ID);INSERT INTO TEST SELECT 1001, 'Kerry' FROM DUAL UNION ALLSELECT 1002, 'Ken'   FROM DUAL UNION ALLSELECT 1003, 'Jimmy' FROM DUAL UNION ALLSELECT 1004, 'Jack'  FROM DUAL;COMMIT;ALTER INDEX IDX_TEST_ID MONITORING USAGE;SQL> SET AUTOTRACE ON;SQL> SELECT *  FROM TEST WHERE ID =1001;        ID NAME---------- --------------------------------      1001 KerryExecution Plan--------------------------------------------------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost  |----------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    31 |     2 ||   1 |  TABLE ACCESS FULL| TEST |     1 |    31 |     2 |----------------------------------------------------------Note-----   - 'PLAN_TABLE' is old versionStatistics----------------------------------------------------------          7  recursive calls          0  db block gets         10  consistent gets          0  physical reads          0  redo size        578  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL> SELECT * FROM V$OBJECT_USAGE;INDEX_NAME   TABLE_NAME MONITORING USED   START_MONITORING    END_MONITORING------------ ---------- ---------- ------  -----------    -------------------IDX_TEST_ID   TEST       YES        YES   11/28/2015 15:11:46

 

So why? The guess is that some index information is USED when parsing and generating the execution plan. As a result, the USED field in the V $ OBJECT_USAGE table is marked as YES. If we want to monitor all the indexes in the system, we can monitor all the indexes in the database using the script below. Note that we should exclude some system table indexes and LOB indexes. There are two reasons: 1: LOB indexes cannot be modified, otherwise a ORA-22864 error will be reported (ORA-22864: cannot ALTER or drop lob indexes ). 2: ORA-00701: object necessary for warmstarting database cannot be altered ORA-00701: object necessary for warmstarting database cannot be altered 00701. 00000-"object necessary for warmstarting database cannot be altered"

*Cause: Attempt to alter or drop a database object (table, cluster, orindex) which are needed for warmstarting the database.*Action: None.SET PAGES 999;SET HEADING OFF;SPOOL run_monitor.sqlSELECT   'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;'FROM   DBA_INDEXESWHERE   INDEX_TYPE != 'LOB' AND OWNER NOT IN  ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN');SPOOL OFF;@run_monitor.sql

 

In this case, we can use the following script to find out which indexes are not used. Of course, the monitoring index duration is very important. If it is too short, it may cause a problem with the queried data, it is generally recommended that you monitor the data within one week, while the OLAP system needs to extend the monitoring time as appropriate.

SELECT I.TABLE_OWNER,       T.TABLE_NAME,       I.INDEX_NAME,       U.USED,       U.START_MONITORING,       U.END_MONITORINGFROM USER_TABLES TINNER JOIN USER_INDEXES ION T.TABLE_NAME = I.TABLE_NAMEINNER JOIN V$OBJECT_USAGE UON U.TABLE_NAME    = I.TABLE_NAMEAND I.INDEX_NAME   = U.INDEX_NAMEWHERE I.TABLE_OWNER=SYS_CONTEXT('USERENV','CURRENT_USER')

 

In addition, search for SQL statements that do not use indexes. Statspack_unused_indexes. SQL

Col owner heading "Index Owner" format a30col index_name heading "Index Name" format a30set linesize 95 trimspool on pagesize 80 select * from (select owner, index_name from dba_indexes di where di. index_type! = 'Lob' and owner not in ('sys ', 'sysman', 'system', 'mdsys', 'wmsys ', 'tsmsys', 'dbsnmp ', 'outln ') minusselect index_owner owner, index_namefrom dba_constraints dcwhere index_owner not in ('sys', 'sysmanc', 'system', 'mdsys ', 'wmsys', 'tsmsys ', 'dbsnmp ', 'outln') minusselect p. object_owner owner, p. object_name index_namefrom stats $ snapshot sn, stats $ SQL _plan p, stats $ SQL _summary st, stats $ SQL _plan_us Age spuwhere st. SQL _id = spu. SQL _id and spu. plan_hash_value = p. plan_hash_valueand st. hash_value = p. plan_hash_valueand sn. snap_id = st. snap_id and sn. dbid = st. dbid and sn. instance_number = st. instance_numberand sn. snap_id = spu. snap_idand sn. dbid = spu. snap_id and sn. instance_number = spu. instance_numberand sn. snap_id between & begin_snap and & end_snapand p. object_type = 'index') where owner not I N ('sys ', 'sysman', 'system', 'mdsys', 'wmsys ', 'tsmsys', 'dbsnmp ', 'outln') order by 1, 2/here is another script used to track unused indexes and display the call count for all indexes. Most importantly, this script displays the columns referenced by multiple column indexes (this script takes a long time to run and consumes a large amount of resources .) Col c1 heading 'in in | Interval | time'format a20col c2 heading 'search Columns 'format 999col c3 heading 'invocation | Count 'format 99,999,999 break on c1 skip 2 accept idxname char prompt' Enter index Name: 'ttitle' Invocation Counts for index | & idxname' select to_char (sn. begin_interval_time, 'yy-mm-dd hh24 ') c1, p. search_columns c2, count (*) c3from dba_hist_snapshot sn, dba_hist_ SQL _plan p, dba_hist_sqlstat stwhere st. SQL _id = p. SQL _idand sn. snap_id = st. snap_id and p. object_name = '& idxname' group by begin_interval_time, search_columns;