​细谈ORACLE数据库的行迁移和行链接

细谈ORACLE数据库的行迁移和行链接

--作者：金蝶云星空基础系统部张华福

--文章基于ORACLE11204版本

--1 行迁移和行链接的产生原因。
--2 行迁移和行链接的查找方法。
--3 行迁移和行链接的处理方法。
--4 后续。

--1 行迁移和行链接产生的原因。

--1.1 行迁移和行链接的定义。

--1.1.1 行迁移：是指一行记录，涉及到两个数据块：在第一个块里保留了这行记录的一个指针，指向这行记录真正的保存位置。当影子进程
--      读取该记录时，需要先读取第一个块，在块里读取到该记录的指针，按照该指针指定的位置，才找到记录/数据真正的保存位置。
--1.1.2 行链接：是指一行记录，其数据分散在两个或者多个数据块里保存。
--1.1.3 两者差异：行迁移数据保存在一个数据块里（第一个块只有指针）；若在一个数据块里保存不下，需要两个或多个块保存，那就是行链接。


--1.2 行迁移和行链接的产生原因。

--1.2.1 在11204版本，ORACLE的数据库块，默认尺寸为8K(8192字节)。这是数据库的最小尺寸单位。
--1.2.2 对于行迁移：是因为该记录被UPDATE时，其所在的块的剩余空间，不足以保存UPDATE过来的新的数据，故，ORACLE只能把该记录，
--      完整地迁移到新的数据块上保存，并在最早的块上留下该记录的位置指针。
--1.2.3 对于行链接：是因为该记录尺寸太大，无法在一个块上保存，只能分散保存到多个数据块上。
--1.2.4 行迁移和行链接记录因为涉及多个数据块，读写时将影响效率，因此，需要尽可能消除这些现象，以提高性能。


--2 行迁移和行链接的查找方法。

--2.1 ORACLE提供了分析表存在行迁移/行链接的命令方法，执行该命令，即可找出表存在的行迁移和行链接信息，保存到指定的辅助表中。
--2.2 命令语句：analyze table 要分析的表名 list chained rows into chained_rows;
--2.3 命令执行结束后，select * from chained_rows，即可查询到存在行迁移/行链接的记录。
--2.4 在执行该命令前，需要先在该数据库账号下，创建辅助表: CHAINED_ROWS。如下脚本：
--    SQL>start $ORACLE_HOME/rdbms/admin/utlchain.sql

--3 行迁移和行链接的处理方法。
--3.1 上面提过，行迁移是因为记录被修改时，所在的块没有足够的空间容纳UPDATE过来的新的数据，ORACLE才将整行记录，迁移到新的块上，
--    换句话说，把行迁移记录备份后，删除老记录，再用备份的记录重新插回去，即可消除这种现象，因为，插入操作，不会引起行迁移。

--3.2 下面的脚本，我运行了多次，现贴出来，供大家参考，若发现脚本存在的BUG/缺陷，请反馈：huafu_zhang@kingdee.com。

set serverout on;
--create or replace procedure pro_deal_chainedrows as
declare
  v_begin_time        date:=sysdate;
  v_run_duration      number(10,5):=1/6;
  v_rowid_tname       varchar2(30):='T_CHNR_ROWID_'||to_char(sysdate,'yyyymmdd');
  v_data_tname        varchar2(30):='T_CHNR_DATA_'||to_char(sysdate,'yyyymmdd');
  v_chnr_nrows        number(10):=5000;
  stop_next           exception;
  ora_54              exception;
  pragma              exception_init(ora_54,-54);
  ora_942             exception;
  pragma              exception_init(ora_942,-942);
begin
   --/*
   for i in (select ut.table_name, ut.num_rows, ut.last_analyzed from user_tables ut
               left join (select table_name from user_tab_cols where (virtual_column='YES' or data_type='LONG') group by table_name) utc
               on utc.table_name=ut.table_name
               left join (select table_name from chained_rows group by table_name) chained_rows
               on chained_rows.table_name=ut.table_name
               left join (select uc2.table_name from user_constraints uc1
                          inner join user_constraints uc2 on uc1.r_constraint_name=uc2.constraint_name
                          where uc1.constraint_type='R' and uc2.constraint_type='P'
                            and uc1.status='ENABLED' and uc2.status='ENABLED' group by uc2.table_name) Refed_tables
               on Refed_tables.table_name=ut.table_name
               where last_analyzed is not null and num_rows > 0 and temporary='N' and read_only='NO'
               and not ( (regexp_like(ut.table_name,'^TMP[[:alnum:]]{27}$')) or
                         (regexp_like(ut.table_name,'^VT[[:alnum:]]{20}')) )
               and utc.table_name is null and chained_rows.table_name is null and Refed_tables.table_name is null
               order by ut.num_rows desc, ut.table_name) loop
         begin
           execute immediate 'analyze table "'||i.table_name||'" list chained rows into chained_rows';
           if (sysdate - v_begin_time) >= v_run_duration then return; end if;
         exception
           when ora_942 then
             dbms_output.put_line(i.table_name);
         end;
   end loop;
   dbms_stats.gather_table_stats(user,'CHAINED_ROWS',method_opt=>'for all columns size auto',estimate_percent=>100);
   execute immediate 'create table CHAINED_ROWS_'||to_char(sysdate,'yyyymmdd_hh24miss')||' as select * from chained_rows';
   /*/
   for i in (select table_name from user_tables where table_name=v_rowid_tname or table_name=v_data_tname) loop
     execute immediate 'drop table '||i.table_name;
   end loop;
   execute immediate 'create table '||v_rowid_tname||'(head_rowid rowid)';   
   for j in (select * from (
             select table_name, count(*), min(analyze_timestamp) from chained_rows
             group by table_name order by min(analyze_timestamp) nulls last) where rownum <= 50) loop
       begin
          execute immediate 'create table '||v_data_tname||' as select * from '||j.table_name||' where 1=2';
          loop
             begin
               execute immediate 'lock table '||j.table_name||' in exclusive mode wait 60';
               execute immediate 'delete from '||v_rowid_tname;
               execute immediate 'insert into '||v_rowid_tname||' select head_rowid from chained_rows where table_name='''||j.table_name||''' and rownum <= '||v_chnr_nrows;
               if sql%rowco