Deferred index maintenance

Note: this stuff will be edited in the near future

This post is about index maintenance and how amount and type of work during DML can depends on child steps in an execution plan.

Introduction
Oracle has deferred index maintenance mechanism used in some kind of DMLs. In this case Oracle during DML sorts and keeps index keys in workarea and when data changes in a table is finished it applies each change to an index as array of keys. It allows to decrease resource consumption such as amount of logical I/O, redo size, etc. From the other hand it is also the reason why you may encounter that a session executing the simplest statement like this

delete
  from t
 where col = :val

can wait “direct path write temp” / “direct path read temp” because the statement has active workareas which do not fit into the memory.
More details are below in section “How it works”.

Described deferred index maintenance is performed in:
1. Direct-path insert
2. Delete / Update / Merge if access path to the data is via b-tree Index Range Scan or Index Full Scan
3. Parallel DML, if DML part, not only query part of DML, is parallelized. In this case an execution plan contains step INDEX MAINTENANCE.
An example:

---------------------------------------------
| Id  | Operation                | Name     |
---------------------------------------------
|   0 | UPDATE STATEMENT         |          |
|   1 |  PX COORDINATOR          |          |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |
|   3 |    INDEX MAINTENANCE     | TBL      |
|   4 |     PX RECEIVE           |          |
|   5 |      PX SEND RANGE       | :TQ10000 |
|   6 |       UPDATE             | TBL      |
|   7 |        PX BLOCK ITERATOR |          |
|   8 |         TABLE ACCESS FULL| TBL      |
---------------------------------------------

In this blog entry I describe the mechanics of the index maintenance based on example from point (2):
“Delete / Update / Merge if access path to the data is via b-tree Index Range Scan or Index Full Scan”
because this case is the least known.

Note: If you are not interested in internal details you can jump to the section “How it works” below.

There was a topic on sql.ru where the author asked why Oracle uses Index Fast Full Scan instead of an Index Fast Full Scan on simple DML. Let’s check it.

Data preparation

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> create table t as select * from all_objects;

Table created

SQL> create index i on t(object_id);

Index created

SQL> exec dbms_stats.gather_table_stats(user, 'T', estimate_percent => 100, cascade => true);

PL/SQL procedure successfully completed

SQL> select object_name, object_id from user_objects where object_name in ('T', 'I');

OBJECT_NAME      OBJECT_ID
--------------- ----------
I                    81755
T                    81754

Let’s check what Oracle thinks about following statement

  delete
    from t
   where object_id <> 0;

 

SQL> explain plan for
  2    delete
  3      from t
  4     where object_id <> 0;

Explained

SQL> @plan

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | DELETE STATEMENT |      | 65919 |   321K|   149   (2)| 00:00:02 |
|   1 |  DELETE          | T    |       |       |            |          |
|*  2 |   INDEX FULL SCAN| I    | 65919 |   321K|   149   (2)| 00:00:02 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<>0)

So, Oracle has choosen Index Full Scan.

Let’s check what Oracle thinks about Index Fast Full Scan

SQL> explain plan for
  2    delete --+ index_ffs(t i)
  3      from t
  4     where object_id <> 0;

Explained

SQL> @plan;

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |      | 65919 |   321K|    35   (3)| 00:00:01 |
|   1 |  DELETE               | T    |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| I    | 65919 |   321K|    35   (3)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<>0)

The cost in IFFS case is 35 when the cost in IFS case is 149.
If we check 10053 we will see than Index Fast Full Scan is not considered at all.
Looks like CBO oddity.

Let’s execute both statements.

SQL> begin
  2
  3      dbms_monitor.session_trace_enable;
  4
  5      delete
  6        from t
  7       where object_id <> 0;
  8
  9      rollback;
 10
 11      delete --+ index_ffs(t i)
 12        from t
 13       where object_id <> 0;
 14
 15      rollback;
 16
 17  end;
 18  /

PL/SQL procedure successfully completed

 

