12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?)

I previously looked at how global index maintenance was performed when dropping a table partition prior to Oracle Database 12c. Let’s see how things have now changed since the introduction of 12c.

Let’s start by creating the same partitioned table and global indexes as previously:

SQL> create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue));

Table created.

SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse_id_i on muse(id);

Index created.

SQL> create index muse_code_i on muse(code) global partition by range(code)(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

If we look at the current state of affairs, all is currently hunky dory:

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'
union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME    NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
------------ --------------- ---------- ---------- ----------- -------- ---
MUSE_CODE_I  CODE_P1            1500000       4224        4137 USABLE   NO
MUSE_CODE_I  CODE_P2            1500000       4352        4177 USABLE   NO
MUSE_ID_I                       3000000       9216        8633 VALID    NO

However, a difference to note here is a new data dictionary column called ORPHANED_ENTRIES which denotes whether the index currently has any orphaned index entries. What are these ? We shall see …

Let’s see how expensive it is to now drop the same table partition while updating the global indexes:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=7;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        129249
redo size                                                         105069544

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

Elapsed: 00:00:00.76

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=7;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        129314
redo size                                                         105083724

As we can see, this is significantly different than before when this was a relatively slow and expensive exercise. At just 65 block gets and only 14180 bytes of redo, this is now about the same cost as dropping the partition without updating the global indexes. How can this be ?

If we now look at the status of our global indexes:

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'
union
select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME    NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
------------ --------------- ---------- ---------- ----------- -------- ---
MUSE_CODE_I  CODE_P1            1000000       4224        4137 USABLE   YES
MUSE_CODE_I  CODE_P2            1000000       4352        4177 USABLE   YES
MUSE_ID_I                       2000000       9216        5849 VALID    YES

We notice that indeed, the index entries have been reduced (for example, only 2M index entries now in MUSE_ID_I instead of 3M) as if the indexes have been updated. However, we also notice that although the indexes are both marked as now having orphaned entries, they’re still in a USABLE state.

Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly.

However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries.

So if we now select values via the ID column index that only spans data in the dropped table partition:

SQL> select * from muse where id between 42 and 420;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2515419874
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |           |     1 |    23 |     4   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE      |     1 |    23 |     4   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | MUSE_ID_I |     1 |       |     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=42 AND "ID"<=420)

filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

Statistics
----------------------------------------------------------

0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
470  bytes sent via SQL*Net to client
532  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

We notice that quite correctly, no rows are now returned.

The partitioned global index on the CODE column likewise only returns valid data when accessed:

SQL> select * from muse where code=42;

ID       CODE NAME
---------- ---------- ------------------------------
1000042         42 DAVID BOWIE
1100042         42 DAVID BOWIE
1200042         42 DAVID BOWIE
1300042         42 DAVID BOWIE
1400042         42 DAVID BOWIE
1500042         42 DAVID BOWIE
1700042         42 DAVID BOWIE
1600042         42 DAVID BOWIE
1900042         42 DAVID BOWIE
1800042         42 DAVID BOWIE
2000042         42 DAVID BOWIE
2100042         42 DAVID BOWIE
2200042         42 DAVID BOWIE
2300042         42 DAVID BOWIE
2400042         42 DAVID BOWIE
2500042         42 DAVID BOWIE
2600042         42 DAVID BOWIE
2700042         42 DAVID BOWIE
2900042         42 DAVID BOWIE
2800042         42 DAVID BOWIE

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4070098220
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |             |    20 |   460 |    24   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                     |             |    20 |   460 |    24   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE        |    20 |   460 |    24   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                         | MUSE_CODE_I |    20 |       |     3   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CODE"=42)

filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

Statistics

----------------------------------------------------------
1  recursive calls
0  db block gets
25  consistent gets
2  physical reads
0  redo size
1147  bytes sent via SQL*Net to client
554  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20  rows processed

As we can see, only valid data belonging to the non-dropped partitions are returned via the index, even though the index has orphaned index entries that reference the dropped table partition.

If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME            LF_ROWS DEL_LF_ROWS
------------ ---------- -----------
MUSE_ID_I       3000000     1000000

We see that the index statistics is indicating that there are 1M so-called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.

So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do. So what’s the catch ?

Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:

SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:03:56.52

Execution Plan
----------------------------------------------------------

Plan hash value: 1731520519
-------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |      |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | MUSE |       |            |          |
|   2 |   COUNT                        |      |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(LEVEL<=1000000)

Statistics
----------------------------------------------------------

700  recursive calls
758362  db block gets
53062  consistent gets
5069  physical reads
355165692  redo size
869  bytes sent via SQL*Net to client
903  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
75  sorts (memory)
0  sorts (disk)
1000000  rows processed

SQL> commit;

Commit complete.

This is notably slower and more expensive than if the index entries had actually been deleted because Oracle is not able to simply identify and overwrite the orphaned index entries during DML operations as they’re not physically marked as deleted. If we look at the INDEX_STATS after inserting these new rows:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                BLOCKS    LF_ROWS DEL_LF_ROWS
--------------- ---------- ---------- -----------
MUSE_ID_I            12288    4000000     1000000

SQL> analyze index muse_code_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                BLOCKS    LF_ROWS DEL_LF_ROWS
--------------- ---------- ---------- -----------
MUSE_CODE_I           9216    2000000      500000

We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead  that none of the space occupied by the orphaned rows has been reused. This in the end means accessing more index blocks, potentially performing more block splits, more newer blocks having to be generated and overall more work having to be performed than would have been necessary if they had just been plain deleted index entries.

So how we actually get rid of these orphaned index entries ? I look at a number of different techniques we can use in Part II. And yes, good old block dumps are on their way as well 🙂