## Who's online

There are currently 0 users and 27 guests online.

# Removing Unnecessary Indexes: 3.Cost of Maintaining Indexes

This is the third post in a series about unnecessary indexes and some of the challenges that they present.
In this post I want to look at how to quantify the overhead of index maintenance, and estimate the benefit of removing redundant indexes.
Let’s conduct an experiment. I will set up the same table and indexes as in the previous posts in this series, but this time I will put them into the RECYCLE buffer pool, to which I have allocated the minimum possible 4M of memory, and I will allocate 25% free space to the indexes. The idea is not to benefit from the buffer cache, but suffer the pain of the physical I/O.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`CREATE TABLE t (a NUMBER, b NUMBER, c NUMBER, d NUMBER,CONSTRAINT t_pk PRIMARY KEY (a) USING INDEX STORAGE (BUFFER_POOL RECYCLE)) STORAGE(BUFFER_POOL RECYCLE);CREATE INDEX t_ab  ON t (a,b)   STORAGE(BUFFER_POOL RECYCLE) PCTFREE 25;CREATE INDEX t_bc  ON t (b,c)   STORAGE(BUFFER_POOL RECYCLE) PCTFREE 25;CREATE INDEX t_bcd ON t (b,c,d) STORAGE(BUFFER_POOL RECYCLE) PCTFREE 25;INSERT /*+APPEND*/ INTO tWITH x AS (SELECT  rownum-1 n FROM DUAL connect by level <= 1E5)SELECT  n, MOD(n,100), ROUND(MOD(n,100),-1), dbms_random.value(1,100)FROM   x/CREATE INDEX T_BCD_ROUND ON T (B, C, ROUND(D,0)) STORAGE(BUFFER_POOL RECYCLE) PCTFREE 25;CREATE INDEX T_BCD_DESC  ON T (B, C, D DESC)     STORAGE(BUFFER_POOL RECYCLE) PCTFREE 25;EXEC dbms_stats.gather_table_stats(null,'T',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 1 (B,C)');``

The table and indexes really too large to fit in the recycle buffer pool, so there will be physical read as blocks are loaded into the buffer cache, and physical write as dirty blocks are pushed out.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`SELECT s.owner, s.segment_name,      s.blocks, s.bytes/1024/1024 Mb,      t.blocksFROM   dba_segments s,      dba_tables tWHERE  t.table_name = 'T'AND    s.segment_type = 'TABLE'AND    s.segment_name = t.table_name/OWNER      SEGMENT_NAME     BLOCKS         MB     BLOCKS---------- ------------ ---------- ---------- ----------SYSADM     T                   640          5        543SELECT  s.owner, s.segment_name,       s.blocks, s.bytes/1024/1024 Mb,       i.leaf_blocksFROM    dba_segments s,       dba_indexes iWHERE   s.owner = i.ownerAND     s.segment_name = i.index_nameAND     i.table_name = 'T'/OWNER      SEGMENT_NAME     BLOCKS         MB LEAF_BLOCKS---------- ------------ ---------- ---------- -----------SYSADM     T_PK                256          2         187SYSADM     T_AB                256          2         237SYSADM     T_BC                256          2         213SYSADM     T_BCD               512          4         488SYSADM     T_BCD_ROUND         384          3         336SYSADM     T_BCD_DESC          768          6         675``

Now I will run a PL/SQL loop that randomly updates rows in the table with random values, 500,000 times. The random nature of the update will minimize the benefit of the caching.  The database will continuously have to load blocks from disk back into buffer cache.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`DECLARE   l_counter INTEGER := 0;  l_t1 DATE;  l_r1 INTEGER;  l_r2 INTEGER;  l_module VARCHAR2(64);  l_action VARCHAR2(64);BEGIN  dbms_application_info.read_module(l_module, l_action);  dbms_application_info.set_module('REDUNDANT','UPDATE TEST');  l_t1 := SYSDATE + &&runtime/86400;  WHILE l_t1 >= SYSDATE AND l_counter < 5e5 LOOP    l_r1 := round(dbms_random.value(1,100),0);    l_r2 := round(dbms_random.value(1,100),0);    UPDATE t    SET    c = l_r2    ,      d = ROUND(l_r2,-1)    WHERE  a = l_r1;    l_counter := l_counter + 1;    COMMIT;  END LOOP;   dbms_output.put_line(l_counter||' rows updated');  dbms_application_info.set_module(l_module, l_action);END;/``

The activity in the buffer cache is visible via v\$buffer_pool_statistics.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`select * from v\$buffer_pool_statistics where name = 'RECYCLE';``

