BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 行连接/行迁移实验测试


Oracle 行连接/行迁移

行链接(Row chain) 与行迁移(Row Migration)

当一行的数据过长而不能插入一个单个数据块中时,可能发生两种事情:

  • 行链接(Row chain)

  • 行迁移(row migration)

行迁移(Row Migration)

原因:

当发出update导致记录行长增加,block的剩余空间不足以存放这条记录,就会产生行迁移,发生行迁移时rowid不会改变,原来的block中会用一个指针存放这条记录在新的block中的地址,发生行迁移会对性能产生影响,因为读这条记录会读两个BLOCK。

后果:

因为需要访问更多的数据块,性能下降。

预防:

  • 将数据块的PCTFREE调大;
  • 针对表空间扩大数据块大小;

检查:

用dbms_stats不能查出行链接或行迁移

select table_name,chain_cnt from user_tables where table_name in 'TABLENAME';

需要通过

analyze table 表名 validate structure cascade into chained_rows;

测试:

行连接脚本的执行测试:

HR> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
HR> create table test01(col1 varchar2(4000),col2 varchar2(4000),col3 varchar2(4000));

Table created.

HR> insert into test01 values('a','b','c');

1 row created.

HR> commit;                                                         

Commit complete.

HR> select table_name,chain_cnt from user_tables where table_name='TEST01';

TABLE_NAME                      CHAIN_CNT
------------------------------ ----------
TEST01

HR> exec dbms_stats.gather_table_stats(user,'test01',cascade=>true);

PL/SQL procedure successfully completed.

HR> select table_name,chain_cnt from user_tables where table_name='TEST01';

TABLE_NAME                      CHAIN_CNT
------------------------------ ----------
TEST01                                  0

HR> select rpad('a',20,'*') from dual;

RPAD('A',20,'*')
--------------------
a*******************

HR> update test01 set col1=rpad('a','4000','*'); 

1 row updated.

HR> commit;

Commit complete.

HR> update test01 set col2=rpad('b','4000','*');

1 row updated.

HR> commit;

Commit complete.

HR> update test01 set col3=rpad('c',4000,'*');

1 row updated.

HR> commit;                                

Commit complete.

HR> analyze table test01 list chained rows;
analyze table test01 list chained rows
*
ERROR at line 1:
ORA-01495: specified chain row table not found


HR> !ls -ltr $ORACLE_HOME/rdbms/admin/utlchain.sql
-rw-r--r-- 1 oracle oinstall 1160 Jun 24  1998 /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlchain.sql

HR> @$ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

HR> analyze table test01 list chained rows;

Table analyzed.

HR> exec dbms_stats.gather_table_stats(user,'test01',cascade=>true);

PL/SQL procedure successfully completed.

HR> select table_name,chain_cnt from user_tables where table_name='TEST01';

TABLE_NAME                      CHAIN_CNT
------------------------------ ----------
TEST01                                  0

HR> select * from chained_rows;

OWNER_NAME                     TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME                   PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_T
------------------------------ ------------------ ---------
HR                             TEST01

N/A                            AAAS98AAEAAACvrAAA 23-FEB-17


HR> 

消除行迁移实验测试:

=== 创建测试表,修改表结构,update数据

HR> DROP TABLE EMPLOYEES_TEMP PURGE;

Table dropped.

HR> CREATE TABLE EMPLOYEES_TEMP AS SELECT * FROM HR.EMPLOYEES ;

Table created.

HR> desc EMPLOYEES_TEMP;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

HR> create index idx_emp_id on EMPLOYEES_TEMP(employee_id);

Index created.

HR> alter table EMPLOYEES_TEMP modify FIRST_NAME VARCHAR2(1000);

Table altered.

HR> alter table EMPLOYEES_TEMP modify LAST_NAME  VARCHAR2(1000);

Table altered.

HR> alter table EMPLOYEES_TEMP modify EMAIL VARCHAR2(1000);

HR> alter table EMPLOYEES_TEMP modify EMAIL VARCHAR2(1000);

Table altered.

HR> alter table EMPLOYEES_TEMP modify PHONE_NUMBER  VARCHAR2(1000);

Table altered.

HR> desc EMPLOYEES_TEMP;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(1000)
 LAST_NAME                                 NOT NULL VARCHAR2(1000)
 EMAIL                                     NOT NULL VARCHAR2(1000)
 PHONE_NUMBER                                       VARCHAR2(1000)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

HR> 


