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

行迁移
成 因:当发出update导致记录行长增加,block的剩余空间不足以存放这条记录,就会产生行迁移,发生行迁移时rowid不会改变,原来的block 中会用一个指针存放这条记录在新的block中的地址,发生行迁移会对性能产生影响,因为读这条记录会读两个BLOCK。
后果:导致应用需要访问更多的数据块,性能下降。
预防:1. 将数据块的PCTFREE调大;2. 针对表空间扩大数据块大小
检查:analyze table 表名 validate structure cascade into chained_rows;
操作:(以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;
注意:此外还可以采用move和exp/imp的方式(特别注意move会导致索引失效,需要重建索引)。
行迁移实验
--- PCTFREE试验准备之建表
DROP TABLE EMPLOYEES_TEMP PURGE;
CREATE TABLE EMPLOYEES_TEMP AS SELECT * FROM HR.EMPLOYEES ;
desc EMPLOYEES_TEMP;
create index idx_emp_id on EMPLOYEES_TEMP(employee_id);
--- PCTFREE试验准备之扩大字段
alter table EMPLOYEES_TEMP modify FIRST_NAME VARCHAR2(1000);
alter table EMPLOYEES_TEMP modify LAST_NAME  VARCHAR2(1000);
alter table EMPLOYEES_TEMP modify EMAIL VARCHAR2(1000);
alter table EMPLOYEES_TEMP modify PHONE_NUMBER  VARCHAR2(1000);
--- PCTFREE试验准备之更新表
UPDATE EMPLOYEES_TEMP
  SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'),
  PHONE_NUMBER = LPAD('1', 1000, '*');
COMMIT;
---行迁移优化前,先看看该语句逻辑读情况(执行计划及代价都一样,没必要展现了,就展现statistics即可)
SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES_TEMP,idx_emp_id)*/ * from EMPLOYEES_TEMP  where employee_id>0;
/
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        219  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
set autotrace off
----- 发现存在行迁移的方法
--首先建chaind_rows相关表,这是必需的步骤
@?/rdbms/admin/utlchain.sql
----以下命令针对EMPLOYEES_TEMP表和EMPLOYEES_TEMP_BK做分析,将产生行迁移的记录插入到chained_rows表中
analyze table EMPLOYEES_TEMP list chained rows into chained_rows;

注意:这里只能用analyze table,不能使用dbms_stats,具体请参见
http://czmmiao.iteye.com/blog/1483190
analyze table EMPLOYEES_TEMP compute statistics;
select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';
---以下方法可以去除行迁移
drop table EMPLOYEES_TEMP_TMP;
create table EMPLOYEES_TEMP_TMP as select * from EMPLOYEES_TEMP where rowid in (select head_rowid from chained_rows);
Delete from EMPLOYEES_TEMP where rowid in (select head_rowid from chained_rows);
Insert into EMPLOYEES_TEMP select * from EMPLOYEES_TEMP_TMP;
delete from chained_rows ;
commit;
analyze table EMPLOYEES_TEMP list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';
--这时的取值一定为0,用这种方法做行迁移消除!
---行迁移优化后,先看看该语句逻辑读情况(执行计划及代价都一样,没必要展现了,就展现statistics即可)
SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES_TEMP,idx_emp_id)*/ * from EMPLOYEES_TEMP  where employee_id>0;
/
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       9221  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
行迁移消除后逻辑读明显减少。

行链接

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

行链接实验
继续扩大字段
alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);
alter table EMPLOYEES modify LAST_NAME  VARCHAR2(2000);
alter table EMPLOYEES modify EMAIL VARCHAR2(2000);
alter table EMPLOYEES modify PHONE_NUMBER  VARCHAR2(2000);
--- PCTFREE试验准备之更新表
UPDATE EMPLOYEES
  SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'),
  PHONE_NUMBER = LPAD('1', 2000, '*');
COMMIT;
---行链接移优化前,先看看该语句逻辑读情况
SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0
/

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        324  consistent gets
          0  physical reads
          0  redo size
     868528  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
set autotrace off
----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析,将产生行迁移的记录插入到chained_rows表中
analyze table EMPLOYEES list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES';
---用消除行迁移的方法根本无法消除行链接!!!
drop table EMPLOYEES_TMP;
create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);
Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);
Insert into EMPLOYEES select * from EMPLOYEES_TMP;
delete from chained_rows ;
commit;
--发现用消除行迁移的方法根本无法消除行链接!
analyze table EMPLOYEES list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES';
COUNT(*)

---------------
321
---------------------------------------------------------------------------------
---启动大小为16K的块新建表空间(
--行链接只有通过加大BLOCK块的方式才可以避免,如下:
create tablespace dba_16k
blocksize 16K
datafile  '/home/oracle/dba_16k.DBF' size 100M 
autoextend on 
extent management local
segment space management auto;
alter table EMPLOYEES_TEMP move tablespace dba_16k;
alter index idx_emp_id rebuild ;
delete from chained_rows ;
commit;
analyze table EMPLOYEES_BK list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';
COUNT(*)

---------------
0


SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0
/
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        116  consistent gets
          0  physical reads
          0  redo size
     866745  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

参考至:http://blog.csdn.net/robinson1988/article/details/4728717

              《基于案例学习sql优化》著梁剑斌

如有错误,欢迎指正

邮箱:czmcj@163.com

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值