12c Asynchronous Global Index Maintenance Part II (The Space Between) August 6, 2013
Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Coalesce Cleanup, dbms_part.cleanup_gidx, Index Coalesce, Oracle Indexes, Partitioning.trackback
In Part I, I discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the index during subsequent index scans. As such, these table partition operations are very fast and efficient as the global indexes are not actually maintained during the partition operation, but importantly, continue to remain in a usable state.
If we look at a partial 11g global index block dump after dropping a table partition (eg. the MUSE_ID_I in the previous demo):
Block header dump: 0x01028750
Object id on Block? Y
seg/obj: 0x130ac csc: 0x00.3c7323 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x1028748 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0006.001.00000f91 0x00c03e16.0177.02 —- 378 fsc 0x1c0b.00000000
Leaf block dump
===============
header address 130573412=0x7c86464
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0x318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 378
kdxlenxt 16942929=0x1028751
kdxleprv 16942927=0x102874f
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3b
row#1[8002] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3c
row#2[7985] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3d
row#3[7968] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3e
row#4[7951] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3f
….
We notice that all index entries that reference the dropped table partition are marked as deleted. They all have a D (deleted) flag set and have been locked by the drop table partition transaction in ITL slot 2. So prior to Oracle 12c, to update global indexes on the fly was a relatively expensive operation as it required all the associated index entries to be deleted from the global indexes.
However, if we look at a block dump of the same index in an Oracle 12c database following a table partition being dropped:
Block header dump: 0x018000e0
Object id on Block? Y
seg/obj: 0x16bbe csc: 0x00.26ae40 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x18000d8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0026ae40
Leaf block dump
===============
header address 364741220=0x15bd8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0x318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25166049=0x18000e1
kdxleprv 25166047=0x18000df
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3b
row#1[8002] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3c
row#2[7985] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3d
row#3[7968] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3e
row#4[7951] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3f
…
We notice there are no deleted index entries, the index remains totally untouched by the drop table partition operation. So the good news is that dropping/truncating a table partition while updating global indexes is extremely fast and efficient while the indexes remain hunky dory as subsequent index range scans can ignore any rowids that don’t reference existing table partitions of interest.
However, the bad news is that during subsequent index DML operations, Oracle does not know which index entries are valid and which are not and so the space used by these “orphaned” index entries can not be automatically reclaimed and reused as it can with conventionally deleted index entries. Therefore, we need some other way to clean out the orphaned index entries.
There are a number of possible ways to do this. One way is to simply rebuild the global index (or index partition):
SQL> alter index muse_code_i rebuild partition code_p1; Index altered. SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status 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, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME ORP NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ------------ --------------- ------------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 NO 1000000 2944 2758 USABLE MUSE_CODE_I CODE_P2 YES 1000000 4352 4177 USABLE MUSE_ID_I YES 2000000 9216 5849 VALID
Effective, but relatively expensive as this requires the entire index structure to be rebuilt from scratch. Depending on the scale and distribution of the orphaned index entries, another possibly cheaper alternative is to use the new CLEANUP coalesce clause:
SQL> alter index muse_id_i coalesce cleanup; Index altered. SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MUSE_ID_I', estimate_percent=>null); PL/SQL procedure successfully completed. SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status 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, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME ORP NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ------------ --------------- --- --------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 NO 1000000 4224 4137 USABLE MUSE_CODE_I CODE_P2 YES 1000000 4352 4177 USABLE MUSE_ID_I NO 2000000 9216 5849 VALID 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 2000000 0
This visits each index leak block and removes all the orphaned index entries as part of the coalesce process. Note this is a more “powerful” version of coalesce as a standard coalesce is not aware of orphaned index entries and will only coalesce the index without actually removing the orphaned index.
Yet another possible option is to simply wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the 2am maintenance window) to clean out orphaned index entries from all currently impacted global indexes. Yet another alternative is to manually run the dbms_part.cleanup_gidx procedure which is in turn called by this job:
SQL> exec dbms_part.cleanup_gidx; PL/SQL procedure successfully completed. SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status 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, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME ORP NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ------------ --------------- --- ---------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 NO 1000000 2944 2758 USABLE MUSE_CODE_I CODE_P2 NO 1000000 4352 4177 USABLE MUSE_ID_I NO 2000000 9216 5849 VALID
We notice the last index partition has now been cleaned out and no longer has orphaned index entries.
So with the new asynchronous global index maintenance capabilities of the Oracle 12c database, we can perform a much faster and more efficient drop/truncate table partition operation while keeping our global indexes in a usable state and leave the tidying up of the resultant orphaned index entries to another time and method of our convenience.
What about unique indexes? If the index entries aren’t marked deleted but are truly still “there” in the structure, does that mean you can’t use this feature if any global indexes are unique?
Or else is the structure of a unique index modified to include the rowid as part of the key? (which is what’s done for non-unique indexes–but of course you know that already 😉 )
LikeLike
Hi Jason
That’s a good question. Damn, I forgot to mention unique indexes 🙂
The short answer is that unique indexes are an exception and Oracle will indeed reuse the space associated with an orphaned unique index entry if the same value is subsequently reinserted.
This is a similar concept to this previous discussion on the differences between unique and non-unique indexes:
The long answer though is probably best explained with a demo and so I’ll try and whip out another blog article on this subject 🙂
LikeLike
> However, the bad news is that during subsequent index DML operations, Oracle does not know which index entries are valid
Hi Richard,
just for clarification: Why does Oracle know at query time which entries are orphaned and which are not, but shouldn’t know that at DML time?
LikeLike
Hi Randolf
Another good question 🙂
At query time it uses the index to access specific table partitions , even if it happens to be all of them which currently exist. Therefore, it can disregard any rowids that references an object number that no longer exists as it navigates its way through the index entries during an index scan.
In theory, during DML processing, it could do a look-up of all the rowids within a leaf block each and every time and clean out any orphaned index entries it finds. In theory, it could even do this at query time and perform some form of delayed block cleanout. But it doesn’t as it would add additional costs to these processes at these times.
So Oracle takes the approach that at table partition drop/truncate time, Oracle performs the minimum amount of work by not updating the global indexes. At query time, when it’s vital that orphaned index entries do get ignored to ensure accurate results, it does the minimum amount of work to get the correct results. At DML time, it again does the minimum amount of work necessary to just getting the job done.
Remember also that as the orphaned entries aren’t marked in any way as being deleted, there is no quick and easy way for Oracle to know which are which without checking the object numbers of all the rowids in a leaf block. So things such as checking for and reusing fully orphaned index blocks becomes problematic.
So the actual cleaning out is done subsequently, at a time of the DBA choosing.
LikeLike
Regarding “delayed block cleanout” or should we call it delayed index cleanout, maybe oracle will add that in the future , since it can reduce the i/o peak on maintenance time.
This because time for maintenance windows is getting shorter as more of the db’s are 24×7 .
LikeLike
Hi Amihay
It’s always a bit of a balance act when to clean these things out. We don’t really want to impact foreground processes, hence these options but we don’t really want to impact subsequent accesses either. Giving us the choice on when and how to do this is probably a reasonable compromise. I agree that most DBs are 24×7 but most have quieter periods when these things can be done with less overall impact to the business. It’s all new so there will likely be more improvements to come 🙂
LikeLike
[…] “orphaned” index entries are deleted asynchronously [posts by Richard Foote: part 1, part 2, part […]
LikeLike
[…] Part II: The Space Between […]
LikeLike
[…] The Space Between: 12c asynchronous global index maintenance part ii […]
LikeLike
Hello Richard,
Any idea whether the SYS.PMO_DEFERRED_GIDX_MAINT_JOB does an online operation? the documentation does not say anything about that one way or other.
LikeLike