==== Update数据之后:

HR> UPDATE EMPLOYEES_TEMP
  SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'),
  PHONE_NUMBER = LPAD('1', 1000, '*');
COMMIT;  2    3  
107 rows updated.

HR> 

Commit complete.

==== 发生行迁移后的执行计划

HR> SET AUTOTRACE traceonly statistics
set linesize 1000HR> 
HR> 
HR> select /*+index(EMPLOYEES_TEMP,idx_emp_id)*/ * from EMPLOYEES_TEMP  where employee_id>0;

107 rows selected.


Statistics
----------------------------------------------------------
	 16  recursive calls
	  0  db block gets
	297  consistent gets
	  0  physical reads
	  0  redo size
     437663  bytes sent via SQL*Net to client
	496  bytes received via SQL*Net from client
	  9  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	107  rows processed

HR> 

==== 分析行迁移信息	

HR> analyze table EMPLOYEES_TEMP list chained rows into chained_rows;

Table analyzed.

HR> analyze table EMPLOYEES_TEMP compute statistics;

Table analyzed.

HR> set autot off

HR> select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';

  COUNT(*)
----------
       105

HR> 


==== 发生行迁移对策步骤

HR> create table EMPLOYEES_TEMP_TMP as select * from EMPLOYEES_TEMP where rowid in (select head_rowid from chained_rows);

Table created.

HR> Delete from EMPLOYEES_TEMP where rowid in (select head_rowid from chained_rows);

105 rows deleted.

HR> Insert into EMPLOYEES_TEMP select * from EMPLOYEES_TEMP_TMP;

105 rows created.

HR> delete from chained_rows ;

106 rows deleted.

HR> commit;

Commit complete.

HR> analyze table EMPLOYEES_TEMP list chained rows into chained_rows;

Table analyzed.

HR> select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP'; 

  COUNT(*)
----------
	 0

HR> 

==== count为0,用这种方法做行迁移消除

==== 行迁移优化后,该语句逻辑读情况

HR> SET AUTOTRACE traceonly statistics
HR> set linesize 1000 
HR> select /*+index(EMPLOYEES_TEMP,idx_emp_id)*/ * from EMPLOYEES_TEMP  where employee_id>0;

107 rows selected.


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	116  consistent gets
	  0  physical reads
	  0  redo size
     437033  bytes sent via SQL*Net to client
	496  bytes received via SQL*Net from client
	  9  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	107  rows processed

HR> 

所以通过下面步骤可以消除行迁移:

(以EMPLOYEES_TEMP表为例,如果涉及到该表有主键,并且有别的表的外键REFERENCE关联到本表,必须要执行步骤2和步骤7,否则不必执行):

1.执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。

2.禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES_TEMP表有主键PK_EMPLOYEES_TEMP_ID,假想test表有外键f_EMPLOYEES_TEMP_id关联reference到EMPLOYEES_TEMP表)。

  select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES_TEMP';
  select  CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_TEMP_ID';
  alter table test disable constraint f_EMPLOYEES_TEMP_id;

3.将存在有行迁移的表(用table_name代替)中的产生行迁移的行的rowid放入到chained_rows表中。

4.将表中的行迁移的row id放入临时表中保存。

5.删除原来表中存在的行迁移的记录行。

6.从临时表中取出并重新插入那些被删除了的数据到原来的表中,并删除临时表。

7.启用所有其它表上关联到此表上的所有限制。

     alter table test enable constraint f_EMPLOYEES_TEMP_id;

另外,也可以使用expdp/impdp方式或者alter table move 命令消除行迁移,以及降低HWM,不过这个时候这个应用不能访问该表,而且该表上的index会无效,因为move之后的ROWID变了,此时需要重新创建索引(rebulid)。

此外,在线冲定义到其他大表空间等手段也可以,而且不影响服务情况使用,只是构造实践稍微要复杂点,之后再实验测试下。

行链接(Row chain)

当一个BLOCK不足以存放下一条记录的时候,就会发生行连接,这个时候oracle会把这条记录分成几个部分,分别存放在几个block中,然后把这几个block chain起来。行连接同样会影响性能,因为读一条记录至少会读两个BLOCK.

上面用消除行迁移的方法根本无法消除行链接。只能通过move到大表空间或者在线冲定义move到大表空方式可以消除。

参考:

row migeration and row chain

Oracle行迁移和行链接详解(原创)


Similar Posts

下一篇 PB是最大吗?

Comments