[20130815]12c Asynchronous Global Index Maintenance Part II.txt
参考链接：
http://richardfoote.wordpress.com/2013/08/06/12c-asynchronous-global-index-maintenance-part-ii-the-space-between/
更多的是重复作者的测试，加深理解:
1.测试环境:
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.测试建立：
建立测试数据参考[20130811]12c Asynchronous Global Index Maintenance Part I .txt。
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));
SQL> insert into muse
select rownum, mod(rownum,100000), 'DAVID BOWIE' from
(select 1 from dual connect by level (select 1 from dual connect by level
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.
SQL> alter table muse drop partition muse1 update global indexes;
Table altered.
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 ORPHANED_ENTRIES
-------------------- -------------------- ---------- ---------- ----------- -------- --------------------
MUSE_CODE_I CODE_P1 1500000 4224 4135 USABLE YES
MUSE_CODE_I CODE_P2 1500000 4352 4177 USABLE YES
MUSE_ID_I 3000000 9216 8633 VALID YES
3.我们转储索引信息看看。
SQL> select object_id,data_object_id,object_name from dba_objects where wner=user and object_name='MUSE_ID_I';
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- --------------------
92925 92925 MUSE_ID_I
SQL> alter session set events 'immediate trace name treedump level 92925';
----- begin tree dump 太长截取前面1段。
branch: 0x2400dc3 37752259 (0: nrow: 14, level: 2)
branch: 0x2404937 37767479 (-1: nrow: 673, level: 1)
leaf: 0x2400dc4 37752260 (-1: nrow: 383 rrow: 383)
leaf: 0x2400dc5 37752261 (0: nrow: 378 rrow: 378)
leaf: 0x2400dc6 37752262 (1: nrow: 378 rrow: 378)
leaf: 0x2400dc7 37752263 (2: nrow: 378 rrow: 378)
leaf: 0x2400dc8 37752264 (3: nrow: 378 rrow: 378)
leaf: 0x2400dc9 37752265 (4: nrow: 378 rrow: 378)
...
SQL> @dfb 2400dc4
old 2: dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
new 2: dbms_utility.data_block_address_file(to_number('2400dc4','xxxxxxxxxxxxxxxx')) rfile#,
old 3: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
new 3: dbms_utility.data_block_address_block(to_number('2400dc4','xxxxxxxxxxxxxxxx')) block#
RFILE# BLOCK#
---------- ----------
9 3524
SQL> alter system dump datafile 9 block 3524;
Block header dump: 0x02400dc4
Object id on Block? Y
seg/obj: 0x16afd csc: 0x00.343e73 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2400dc0 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.00343e73
Leaf block dump
===============
header address 366895204=0x15de6064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 383
kdxcofbo 802=0x322
kdxcofeo 1627=0x65b
kdxcoavs 825
kdxlespl 0
kdxlende 0
kdxlenxt 37752261=0x2400dc5
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8020] flag: -------, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 6a fa 02 40 16 98 00 00
row#1[8004] flag: -------, lock: 0, len=16
col 0; len 2; (2): c1 03
col 1; len 10; (10): 00 01 6a fa 02 40 16 98 00 01
....
--可以发现这些flag都没有删除标识。换一句话讲删除分区时这些键值对应的块都没有改动。
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):
4.如何利用这些块呢?
SQL> alter index muse_code_i rebuild partition code_p1;
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 ORPHANED_ENTRIES NUM_ROWS BLOCKS LEAF_BLOCKS STATUS
-------------------- -------------------- -------------------- ---------- ---------- ----------- --------
MUSE_CODE_I CODE_P1 NO 1000000 2816 2756 USABLE
MUSE_CODE_I CODE_P2 YES 1500000 4352 4177 USABLE
MUSE_ID_I YES 3000000 9216 8633 VALID
--可以发现现在PARTITION_NAME=CODE_P1的ORPHANED_ENTRIES = NO.
SQL> alter index muse_id_i coalesce cleanup;
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 ORPHANED_ENTRIES NUM_ROWS BLOCKS LEAF_BLOCKS STATUS
-------------------- -------------------- -------------------- ---------- ---------- ----------- --------
MUSE_CODE_I CODE_P1 NO 1000000 2816 2756 USABLE
MUSE_CODE_I CODE_P2 YES 1500000 4352 4177 USABLE
MUSE_ID_I NO 3000000 9216 8633 VALID
--可以发现现在索引MUSE_ID_I的ORPHANED_ENTRIES = NO.
做一次分析：
SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MUSE_ID_I', estimate_percent=>null);
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 ORPHANED_ENTRIES NUM_ROWS BLOCKS LEAF_BLOCKS STATUS
-------------------- -------------------- -------------------- ---------- ---------- ----------- --------
MUSE_CODE_I CODE_P1 NO 1000000 2816 2756 USABLE
MUSE_CODE_I CODE_P2 YES 1500000 4352 4177 USABLE
MUSE_ID_I NO 2000000 9216 5849 VALID
--统计信息显示正确！
SQL> analyze index muse_id_i validate structure;
SQL> select name, lf_rows, del_lf_rows from index_stats;
NAME LF_ROWS DEL_LF_ROWS
-------------------- ---------- -----------
MUSE_ID_I 2000000 0
--DEL_LF_ROWS =0.
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 ORPHANED_ENTRIES NUM_ROWS BLOCKS LEAF_BLOCKS STATUS
-------------------- -------------------- -------------------- ---------- ---------- ----------- --------
MUSE_CODE_I CODE_P1 NO 1000000 2816 2756 USABLE
MUSE_CODE_I CODE_P2 NO 1500000 4352 4177 USABLE
MUSE_ID_I NO 2000000 9216 5849 VALID
SQL> set autot traceonly ;
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 | 253 | | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=42 AND "ID"--执行计划再没有filter条件。
5.总结一下:1.alter index muse_code_i rebuild partition code_p1;也就是rebuild重建。2.alter index muse_id_i coalesce cleanup;3.exec dbms_part.cleanup_gidx;