DELETE FROM T
WHERE
 OBJECT_ID <> 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.15       3.16       2135        155      77120       65920
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.15       3.16       2135        155      77120       65920

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9511     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T (cr=155 pr=2135 pw=0 time=0 us)
  65920   INDEX FULL SCAN I (cr=147 pr=0 pw=0 time=0 us cost=149 size=329595 card=65919)(object id 81755)

....

DELETE --+ index_ffs(t i)
 FROM T WHERE OBJECT_ID <> 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      3.42       4.61       2697        165     210070       65920
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.42       4.61       2697        165     210070       65920

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9511     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T (cr=165 pr=2697 pw=0 time=0 us)
  65920   INDEX FAST FULL SCAN I (cr=153 pr=0 pw=0 time=0 us cost=35 size=329595 card=65919)(object id 81755)

Pay attention 77120 current gets in Index Full Scan case and 210070 current gets in Index Fast Full Scan case.
What is going on?

The answer we can found in redo log dump (similar info you can find in undo changes dump, event 10221).
But before let’s look on the dtracelio output. Note that current version of dtracelio contains mode_held for current gets (db block gets), also it monitors calls of function kcbget which also increment statistic “db block gets” and calculated in “cu=” figures in 10046 trace.

DtraceLIO
Below is an excerpt from Index Full Scan case. The most part of the output (~99%) looks like following (it is only small excerpt):

...
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec7 (5/69319) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF5C48,2,258,0) [tsn: 5 rdba: 0x1410e81 (5/69249) obj: 81754 dobj: 81754] where: 258 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec8 (5/69320) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec8 (5/69320) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec8 (5/69320) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec8 (5/69320) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ec8 (5/69320) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF5C48,2,258,0) [tsn: 5 rdba: 0x1410e81 (5/69249) obj: 81754 dobj: 81754] where: 258 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ee0 (5/69344) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ee0 (5/69344) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ee0 (5/69344) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ee0 (5/69344) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF5C48,2,258,0) [tsn: 5 rdba: 0x1410e81 (5/69249) obj: 81754 dobj: 81754] where: 258 mode_held: 2
kcbgcur(0xFFFFFFFF7CE5A4E8,2,952,0) [tsn: 5 rdba: 0x1410ee1 (5/69345) obj: 81754 dobj: 81754] where: 952 mode_held: 2
...

Object 81754 is the table.
We can see here
* blocks 5/69319, 5/69320, 5/69344, 5/69345 (table data blocks) are read by kcbgcur calls from where: 952 (“kddwh01: kdddel”, delete row)
* block 5/69249 (first level bitmap block, ASSM) is read by kcbgcur calls from where: 258 is “ktspfwh12: ktspstchg”

These calls delete rows from the table.

after that:

...
kcbgcur(0xFFFFFFFF7FFF5258,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF2C30,2,38,0) [tsn: 2 rdba: 0xc00100 (3/256) obj: 0 dobj: -1] where: 38 mode_held: 2
kcbgcur(0x461B36078,2,10,0) [tsn: 2 rdba: 0xc81bc9 (3/531401) obj: 0 dobj: -1] where: 10 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF5258,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF3498,2,258,0) [tsn: 5 rdba: 0x1402818 (5/10264) obj: 81755 dobj: 81755] where: 258 mode_held: 2
...

Object 81755 is the index.
* Block 5/10267 (index root block) is read four times by kcbgcur calls in SHARED mode (mode_held = 1) from where: 816 is “kdiwh17: kdifind” (Finds the appropriate index block to store the key ?).
* Blocks 5/10268 and 5/10269 (index leaf blocks) are read by kcbget in EXCLUSIVE mode (mode_held = 2) from where: 820 is “kdiwh22: kdifind”.
* Block 5/10264 (first level bitmap block, ASSM) is read from where: 258 is “ktspfwh12: ktspstchg”.

Note:
the index in the example has blevel=1, the only root block and leaf blocks. Below I show that all branches are also read before each leaf block.

Summary section:

