[20130815]12c Asynchronous Global Index Maintenance Part II.txt

[20130815]12c Asynchronous Global Index Maintenance Part II.txt

參考連結：


更多的是重複作者的測試，加深理解:

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 <= 3000) a,
(select 1 from dual connect by level<=1000) b;

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"<=420)

--執行計劃再沒有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;