ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130813]Global Index Maintenance 11G.txt

[20130813]Global Index Maintenance 11G.txt

原创 Linux操作系统 作者:lfree 时间:2013-08-15 15:39:37 0 删除 编辑
[20130813]Global Index Maintenance 11G.txt

参考链接:
http://richardfoote.wordpress.com/2013/07/26/global-index-maintenance-pre-12c-unwashed-and-somewhat-slightly-dazed/

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

1.测试环境:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


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));

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;

commit;

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

create index muse_id_i on muse(id);

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));

SQL> select index_name, null partition_name, 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, 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                   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ---------- ----------- --------
MUSE_CODE_I                    CODE_P1                           1500000       4224        4137 USABLE
MUSE_CODE_I                    CODE_P2                           1500000       4352        4177 USABLE
MUSE_ID_I                                                        3000000       9216        8633 VALID

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                   776

SQL> alter table muse drop partition muse1;
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                71
redo size                 14240

-- 删除分区索引很快。


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

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ----------- --------
MUSE_CODE_I                    CODE_P1                           1500000        4137 UNUSABLE
MUSE_CODE_I                    CODE_P2                           1500000        4177 UNUSABLE
MUSE_ID_I                                                        3000000        8633 UNUSABLE

--但是索引全部失效。redo size也很小。

3.采用另外的方式,要重新建表看看。

SQL> select index_name, null partition_name, 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, 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                   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ---------- ----------- --------
MUSE_CODE_I                    CODE_P1                           1500000       4224        4137 USABLE
MUSE_CODE_I                    CODE_P2                           1500000       4352        4177 USABLE
MUSE_ID_I                                                        3000000       9216        8633 VALID

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                   776

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

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             56376
redo size              44149272
--可以发现产生的redo size很多。

SQL> select index_name, null partition_name, 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, 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                   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ---------- ----------- --------
MUSE_CODE_I                    CODE_P1                           1500000       4224        4137 USABLE
MUSE_CODE_I                    CODE_P2                           1500000       4352        4177 USABLE
MUSE_ID_I                                                        3000000       9216        8633 VALID

--但是索引保持有效。

SQL> set autot traceonly ;
SQL> select min(id) from muse;
Execution Plan
----------------------------------------------------------
Plan hash value: 2104594370
----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2787  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--可以发现由于前面的空块没有重用,查询最小值,从left扫描,要读许多空块。

--简单的做一个插入操作看看。

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

SQL> commit ;
Commit complete.

SQL> set autot traceonly ;
SQL> select min(id) from muse;
Execution Plan
----------------------------------------------------------
Plan hash value: 2104594370
----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        154  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--一些块已经重用,这样再执行min查询,扫描的块就减少许多。
--这些是11G的情况,而12c将发生什么变化呢,请看后面的blog。
    So that was how things kinda worked in 11g and beforehand. However, with Oracle 12c, things have now changed as
we'll see in the next post …


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-768592/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2595
  • 访问量
    6371057