=============================== Summary =========================
object_id    data_object_id  function   mode_held  where    count
81755        81755           kcbgtcr               1048     1
81755        81755           kcbgtcr               1047     1
0            -1              kcbgcur    2          168      2
0            -1              kcbgtcr               643      2
1            -1              kcbgcur    1          744      2
1            -1              kcbgcur    2          715      2
1            -1              kcbgcur    2          745      2
17           17              kcbgtcr               765      2
44           44              kcbgtcr               815      2
44           44              kcbgtcr               1047     2
0            -1              kcbgcur    1          48       4
0            -1              kcbgcur    1          39       6
81754        81754           kcbgcur    2          325      63
81755        81755           kcbgtcr               814      145
81755        81755           kcbgcur    2          258      146
81755        81755           kcbgcur    1          816      291
81755        81755           kcbget     2          820      291
0            -1              kcbgcur    2          10       2134
0            -1              kcbgcur    2          38       2139
81754        81754           kcbgcur    2          258      3975
81754        81754           kcbgcur    2          952      65922

We can see here
65922 calls of kcbgcur from where: 952 (“kddwh01: kdddel” delete row)
291 calls of kcbgcur from where: 816 in shared mode (index root block)
291 calls of kcbget from where: 820 in exclusive mode (leaf blocks)

Index Fast Full Scan:
The most part of output looks following:

...
kcbgcur(0xFFFFFFFF7FFF67F8,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF67F8,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7CE6BFB0,2,952,0) [tsn: 5 rdba: 0x140272b (5/10027) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF67F8,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF67F8,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7CE6BFB0,2,952,0) [tsn: 5 rdba: 0x140272b (5/10027) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF67F8,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF67F8,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7CE6BFB0,2,952,0) [tsn: 5 rdba: 0x140272b (5/10027) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF67F8,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF67F8,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7CE6BFB0,2,952,0) [tsn: 5 rdba: 0x140272b (5/10027) obj: 81754 dobj: 81754] where: 952 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF67F8,1,816,0) [tsn: 5 rdba: 0x140281b (5/10267) obj: 81755 dobj: 81755] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF67F8,2,820,0) [tsn: 5 rdba: 0x140281c (5/10268) obj: 81755 dobj: 81755] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7CE6BFB0,2,952,0) [tsn: 5 rdba: 0x140272b (5/10027) obj: 81754 dobj: 81754] where: 952 mode_held: 2
...

We can see here
block 5/10267 (index root block) is read by kcbgcur calls from where: 816 in shared mode.
block 5/10268 (index leaf) is read by kcbget calls from where: 820 in exclusive mode.
block 5/10027 (table data block) is read by kcbgcur calls from where: 952 (kddwh01: kdddel, delete row) in exclusive mode.

=============================== Summary =========================
object_id    data_object_id  function   mode_held  where    count
0            -1              kcbgcur    2          6        1
81755        81755           kcbgtcr               644      2
0            -1              kcbgcur    2          168      3
0            -1              kcbgtcr               643      3
1            -1              kcbgcur    1          744      3
1            -1              kcbgcur    2          715      3
1            -1              kcbgcur    2          745      3
17           17              kcbgtcr               765      3
44           44              kcbgtcr               815      3
44           44              kcbgtcr               1047     3
81755        81755           kcbgtcr               645      4
0            -1              kcbgcur    1          48       5
0            -1              kcbgcur    1          39       8
81754        81754           kcbgcur    2          325      63
81755        81755           kcbgcur    2          258      146
81755        81755           kcbgtcr               872      147
0            -1              kcbgcur    2          10       2696
0            -1              kcbgcur    2          38       2703
81754        81754           kcbgcur    2          258      3975
81755        81755           kcbgcur    1          816      65921
81755        81755           kcbget     2          820      65921
81754        81754           kcbgcur    2          952      65922

We can see here
65921 calls of kcbgcur from where: 816 (index root blocks were read by these calls)
65921 calls of kcbgcur from where: 820 (index leaf blocks were read by these calls)
65921 calls of kcbgcur from where: 952 (table data block were read by these calls)

Redo log dump
And below is an excerpt containing “index undo for leaf key operations” from redo log dump:

Index Full Scan

