Monitoring Index Usage

A common requirement cropped up this week. A new Data Warehouse has just gone live and is still in the 'do we have the right indexes here' phase. In this case, the suspicion is that there are too many indexes in a specific schema and that a number of them may just be a waste of space and a performance overhead. The developers would like to know which indexes are not being used.

Oracle's index monitoring facility can be used for this, as described in Tim Hall's article. There's plenty of information that you can read through by googling Oracle Index Monitoring and that was just one of the first examples I found.

However, there are a few problems with this approach.

1) As mentioned in Tim's article, you need to have the facility enabled for long enough to be sure that indexes aren't used. Even if you leave it enabled for a month or so, who is to say that a particular index isn't vital to the performance of a year-end routine? Perhaps unlikely, but it would be a shame to find out after you've dropped the index.

2) Index monitoring simply isn't granular enough. All it will tell me is that an index has been used but I have no idea of how often it's been used. I suppose you could get round this by switching index monitoring on and off frequently and seeing which indexes only appear to be used occasionally.

3) Related to the low granularity, an index may not be used by your application at all but a DBA or developer happens to run a one-off query, which happens to use the index. I know that an index was used, but how do I know whether that use was significant or just an anomaly? The information 'YES' doesn't contain much value.

There are other approaches. Maybe we could look at V$SQL_PLAN and V$SQL_PLAN_STATISTICS or use a login trigger to capture Stored Outlines?

One approach that sprung to mind for me was something that I heard Jonathan Lewis mention at the UKOUG Northern Server Tech day. One of the things I like about Jonathan's presentations is that there are always useful nuggets of information scattered through the main narrative.

He mentioned an event that could be set to capture all the execution plans into a trace file - event 10132 - dump plan after compilation. I searched for information on this event on Metalink and the wider web and there isn't too much kicking around, so I thought I'd mention it here. Eventually we're going to set the event at the instance level, so that we can capture all of the plans and look for indexes that don't appear in the trace file. Before we do that, I have an SR open with Oracle Support to make sure that they're happy with us setting this event. For the example here, I'll just set it at the session level and executed a single new statement.

livdwhtst01:[oracle]:sqlplus testuser
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:12:42 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> create table test as select * from dba_objects;

Table created.

SQL> create unique index test_ix on test(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST');

PL/SQL procedure successfully completed.

SQL> alter session set events '10132 trace name context forever, level 1';

Session altered.

SQL> select count(*) from test;

  COUNT(*)
----------
    106242

SQL> show parameter user_dump_dest

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
user_dump_dest                       string
/ora/admin/MIDDWHT3/udump

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

livdwhtst01:[oracle]:pwd
/ora/admin/MIDDWHT3/udump

livdwhtst01:[oracle]:ls -ltra | tail
-rw-r-----   1 oracle   dba          644 Jul 21 07:25 middwht3_ora_8618.trc
-rw-rw-rw-   1 oracle   dba         1561 Jul 21 08:26 middwht3_ora_16958.trc
-rw-r-----   1 oracle   dba        32723 Jul 21 08:52 middwht3_ora_26349.trc
-rw-r-----   1 oracle   dba          646 Jul 21 11:37 middwht3_ora_17201.trc
-rw-r-----   1 oracle   dba       109697 Jul 21 11:59 middwht3_ora_21441.trc
-rw-r-----   1 oracle   dba        59396 Jul 21 12:06 middwht3_ora_22680.trc
-rw-r-----   1 oracle   dba        42243 Jul 21 12:16 middwht3_ora_23474.trc
-rw-r-----   1 oracle   dba          646 Jul 21 12:18 middwht3_ora_24621.trc
drwxrwxr-x   2 oracle   dba       224256 Jul 21 13:14 .
-rw-r-----   1 oracle   dba        21537 Jul 21 13:15 middwht3_ora_9429.trc

livdwhtst01:[oracle]:cat middwht3_ora_9429.trc

/ora/admin/MIDDWHT3/udump/middwht3_ora_9429.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options
ORACLE_HOME = /ora/product/10.2.0
System name:    SunOS
Node name:      livdwhtst01
Release:        5.9
Version:        Generic_118558-17
Machine:        sun4us
Instance name: MIDDWHT3
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 9429, image: oracle@livdwhtst01 (TNS V1-V3)

*** 2006-07-21 13:14:59.446
*** ACTION NAME:() 2006-07-21 13:14:59.445
*** MODULE NAME:(SQL*Plus) 2006-07-21 13:14:59.445
*** SERVICE NAME:(SYS$USERS) 2006-07-21 13:14:59.445
*** SESSION ID:(89.10029) 2006-07-21 13:14:59.445

sql_id=7b2twsn8vgfsc.
Current SQL statement for this session:
select count(*) from test

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   328 |           |
| 1   |  SORT AGGREGATE     |         |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | TEST    |  104K |       |   328 |  00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------

Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : TESTUSER
  plan_hash      : 3467505462
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST"@"SEL$1")
    END_OUTLINE_DATA
  */

Optimizer environment:
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0

[Lots of other parameter values snipped out here ]

  _force_rewrite_enable               = false
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _gby_hash_aggregation_enabled       = true
Query Block Registry:
*********************
SEL$1 0x7b01b6a8 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=19392, alloc=49080), compile(in-use=59056, alloc=107040)

sql_id=gz3s8nmsh6s8c.
Current SQL statement for this session:
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",

:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",        

:"SYS_B_10",:"SYS_B_11", :"SYS_B_12") TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM 

FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:"SYS_B_13") ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM

============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |         |       |       |     2 |           |
| 1   |  SORT ORDER BY       |         |     1 |   371 |     2 |  00:00:01 |
| 2   |   COUNT              |         |       |       |       |           |
| 3   |    HASH JOIN         |         |     1 |   371 |     1 |  00:00:01 |
| 4   |     FIXED TABLE FULL | X$KSPPI |     1 |    81 |     0 |           |
| 5   |     FIXED TABLE FULL | X$KSPPCV|   100 |   28K |     0 |           |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("X"."INDX"="Y"."INDX")
3 - filter((TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0))
4 - filter(("X"."INST_ID"=USERENV('INSTANCE') AND UPPER("KSPPINM") LIKE UPPER(:SYS_B_13) AND 
           TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%'))

Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : TESTUSER
  plan_hash      : 245223518
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$88122447")
      MERGE(@"SEL$641071AC")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$641071AC")
      MERGE(@"SEL$07BDC5B4")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$07BDC5B4")
      MERGE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      FULL(@"SEL$88122447" "X"@"SEL$4")
      FULL(@"SEL$88122447" "Y"@"SEL$4")
      LEADING(@"SEL$88122447" "X"@"SEL$4" "Y"@"SEL$4")
      USE_HASH(@"SEL$88122447" "Y"@"SEL$4")
    END_OUTLINE_DATA
  */