[20130607]行迁移与ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt

[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.测试环境: 

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
...
--以此文作为回忆.
				


				
时间： 2024-09-22 04:03:38

		
		


		


	

	
	

		


		
[20130607]行迁移与ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt的相关文章


								

		

			

                [20140209]行迁移和expdp导出.txt
			

		

		

									

				[20140209]行迁移和expdp导出.txt 前一阵子与别人聊天,谈到一个系统升级expdp导出很慢,我比较熟悉这个系统,当他说出导出很慢的那张表的时候, 我随口讲不会这个表存在大量的行迁移吧.我记得以前学习oracle,听别人讲课,讲过一句话,如果你看这个系统 的用户模式下所有表的pctfree设置都是10的话,那么这个系统没有dba管理.按照这样看,中国大部分数据库系统 没有dba管理. 我自己以前对行迁移还是比较重视的,当然现在变懒了.我发现许多dba也不是太重视这个问题,慢慢我对这			

		

	

				

		

			

                【性能优化】消除行迁移 table fetch continued row
			

		

		

									

				消除行迁移 table fetch continued row 1.搭建实验环境 1.1.创建新表,同时把表的pctfree设置为0 SCOTT@ prod>create table emp3 as select * from emp where 1=2; SCOTT@ prod>alter table emp3 modify empno number(10); SCOTT@ prod>alter table emp3 modify ENAME varchar2(30); SCOTT@			

		

	

				

		

			

                Oracle数据库中行迁移/行链接学习（一）什么是行迁移/行链
			

		

		

									

				在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题,当然,引起Oracle数据库性能较低的原因是多方面的,我们能够通过一些正确的设计和诊断来尽量的避免一些Oracle数据库性能不好,Row Migration (行迁移) & Row Chaining (行链接)就是其中我们可以尽量避免的引起Oracle数据库性能低下的潜在问题.通过合理的诊断行迁移/行链接,我们可以较大幅度上提高Oracle数据库的性能. 那究竟什么是行迁移/行链接呢,先让我们从Oracle的block开始谈起.			

		

	

				

		

			

                行链接和行迁移的秘密
			

		

		

									

				一.概述:  如果你的Oracle数据库性能低下,行链接和行迁移可能是其中的原因之一.我们能够通过合理的设计或调整数据库来阻止这个现象.    行链接和行迁移是能够被避免的两个潜在性问题.我们可以通过合理的调整来提高数据库性能.本文主要描述的是:    什么是行迁移与行链接    如何判断行迁移与行链接    如何避免行迁移与行链接   当使用索引读取单行时,行迁移影响OLTP系统.最糟糕的情形是,对所有读取操作而言,增加了额外的I/O.行链接则影响索引读和全表扫描.    注:在翻译行(row			

		

	

				

		

			

                行链接 行迁移的消除
			

		

		

									

				模拟行链接: 如何模仿行链接? 首先要了解三个语句: 第1句:运行$ORACLE_HOME/rdbms/admin/utlchain.sql 脚本,SQL> @D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\utlchain.sql创建chained_rows表,用于存放发生行迁移         的行的rowid. 第2句:运行analyze table table_name list chained rows into chained_rows; 把产生行			

		

	

				

		

			

                [20160726]行链接行迁移与ITL槽.txt
			

		

		

									

				[20160726]行链接行迁移与ITL槽.txt 当表中一行的数据不能在一个数据block中放入的时候,这个时候就会发生两种情况,一种是行链接(Row Chaining),另外一种就是行迁 移(Row Migration)了. 行链接产生在第一次插入数据的时候如果一个block不能存放一行记录的情况下.这种情况下,Oracle将使用链接一个或者多个在这个段 中保留的block存储这一行记录,行链接比较容易发生在比较大的行上,例如行上有LONG.LONG RAW.LOB等数据类型的字段,这种时候			

		

	

				

		

			

                [20160728]]行链接行迁移与ITL槽3.txt
			

		

		

									

				[20160728]]行链接行迁移与ITL槽3.txt --上午测试了行链接行迁移与ITL槽的关系,链接如下: [20160727]行链接行迁移与ITL槽2.txt => http://blog.itpub.net/267265/viewspace-2122663/ --如果仔细看前面的测试可以发现当出现行链接或者行迁移时,除了增加1个空itl槽像如下: 0x05   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0			

		

	

				

		

			

                [20160729]行链接行迁移与ITL槽4.txt
			

		

		

									

				[20160729]行链接行迁移与ITL槽4.txt --做了几个测试,有点乱. http://blog.itpub.net/267265/viewspace-2122700/ http://blog.itpub.net/267265/viewspace-2122663/ http://blog.itpub.net/267265/viewspace-2122599/ --还是通过1个例子来模拟看看.做一个非常极端的测试: 1.环境: SCOTT@book> @ &r/ver1 PORT_ST			

		

	

				

		

			

                [20160803]另类行迁移.txt
			

		

		

									

				[20160803]另类行迁移.txt --前几天做测试时,链接: http://blog.itpub.net/267265/viewspace-2122712/=>[20160729]行链接行迁移与ITL槽4.txt --发现一个块中的记录在字段长度变长后全部发生行迁移,感觉很奇怪,当时也没有仔细思考(开始以为至少有一些记录不会发生行迁移的 --情况),事后才想起来以前我做过类似的测试,参考链接: http://blog.itpub.net/267265/viewspace-1742243/=