REDO RECORD - Thread:1 RBA: 0x00002c.0000c7af.0118 LEN: 0x0ff8 VLD: 0x01
SCN: 0x09d8.6f140f23 SUBSCN:  1 12/20/2011 18:13:04
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x09d8.6f140f21 SEQ:  1 OP:5.2 ENC:0
ktudh redo: slt: 0x001f sqn: 0x00000000 flg: 0x000a siz: 3380 fbi: 0
            uba: 0x00c82c4b.0392.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:36 AFN:3 DBA:0x00c82c4b OBJ:4294967295 SCN:0x09d8.6f140f22 SEQ:  1 OP:5.1 ENC:0
ktudb redo: siz: 3380 spc: 1238 flg: 0x000a seq: 0x0392 rec: 0x01
            xid:  0x000a.01f.00014438
ktubu redo: slt: 31 rci: 0 opc: 10.22 objn: 81755 objd: 81755 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00c82c4a
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f13d63f
Dump kdilk : itl=2, kdxlkflg=0x25 sdc=0 indexid=0x140281a block=0x0140281c
(kdxlre): restore leaf row (clear leaf delete flags)
number of keys: 255
key sizes:
 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
 10 10 10 10 10 10 10 10 10 10 10 10 10 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
 11 11 11 11 11 11 11 11 11 11 11 11 10 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
key :(2705):
 02 c1 03 06 01 40 27 2b 00 1a 02 c1 04 06 01 40 27 2b 00 04 02 c1 05 06 01
 40 27 2b 00 1f 02 c1 06 06 01 40 27 2b 00 16 02 c1 07 06 01 40 27 2b 00 03
 02 c1 08 06 01 40 27 2b 00 12 02 c1 09 06 01 40 27 2b 00 2e 02 c1 0a 06 01
 40 27 2b 00 20 02 c1 0b 06 01 40 27 2b 00 37 02 c1 0c 06 01 40 27 2b 00 02
...
selflock: (32):
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00
bitmap: (32):
 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff
 ff ff ff ff ff ff ff
CHANGE #3 TYP:0 CLS: 1 AFN:5 DBA:0x0140281c OBJ:81755 SCN:0x09d8.6f140727 SEQ:232 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.01f.00014438    uba: 0x00c82c4b.0392.01
REDO: ARRAY / -- / --
itl: 2, sno: 0, row size 3725
number of keys: 255
slots:
 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 12
 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254

Pay attention, CHANGE #2 (index undo) and CHANGE #3 (index redo) contain ARRAY of changes, 255 index keys in each change.

Index Fast Full Scan

REDO RECORD - Thread:1 RBA: 0x00002e.00000005.0020 LEN: 0x00e8 VLD: 0x01
SCN: 0x09d8.6f141196 SUBSCN:  1 12/20/2011 18:20:45
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c82d1f OBJ:4294967295 SCN:0x09d8.6f141195 SEQ:  1 OP:5.1 ENC:0
ktudb redo: siz: 100 spc: 3982 flg: 0x0022 seq: 0x0392 rec: 0x1d
            xid:  0x000a.019.00014416
ktubu redo: slt: 25 rci: 28 opc: 10.22 objn: 81755 objd: 81755 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f13d63f
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140281a block=0x0140281c
(kdxlre): restore leaf row (clear leaf delete flags)
key :(10):  02 c1 03 06 01 40 27 2b 00 1a
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0140281c OBJ:81755 SCN:0x09d8.6f140fbb SEQ:  2 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.019.00014416    uba: 0x00c82d1f.0392.1d
REDO: SINGLE / -- / --
itl: 2, sno: 0, row size 14

...

REDO RECORD - Thread:1 RBA: 0x00002e.00000006.007c LEN: 0x00d0 VLD: 0x01
SCN: 0x09d8.6f141196 SUBSCN:  3 12/20/2011 18:20:45
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c82d1f OBJ:4294967295 SCN:0x09d8.6f141196 SEQ:  2 OP:5.1 ENC:0
ktudb redo: siz: 84 spc: 3662 flg: 0x0022 seq: 0x0392 rec: 0x1f
            xid:  0x000a.019.00014416
