​细谈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%rowcount=0 then
                  raise stop_next;
               else
                  execute immediate 'delete from '||v_data_tname;
                  execute immediate 'insert into '||v_data_tname||' select * from '||j.table_name||' where rowid in (select head_rowid from '||v_rowid_tname||')';
                  execute immediate 'delete from '||j.table_name||' where rowid in (select head_rowid from '||v_rowid_tname||')';
                  execute immediate 'insert into '||j.table_name||' select * from '||v_data_tname;
                  execute immediate 'delete from chained_rows where head_rowid in (select head_rowid from '||v_rowid_tname||')';
                  commit;
               end if;
               if (sysdate - v_begin_time) >= v_run_duration then goto stop_exit; end if;
             exception
               when stop_next or ora_54 then
                 execute immediate 'drop table '||v_data_tname;
                 exit;
             end;
          end loop;
       exception
          when others then
            rollback;
            raise;
       end;
   end loop; --*/
   <<stop_exit>>
   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;
end;
/

--3.2.1  脚本放在数据库的业务账号下执行。
--3.2.2  执行前，须先创建辅助表 chained_rows。
--3.2.3  建议手工执行脚本，分两步先后执行。
--3.2.3.1  使用SQLPLUS工具，用业务账号登录数据库，先执行一遍该脚本，找出存在行迁移/行链接的记录。接下来是第二步，
--3.2.3.2  删除第13行 --/* 这段中的两个注释符：--，此时，从此位置开始，到 /*/ 符号之前的代码将被注释掉，而
--         /*/ 符号之后的代码将生效，此时，再执行一遍该脚本，脚本将根据第一次找出保存在CHAINED_ROWS表里的记录，
--         循环处理：先锁住要处理的表，然后备份要处理的数据，接着删除原表中的数据，最后再把备份表里的数据，插入回原表。
--3.2.3.3  上述的备份删除又是个循环操作，每次将按照变量：v_chnr_nrows　值指定的记录数处理，直到处理完所有记录。
--3.2.3.4  锁住被处理表的原因，是担心在处理期间，被处理的数据发生变化，比如：被处理的记录被备份后，又被其他进程修改，导致备份失效，
--         或者，被处理的记录被备份后，又被其他进程删除，导致备份多出该记录，不适合再插回去。
--         也就是这些原因，我才建议选择在数据库空闲时段，手工操作，而不是设置成定时作业来执行。
--3.2.3.5  处理完当前表后，将释放对该表的锁操作，清空各种辅助表里的数据，然后再处理下一个表。
--3.2.3.6  不论是查找行迁移/行链接记录，还是处理这些记录，最多只运行1/6=4小时，超过该时长（变量v_run_duration设定），脚本将自动结束。

--3.2.4    上面说的是行迁移的处理方法；对于行链接，因为其尺寸太大，一个数据块保存不下，只能跨多块保存，要消除这中现象，只能
--         创建块尺寸更大的表空间，比如：16K，或者32K，然后把存在行链接的表，迁移到该表空间上（使用表重定义包来迁移）。但有个问题：
--         ORACLE在查找行迁移/行链接时，在CHAINED_ROWS表里保存的记录里，并没有说明，哪些是行迁移记录，哪些行链接记录？
--         目前我能够想到的方法，就是跑完第一遍后（正常结束），再跑一遍（看情况，可以都跑几遍），此时，辅助表：
--         execute immediate 'create table CHAINED_ROWS_'||to_char(sysdate,'yyyymmdd_hh24miss')||' as select * from chained_rows';
--         里看到的，应该就是行链接的记录。
--3.2.4.1  需要提醒的是，在创建块尺寸为=16K/32K的表空间前，需要先设定数据库参数：db_16k_block_size/db_32k_block_size 值，并且重启库使之生效。

--3.2.5    脚本中的第一段中的 SELECT 语句，排除这些表不分析：
--3.2.5.1  只读表。
--3.2.5.2  ORACLE的临时表。
--3.2.5.3  CHAINED_ROWS表中出现的表。
--3.2.5.4  以 TMP 开头，总长度达30位，只用字母和数字组成的表。因为这是金蝶云星空定义的临时表，一次性的，不需要处理。
--3.2.5.5  以 VT  开头，总长度达22位以上（含），只用字母和数字组成的表。
--3.2.5.6  存在主外键关系的主表，这类表因为外键的原因，无法删除主表记录。
--3.2.5.7  带 LONG 数据类型的表，这类表由于LONG类型字段的原因，无法使用 insert into select 语句做备份。
--3.2.5.8  带虚拟列字段，因为虚拟列字段的原因，代码里无法排除虚拟列字段，使用 insert into select 语句做备份。


--4 后续，可根据自身情况，定期重跑上述脚本。记得删除这类辅助表：CHAINED_ROWS_'||to_char(sysdate,'yyyymmdd_hh24miss')。

若对文章或脚本有疑问，请发邮件：huafu_zhang@kingdee.com。