Before:

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`        ID NAME                 BLOCK_SIZE  SET_MSIZE  CNUM_REPL CNUM_WRITE   CNUM_SET    BUF_GOT  SUM_WRITE   SUM_SCAN---------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------FREE_BUFFER_WAIT WRITE_COMPLETE_WAIT BUFFER_BUSY_WAIT FREE_BUFFER_INSPECTED DIRTY_BUFFERS_INSPECTED DB_BLOCK_CHANGE---------------- ------------------- ---------------- --------------------- ----------------------- ---------------DB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS PHYSICAL_WRITES------------- --------------- -------------- ---------------         2 RECYCLE                    8192        490        490          0        490     482786     758695          0           65466               22517                0                889060                  612270        49971577     71349961        18260691         385878          758695``

After:

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`         2 RECYCLE                    8192        490        459         31        490     518646     853441          0           73457               25942                0                981089                  688777        57003938     81763622        19260981         413463          853441``

We can see there have been physical reads and writes on objects in the recycle pool.
This is the execution plan of the statement in the PL/SQL loop. The update occurs in line 1 of the plan.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`SQL_ID  3f305xbd6ts1d, child number 1-------------------------------------UPDATE T SET C = :B2 , D = ROUND(:B2 ,-1) WHERE A = :B1Plan hash value: 795017363---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | UPDATE STATEMENT   |      |       |       |     2 (100)|          ||   1 |  UPDATE            | T    |       |       |            |          ||*  2 |   INDEX UNIQUE SCAN| T_PK |     1 |    33 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------``

Now, I will profile the recent ASH data for my test (filtered by module and action) by object and event, but we will only look at line 1 of execution plans where the SQL_OPNAME indicates a DML statement. Thus we are looking exclusively at the overhead of modifying data, and not the overhead of finding it in the first place.
The current_obj# and current_file# is used to identify database segments and tablespace name. However, these columns are reliable only for wait events that relate to physical I/O. They are not, for example, valid for CPU events.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`column sql_id format a13column sql_plan_hash_value heading 'SQL Plan|Hash Value'column sql_plan_line_id heading 'SQL Plan|Line ID'column top_level_sql_id format a13 heading 'Top Level|SQL ID'column event format a25column object_type format a6 heading 'Object|Type'column object_name format a11column p1text format a9column p2text format a6column p3text format a7column action format a11column ash_secs heading 'ASH|secs' format 9999column current_file# heading 'File|#' format 9999column current_obj# heading 'Curr|Obj#' format 999999compute sum of ash_secs on reportbreak on reportWITH x AS (SELECT event, action, current_obj#, current_file#,      p1text, p2text, p3text,      SUM(1) ash_secsFROM   v\$active_session_historyWHERE  module = 'REDUNDANT'AND    sample_time >= SYSDATE - (10+&&runtime)/86400AND    sql_plan_line_id = 1 /*line 1 is where the update occurs*/AND    sql_opname IN('INSERT','UPDATE','DELETE','UPSERT') /*DML Statement*/GROUP BY event, action, current_obj#, current_file#, p1text, p2text, p3text)SELECT x.*,      o.object_type, o.object_name,      f.tablespace_nameFROM   xLEFT OUTER JOIN dba_objects o  ON  o.object_id = x.current_obj#  AND event is not nullLEFT OUTER JOIN dba_data_files f  ON  f.file_id = x.current_file#  AND event is not nullAND x.p1text = 'file#'AND x.p2text = 'block#'ORDER BY ash_secs DESC /``