ktubu redo: slt: 25 rci: 30 opc: 10.22 objn: 81755 objd: 81755 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c82d1f.0392.1d
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140281a block=0x0140281c
(kdxlre): restore leaf row (clear leaf delete flags)
key :(10):  02 c1 04 06 01 40 27 2b 00 04
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0140281c OBJ:81755 SCN:0x09d8.6f141196 SEQ:  1 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c82d1f.0392.1f
REDO: SINGLE / -- / --
itl: 2, sno: 1, row size 14

Here each change contains only one index key.

As we can see in the IFS case Oracle is able to delete index keys by a few calls as ARRAY.
And in the IFFS case Oracle deletes index keys as SINGLE records, one by one.

Conclusion
So, the conclusion – probably it is not CBO bug, but feature, heuristic ;-)

How it works:
Workarea with operation_type “IDX MAINTENANCE (SORT)” where index keys are sorted and stored is created for each index (btree, non-reverse) whose keys are changed by the DML. So, number of workareas will be equivalent number of appropriate indexes. After data changes is finished index keys from workareas are applied by ARRAYs to the existent indexes.
Thus, be careful, if Oracle does not have required PGA for these workareas these sortings will be spilled on the disk.

Described optimization, ARRAY redo/undo, is applied to ALL appropriate (b-tree, non-reverse) indexes which are changed during DML if access path to changed table is via Index Full/Range Scan.
Let’s look at the the first example:

SQL> explain plan for
  2    delete
  3      from t
  4     where object_id <> 0;

Explained

SQL> @plan

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | DELETE STATEMENT |      | 65919 |   321K|   149   (2)| 00:00:02 |
|   1 |  DELETE          | T    |       |       |            |          |
|*  2 |   INDEX FULL SCAN| IDX  | 65919 |   321K|   149   (2)| 00:00:02 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<>0)

Let’s suggest that index IDX is a reverse index. In this case ARRAY optimization is not applied for this exact index.
All redo/undo changes will be SINGLE, row by row. As in Full Table Scan or Index Fast Full Scan cases.

But what if there are N more normal b-tree indexes on the table T?
In this case ARRAY optimization will be applied for all these indexes except reverse index IDX, although the execution plan contains Index Full Scan on reverse index IDX.

Additional points
I would like to emphasize some points which are described in this post

1. I’ve learnt that Jonathan Lewis wrote about this phenomenon in 2006(!)
http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/

2. Note, how index blocks are read during DML.
Before leaf block is read for change (to change a key from kdifind, in fact you can see many another block gets especially during update statement) index root block and all index branch blocks above that leaf are also read.
Without ARRAY optimization (Full Table Scan, Index Fast Full Scan, reverse index, IOT, etc) it is caused for each row change.
And with ARRAY optimization it is caused (leaf block read) for each array batch operation.
This is especially important for indexes with high blevel.

The index in the example above has blevel=1, so the only root block and leaf blocks.
Let’s look at an excerpt of dtracelio output of the similar index changes in index with blevel=4.
Here we can see two array changes for two leaf blocks.

...
kcbgcur(0xFFFFFFFF7FFF5258,1,816,0) [tsn: 31 rdba: 0x1e8a32c4 (122/668356) obj: 82756 dobj: 82756] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,1,820,0) [tsn: 31 rdba: 0x1e8a328c (122/668300) obj: 82756 dobj: 82756] where: 820 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,1,820,0) [tsn: 31 rdba: 0x1d894f8e (118/610190) obj: 82756 dobj: 82756] where: 820 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,1,820,0) [tsn: 31 rdba: 0x1f09a0ec (124/631020) obj: 82756 dobj: 82756] where: 820 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,2,820,0) [tsn: 31 rdba: 0x1e8a328f (122/668303) obj: 82756 dobj: 82756] where: 820 mode_held: 2
kcbgcur(0xFFFFFFFF7FFF5258,1,816,0) [tsn: 31 rdba: 0x1e8a32c4 (122/668356) obj: 82756 dobj: 82756] where: 816 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,1,820,0) [tsn: 31 rdba: 0x1e8a328c (122/668300) obj: 82756 dobj: 82756] where: 820 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,1,820,0) [tsn: 31 rdba: 0x1d894f8e (118/610190) obj: 82756 dobj: 82756] where: 820 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,1,820,0) [tsn: 31 rdba: 0x1f09a0ec (124/631020) obj: 82756 dobj: 82756] where: 820 mode_held: 1
kcbget(0xFFFFFFFF7FFF5258,2,820,0) [tsn: 31 rdba: 0x1f09a0e0 (124/631008) obj: 82756 dobj: 82756] where: 820 mode_held: 2
...

