[20130607]行迁移与ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt
前一阵子,在ITPUB上讨论避免行迁移的方法.想到ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.
当数据行发生大量的行迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据时，必须访问更多的
数据块（data block）。而一般常规的解决方法就是增加PCTFREE的设置，预留更多的空间给行记录增长，但是又带来另外的问题，由于
插入与修改是交互进行的，设置PCTFREE太大，会导致每个数据块的记录很少（很快达到PCTFREE的限制），磁盘空间浪费。而设置
PCTFREE太小，依旧会出现行迁移的情况。而使用ALTER TABLE MINIMIZE RECORDS_PER_BLOCK命令，本来这个命令的作用是创建位图索引
时减少位图索引的大小，但同时也限制每个数据块记录数的数量，通过这种方式可以在生产系统中很好地解决应用系统中的行迁移问题。
结合自己以前遇到的一个问题,来讲解这个命令:
1.测试环境:
--要看到行迁移,要使用analyze命令.大家要注意analyze,与dbms_stats分析AVG_ROW_LEN不一样.
--SQL> analyze table t list chained rows into chained_rows;
--注: 执行前要建立chained_rows表,最好不要放在system表空间.$ORACLE_HOME/rdbms/admin/utlchain.sql.
2.按照以上情况,要避免出现行迁移,要设置一个非常大的pct_free.(21-10)/21=.523809524,要设置pct_free=53才基本消除行迁移.
而使用ALTER TABLE MINIMIZE RECORDS_PER_BLOCK命令就很简单.
--pct_free设置5,数据块8k的数据块,如何算出每块应该放最大多少行记录呢?
--建立一个新表T1再测试:
-- 不算block=1837的块.这块没有填充满信息.
--根据这个情况选择每块放279条记录比较合适.
--设置每块放置的数量.
--可以发现blocks没有增加,基本确定没有行迁移.
3.如何迁移的问题:
实际上正常的移植就是像前面那样,但是确实比较麻烦,存在一些问题,如果生产系统表很大,很繁忙,这样肯定不行的.一般要使用在线重定义表.
我下面要讲的是我生产系统遇到的问题,就是我已经执行ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;了命令,依旧出现行迁移,说明每
块的数量依旧太多. 我不想再像前面的操作,直接操作里面的基表.实际上使用10046跟踪,可以发现修改的就是sys.tab$的spare1字段 .
spare1=32768+每块的行数-1.注意:修改后要刷新共享池,简单一点先执行ALTER TABLE t1 NOMINIMIZE RECORDS_PER_BLOCK;也可以.取消
这个特性.( 以下仅仅作为测试,不要在生产系统做这些操作!!!)
--以sys用户执行如下:(继续前面的操作)
--这样由于块中存在行号超出260的记录,建立位图索引出现如下错误.
--可以发现执行计划使用了BITMAP CONVERSION FROM ROWIDS以及BITMAP AND等操作.
--要彻底解决执行move表,在重新建立索引,问题才能解决,说明不能偷这个懒,生产系统最好不要避免这种非常规操作.
ALTER TABLE T1 MOVE TABLESPACE users;
alter index i_t1_id1 rebuild;
alter index i_t1_id2 rebuild;
SQL> alter index i_t1_id1 rebuild;
Index altered.
SQL> alter index i_t1_id2 rebuild;
Index altered.
SQL> select * from t1 where id1=42 and id2=42;
ID ID1 ID2 NAME
---------- ---------- ---------- --------------------
41 42 42 test1test2
...
--以此文作为回忆.
前一阵子,在ITPUB上讨论避免行迁移的方法.想到ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.
当数据行发生大量的行迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据时，必须访问更多的
数据块（data block）。而一般常规的解决方法就是增加PCTFREE的设置，预留更多的空间给行记录增长，但是又带来另外的问题，由于
插入与修改是交互进行的，设置PCTFREE太大，会导致每个数据块的记录很少（很快达到PCTFREE的限制），磁盘空间浪费。而设置
PCTFREE太小，依旧会出现行迁移的情况。而使用ALTER TABLE MINIMIZE RECORDS_PER_BLOCK命令，本来这个命令的作用是创建位图索引
时减少位图索引的大小，但同时也限制每个数据块记录数的数量，通过这种方式可以在生产系统中很好地解决应用系统中的行迁移问题。
结合自己以前遇到的一个问题,来讲解这个命令:
1.测试环境:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> drop table t purge ;
SQL> create table t as select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast(NULL as varchar2(10)) name from dual connect by level
Table created.
SQL> desc t
Name Null? Type
----- -------- -------------
ID NUMBER
ID1 NUMBER
ID2 NUMBER
NAME VARCHAR2(10)
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T';
TABLE_NAME PCT_FREE NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T 10 20000 51 0 0 10
SQL> update t set name='test1test2' ;
20000 rows updated.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T';
TABLE_NAME PCT_FREE NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T 10 20000 250 0 0 21
--要看到行迁移,要使用analyze命令.大家要注意analyze,与dbms_stats分析AVG_ROW_LEN不一样.
SQL> analyze table t compute statistics;
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T';
TABLE_NAME PCT_FREE NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T 10 20000 250 6 11490 28
--SQL> analyze table t list chained rows into chained_rows;
--注: 执行前要建立chained_rows表,最好不要放在system表空间.$ORACLE_HOME/rdbms/admin/utlchain.sql.
2.按照以上情况,要避免出现行迁移,要设置一个非常大的pct_free.(21-10)/21=.523809524,要设置pct_free=53才基本消除行迁移.
而使用ALTER TABLE MINIMIZE RECORDS_PER_BLOCK命令就很简单.
--pct_free设置5,数据块8k的数据块,如何算出每块应该放最大多少行记录呢?
--建立一个新表T1再测试:
SQL> create table t1 pctfree 5 as select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast('test1test2' as varchar2(10)) name from dual connect by level
Table created.
SQL> SELECT a, b, COUNT (*) c
FROM (SELECT DBMS_ROWID.rowid_block_number (ROWID) a,
DBMS_ROWID.rowid_relative_fno (ROWID) b FROM t1)
GROUP BY a, b ORDER BY a;
A B C
---------- ---------- ----------
1499 4 293
1500 4 286
1501 4 289
....
1798 4 277
1799 4 279
1801 4 276
1802 4 278
1803 4 278
.....
1832 4 279
1833 4 277
1834 4 277
1835 4 278
1836 4 276
1837 4 214
71 rows selected.
-- 不算block=1837的块.这块没有填充满信息.
SQL> select min(c),max(c),avg(c) from (
SELECT a, b, COUNT (*) c
FROM (SELECT DBMS_ROWID.rowid_block_number (ROWID) a,
DBMS_ROWID.rowid_relative_fno (ROWID) b FROM t1)
5 GROUP BY a, b ORDER BY a) where c214;
MIN(C) MAX(C) AVG(C)
---------- ---------- ----------
276 293 282.657143
--根据这个情况选择每块放279条记录比较合适.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 pctfree 5 as select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast(NULL as varchar2(10)) name from dual connect by level
279 rows created.
--设置每块放置的数量.
SQL> ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
SQL> delete from t1;
279 rows deleted.
SQL> commit ;
Commit complete.
SQL> insert into t1 select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast(NULL as varchar2(10)) name from dual connect by level
20000 rows created.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T1';
TABLE_NAME PCT_FREE NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T1 5 20000 76 0 0 10
SQL> update t1 set name='test1test2' ;
20000 rows updated.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T1';
TABLE_NAME PCT_FREE NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T1 5 20000 76 0 0 21
--可以发现blocks没有增加,基本确定没有行迁移.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T1';
TABLE_NAME PCT_FREE NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T1 5 20000 76 4 0 25
3.如何迁移的问题:
实际上正常的移植就是像前面那样,但是确实比较麻烦,存在一些问题,如果生产系统表很大,很繁忙,这样肯定不行的.一般要使用在线重定义表.
我下面要讲的是我生产系统遇到的问题,就是我已经执行ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;了命令,依旧出现行迁移,说明每
块的数量依旧太多. 我不想再像前面的操作,直接操作里面的基表.实际上使用10046跟踪,可以发现修改的就是sys.tab$的spare1字段 .
spare1=32768+每块的行数-1.注意:修改后要刷新共享池,简单一点先执行ALTER TABLE t1 NOMINIMIZE RECORDS_PER_BLOCK;也可以.取消
这个特性.( 以下仅仅作为测试,不要在生产系统做这些操作!!!)
--以sys用户执行如下:(继续前面的操作)
ALTER TABLE scott.t1 NOMINIMIZE RECORDS_PER_BLOCK;
UPDATE SYS.tab$ SET spare1 = 32768+260-1
WHERE (obj#, dataobj#) IN (SELECT object_id, data_object_id FROM dba_objects WHERE wner = 'SCOTT' AND object_name = 'T1');
COMMIT ;
--这样由于块中存在行号超出260的记录,建立位图索引出现如下错误.
SQL> create bitmap index i_t1_id2 on t1(id2);
create bitmap index i_t1_id2 on t1(id2)
*
ERROR at line 1:
ORA-28604: table too fragmented to build bitmap index (16778715,264,264)
--建立b-tree索引没有问题.
SQL> create index i_t1_id1 on t1(id1);
Index created.
SQL> create index i_t1_id2 on t1(id2);
Index created.
SQL> select * from t1 where id1=42 and id2=42;
select * from t1 where id1=42 and id2=42
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerbtRop:rowidIllegal], [], [], [], [], [], [], [], [], [], [], []
--这个就是我生产系统遇到的问题在春节前的事情,而且走这个计划非常特殊,仅仅在做年报表的时候才执行类似的执行计划.
--使用提示正常,如下
select /*+ full(t1) */* from t1 where id1=42 and id2=42;
select /*+ index(t1 ,i_t1_id1) */* from t1 where id1=42 and id2=42;
select /*+ index(t1 ,i_t1_id2) */* from t1 where id1=42 and id2=42;
--建立如下索引后也正常.
SQL> create index i_t1_id1_id2 on t1(id1,id2);
SQL> drop index i_t1_id1_id2;
SQL> explain plan for select * from t1 where id1=42 and id2=42;
Explained.
SQL> @dp
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2192997210
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 19 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I_T1_ID2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | I_T1_ID1 | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID2"=42)
7 - access("ID1"=42)
20 rows selected.
--可以发现执行计划使用了BITMAP CONVERSION FROM ROWIDS以及BITMAP AND等操作.
--要彻底解决执行move表,在重新建立索引,问题才能解决,说明不能偷这个懒,生产系统最好不要避免这种非常规操作.
ALTER TABLE T1 MOVE TABLESPACE users;
alter index i_t1_id1 rebuild;
alter index i_t1_id2 rebuild;
SQL> alter index i_t1_id1 rebuild;
Index altered.
SQL> alter index i_t1_id2 rebuild;
Index altered.
SQL> select * from t1 where id1=42 and id2=42;
ID ID1 ID2 NAME
---------- ---------- ---------- --------------------
41 42 42 test1test2
...
--以此文作为回忆.
