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 t
WITH 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.blocks
FROM dba_segments s
, dba_tables t
WHERE 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 543

SELECT s.owner, s.segment_name
, s.blocks, s.bytes/1024/1024 Mb
, i.leaf_blocks
FROM dba_segments s
, dba_indexes i
WHERE s.owner = i.owner
AND s.segment_name = i.index_name
AND i.table_name = 'T'
/

OWNER SEGMENT_NAME BLOCKS MB LEAF_BLOCKS
---------- ------------ ---------- ---------- -----------
SYSADM T_PK 256 2 187
SYSADM T_AB 256 2 237
SYSADM T_BC 256 2 213
SYSADM T_BCD 512 4 488
SYSADM T_BCD_ROUND 384 3 336
SYSADM 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 = :B1

Plan 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 |
---------------------------------------------------------------------------

Profile DML Overhead

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 a13
column 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 a25
column object_type format a6 heading 'Object|Type'
column object_name format a11
column p1text format a9
column p2text format a6
column p3text format a7
column action format a11
column ash_secs heading 'ASH|secs' format 9999
column current_file# heading 'File|#' format 9999
column current_obj# heading 'Curr|Obj#' format 999999
compute sum of ash_secs on report
break on report
WITH x AS (
SELECT event, action, current_obj#, current_file#
, p1text, p2text, p3text
, SUM(1) ash_secs
FROM v$active_session_history
WHERE module = 'REDUNDANT'
AND sample_time >= SYSDATE - (10+&&runtime)/86400
AND 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_name
FROM x
LEFT OUTER JOIN dba_objects o
ON o.object_id = x.current_obj#
AND event is not null
LEFT OUTER JOIN dba_data_files f
ON f.file_id = x.current_file#
AND event is not null
AND 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 Object
EVENT 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 28
write complete waits UPDATE TEST 99411 4 file# block# 19 INDEX T_BC PSDEFAULT
free buffer waits UPDATE TEST 99412 0 file# block# set-id# 14 INDEX T_BCD
write complete waits UPDATE TEST 99414 4 file# block# 14 INDEX T_BCD_DESC PSDEFAULT
free buffer waits UPDATE TEST 99411 0 file# block# set-id# 12 INDEX T_BC
write complete waits UPDATE TEST 99412 4 file# block# 12 INDEX T_BCD PSDEFAULT
write 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 8
free buffer waits UPDATE TEST 99413 0 file# block# set-id# 7 INDEX T_BCD_ROUND
free 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 5
free buffer waits UPDATE TEST 99412 4 file# block# set-id# 3 INDEX T_BCD PSDEFAULT
log file switch (private UPDATE TEST 99411 4 1 INDEX T_BC
strand 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# 1
log file switch completio UPDATE TEST 99414 0 1 INDEX T_BCD_DESC
free 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 Object
EVENT 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 11
free buffer waits UPDATE TEST 99411 0 file# block# set-id# 10
write complete waits UPDATE TEST 99413 4 file# block# 10 PSDEFAULT
UPDATE TEST 99411 0 file# block# blocks 9
free buffer waits UPDATE TEST 99412 0 file# block# set-id# 9
write complete waits UPDATE TEST 99414 4 file# block# 8 PSDEFAULT
write complete waits UPDATE TEST 99411 4 file# block# 8 PSDEFAULT
write complete waits UPDATE TEST 99418 4 file# block# 6 INDEX T_BCD PSDEFAULT
write complete waits UPDATE TEST 99412 4 file# block# 5 PSDEFAULT
UPDATE TEST 99414 0 file# block# blocks 4
free buffer waits UPDATE TEST 99414 0 file# block# set-id# 4
free buffer waits UPDATE TEST 99413 0 file# block# set-id# 3
write 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 2
free buffer waits UPDATE TEST 99418 0 file# block# set-id# 2 INDEX T_BCD
write complete waits UPDATE TEST 99420 4 file# block# 1 INDEX T_BCD_DESC PSDEFAULT
UPDATE TEST 99411 4 file# block# blocks 1
write 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# 1
log file switch (private UPDATE TEST 99418 0 1 INDEX T_BCD
strand flush incomplete)
UPDATE TEST 99411 0 file# block# 1
UPDATE TEST 99418 0 1
free buffer waits UPDATE TEST 99412 4 file# block# set-id# 1 PSDEFAULT
free buffer waits UPDATE TEST -1 4 file# block# set-id# 1 PSDEFAULT
UPDATE TEST -1 0 file# block# 1
free buffer waits UPDATE TEST 99420 0 file# block# set-id# 1 INDEX T_BCD_DESC
-----
sum 204