The test ran for 314 seconds, though we only spent 210 seconds updating the table. However, we can see how much time was spent time of writing to various indexes. Note that T_BC is still maintained even though it is invisible.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`                                         Curr  File                            ASH ObjectEVENT                     ACTION         Obj#     # P1TEXT    P2TEXT P3TEXT   secs Type   OBJECT_NAME TABLESPACE_NAME------------------------- ----------- ------- ----- --------- ------ ------- ----- ------ ----------- -----------------------free buffer waits         UPDATE TEST      -1     0 file#     block# set-id#    44                          UPDATE TEST      -1     0 file#     block# blocks     28write complete waits      UPDATE TEST   99411     4 file#     block#            19 INDEX  T_BC        PSDEFAULTfree buffer waits         UPDATE TEST   99412     0 file#     block# set-id#    14 INDEX  T_BCDwrite complete waits      UPDATE TEST   99414     4 file#     block#            14 INDEX  T_BCD_DESC  PSDEFAULTfree buffer waits         UPDATE TEST   99411     0 file#     block# set-id#    12 INDEX  T_BCwrite complete waits      UPDATE TEST   99412     4 file#     block#            12 INDEX  T_BCD       PSDEFAULTwrite complete waits      UPDATE TEST   99413     4 file#     block#            11 INDEX  T_BCD_ROUND PSDEFAULT                          UPDATE TEST   99411     0 file#     block# blocks     11                          UPDATE TEST   99413     0 file#     block# blocks      8free buffer waits         UPDATE TEST   99413     0 file#     block# set-id#     7 INDEX  T_BCD_ROUNDfree buffer waits         UPDATE TEST   99414     0 file#     block# set-id#     7 INDEX  T_BCD_DESC                          UPDATE TEST   99414     0 file#     block# blocks      5                          UPDATE TEST   99412     0 file#     block# blocks      5free buffer waits         UPDATE TEST   99412     4 file#     block# set-id#     3 INDEX  T_BCD       PSDEFAULTlog file switch (private  UPDATE TEST   99411     4                              1 INDEX  T_BCstrand flush incomplete)free buffer waits         UPDATE TEST   99411     4 file#     block# set-id#     1 INDEX  T_BC        PSDEFAULT                          UPDATE TEST   99413     4 file#     block# blocks      1                          UPDATE TEST      -1     0 file#     block#             1                          UPDATE TEST   99411     4 file#     block# blocks      1                          UPDATE TEST   99411     0 file#     block#             1log file switch completio UPDATE TEST   99414     0                              1 INDEX  T_BCD_DESCfree buffer waits         UPDATE TEST      -1     4 file#     block# set-id#     1                    PSDEFAULT                          UPDATE TEST      -1     4 file#     block# blocks      1                          UPDATE TEST   99414     0 file#     block#             1                                                                             -----sum                                                                            210``

Now I know where time was spent maintaining which indexes, and I can decide whether it is worth dropping a particular index.  In all, 33 seconds were spent maintaining index T_BC.
When I repeated the test, having dropped index T_BC, the runtime of the test goes down by 21 seconds to 293 seconds. The time spent maintaining other indexes also goes down. Time spent on index T_BCD went from 29 seconds to just 9 seconds. I speculate that this is due to better cache efficiency because it no longer has to cache index T_BC.
More time was spent on other wait events that are not directly associated with the index, so other forms of contention were introduced.  However, the performance of the test as a whole improved, so we are in a better place.

`#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">`                                         Curr  File                            ASH ObjectEVENT                     ACTION         Obj#     # P1TEXT    P2TEXT P3TEXT   secs Type   OBJECT_NAME TABLESPACE_NAME------------------------- ----------- ------- ----- --------- ------ ------- ----- ------ ----------- ---------------free buffer waits         UPDATE TEST      -1     0 file#     block# set-id#    41                          UPDATE TEST      -1     0 file#     block# blocks     39                          UPDATE TEST   99419     0 file#     block# blocks     12                          UPDATE TEST   99418     0 file#     block# blocks     11free buffer waits         UPDATE TEST   99411     0 file#     block# set-id#    10write complete waits      UPDATE TEST   99413     4 file#     block#            10                    PSDEFAULT                          UPDATE TEST   99411     0 file#     block# blocks      9free buffer waits         UPDATE TEST   99412     0 file#     block# set-id#     9write complete waits      UPDATE TEST   99414     4 file#     block#             8                    PSDEFAULTwrite complete waits      UPDATE TEST   99411     4 file#     block#             8                    PSDEFAULTwrite complete waits      UPDATE TEST   99418     4 file#     block#             6 INDEX  T_BCD       PSDEFAULTwrite complete waits      UPDATE TEST   99412     4 file#     block#             5                    PSDEFAULT                          UPDATE TEST   99414     0 file#     block# blocks      4free buffer waits         UPDATE TEST   99414     0 file#     block# set-id#     4free buffer waits         UPDATE TEST   99413     0 file#     block# set-id#     3write complete waits      UPDATE TEST   99419     4 file#     block#             3 INDEX  T_BCD_ROUND PSDEFAULT                          UPDATE TEST   99412     0 file#     block# blocks      3                          UPDATE TEST   99413     0 file#     block# blocks      3                          UPDATE TEST   99420     0 file#     block# blocks      2free buffer waits         UPDATE TEST   99418     0 file#     block# set-id#     2 INDEX  T_BCDwrite complete waits      UPDATE TEST   99420     4 file#     block#             1 INDEX  T_BCD_DESC  PSDEFAULT                          UPDATE TEST   99411     4 file#     block# blocks      1write complete waits      UPDATE TEST   99415     4 file#     block#             1 TABLE  T           PSDEFAULT                          UPDATE TEST   99418     0 file#     block#             1                          UPDATE TEST   99414     0 file#     block#             1log file switch (private  UPDATE TEST   99418     0                              1 INDEX  T_BCDstrand flush incomplete)                          UPDATE TEST   99411     0 file#     block#             1                          UPDATE TEST   99418     0                              1free buffer waits         UPDATE TEST   99412     4 file#     block# set-id#     1                    PSDEFAULTfree buffer waits         UPDATE TEST      -1     4 file#     block# set-id#     1                    PSDEFAULT                          UPDATE TEST      -1     0 file#     block#             1free buffer waits         UPDATE TEST   99420     0 file#     block# set-id#     1 INDEX  T_BCD_DESC                                                                             -----sum                                                                            204``