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

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

参考链接：
http://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/

更多的是重复作者的测试，加深理解:

前面讲了11G的情况：
http://space.itpub.net/267265/viewspace-768592

1.测试环境:

SQL> @ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.测试建立：

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> 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   NO

MUSE_CODE_I          CODE_P2                 1500000       4352        4177 USABLE   NO

MUSE_ID_I                                    3000000       9216        8633 VALID    NO

--注意： ORPHANED_ENTRIES=NO,先做一个查询看看， 注意access条件： access("ID">=42 AND "ID"

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

379 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2515419874

------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |           |   380 |  8360 |     6   (0)| 00:00:01 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE      |   380 |  8360 |     6   (0)| 00:00:01 |     1 |     1 |

|*  2 |   INDEX RANGE SCAN                         | MUSE_ID_I |   380 |       |     4   (0)| 00:00:01 |       |       |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID">=42 AND "ID"

3.删除分区测试:

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

NAME                      VALUE

-------------------- ----------

db block gets                 3

redo size                   752

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

Table altered.

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

NAME                      VALUE

-------------------- ----------

db block gets               137

redo size                 26624

--可以发现操作很快，产生的redo很小。

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   ORPHANED_ENTRIES

-------------------- -------------------- ---------- ---------- ----------- -------- --------------------

MUSE_CODE_I          CODE_P1                 1000000       4224        4135 USABLE   YES

MUSE_CODE_I          CODE_P2                 1000000       4352        4177 USABLE   YES

MUSE_ID_I                                    2000000       9216        5849 VALID    YES

--对比上面ORPHANED_ENTRIES ＝yes,再看看看执行计划:

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"

       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

--注意观察执行计划的filter条件 filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

SQL> select * from muse where code=42;

20 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4070098220

---------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                            |             |    20 |   460 |    23   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE                     |             |    20 |   460 |    23   (0)| 00:00:01 |     1 |     1 |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE        |    20 |   460 |    23   (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

         24  consistent gets

          0  physical reads

          0  redo size

        956  bytes sent via SQL*Net to client

        543  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         20  rows processed

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

SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level SQL> commit ;
SQL> analyze index muse_id_i validate structure;

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

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                    LF_ROWS DEL_LF_ROWS

-------------------- ---------- -----------

MUSE_ID_I               4000000     1000000

--可以发现del_lf_rows信息依旧存在，也就是讲这些删除的信息依旧在索引中，这些块没有重用。

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

-- 索引muse_code_i的情况也一样。
--如果要重用这些块，如何实现呢？ 请看下篇。