The first 5 gets in excerpt:
Block (122/668356) – index root block – was read by kcbgcur from “where: 816″ in shared mode
Blocks (122/668300), (118/610190), (124/631020) – index branches – were read by kcbget from “where: 820″ in shared mode
Block (122/668303) – index leaf block – was read by kcbgcur from “where: 820″ in exclusive mode
And after that, the second 5 gets, the same root block and the same branches, but another leaf block (124/631008).

In the case without array optimization these gets (root, branches, leaf) will be read for each index key change.

3. The deferred index maintenance is not performed on IOTs itself, but performed on additional indexes on IOT.

4. It does not work for whole DML when access path is Index Range/Full Scan Descending (as in an example below),

SQL> explain plan for
  2      delete --+ index_desc(t)
  3        from t
  4       where object_id <> 0;
 
Explained
 
SQL> @planb
--------------------------------------------
| Id  | Operation                   | Name |
--------------------------------------------
|   0 | DELETE STATEMENT            |      |
|   1 |  DELETE                     | T    |
|   2 |   INDEX FULL SCAN DESCENDING| IDX  |
--------------------------------------------

but works with Index Range/Full Scan [ASC] on an index created with DESC sorting order.

5. It also does not work in the following case:

SQL> explain plan for
  2      delete --+ all_rows
  3        from t
  4       where object_id <> 0
  5         and rownum < 999999999999;   
Explained   

SQL> @plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |      | 65919 |   321K|   149   (2)| 00:00:02 |
|   1 |  DELETE           | T    |       |       |            |          |
|*  2 |   COUNT STOPKEY   |      |       |       |            |          |
|*  3 |    INDEX FULL SCAN| I    | 65919 |   321K|   149   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM   3 - filter("OBJECT_ID"<>0)

6. From the other hand deferred index maintenance works in more complicated execution plans as following cases:

delete
  from t1
 where exists (select 1 from t2 where t2.id = t1.id)

 

---------------------------------------
| Id  | Operation              | Name |
---------------------------------------
|   0 | DELETE STATEMENT       |      |
|   1 |  DELETE                | T1   |
|   2 |   HASH JOIN SEMI       |      |
|   3 |    INDEX FULL SCAN     | PK1  |
|   4 |    INDEX FAST FULL SCAN| PK2  |
---------------------------------------

 

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | DELETE STATEMENT    |      |
|   1 |  DELETE             | T1   |
|   2 |   NESTED LOOPS SEMI |      |
|   3 |    INDEX FULL SCAN  | PK1  |
|   4 |    INDEX UNIQUE SCAN| PK2  |
------------------------------------

7. If there are triggers fired in the statement then deferred index maintenance is rejected. Usual index maintenance is used instead.

  1. Tim Hopkins
    March 8, 2013 at 6:43 pm

    Hi Alexander,

    Awesome article! I love this stuff and it’s been useful to know a few times already. It came up again today at a client so I thought it’s about time I let you know how useful it is – keep up the good work!

    Congrats on dtracelio too – I attempted something similar myself in about 2005 but never developed it to the same extent and don’t need to now thanks to your efforts.

    Cheers,
    Tim

  2. Fritz Holden
    May 22, 2014 at 10:22 am

    Thanks for this excellent article !

  3. howard
    September 11, 2014 at 1:45 am

    Good work , excellent article .

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: