ORACLE性能诊断实例-行迁移和行链接 .

通过我的检查,发现,我们的DB同样存在行迁移和行链接.ORACLE的dbms_stats.gather_schema_stats只会收集优化器统计信息,不会检测表的记录是否存在行迁移和行链接.(我原先被这个包给骗了,所以执行完dbms_stats.gather_schema_stats后一查数据字典:USER_TABLES,其中的字段chain_cnt值全为0).要检测表的记录是否存在row chain,需要用到ORACLE早先一点的命令:ANALYZE TABLE.所以我写了一个存储过程(P_analyze_schema)以检测DB:PVH91USRD中所有表存在行链接的情况.

         值得注意的是:数据字典 USER_TABLES.CHAIN_CNT的值是包含了行迁移和行链接的数目.请看ORACLE的解释:

CHAIN_CNT*  Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID

也就是说这个字段的值是行迁移和行链接的总数量,至于要知道具体行迁移有多少,行链接又是多少,ANALYZE TABLE这个命令没办法得到,只有通过DUMP BLOCK来区分(方法复杂,故没有去做).但首先我们要知道行迁移和行链接是两个不同的概念:

(1)行迁移: ORACLE一个BLOCK的DEFAULT SIZE是8K,事实上,一个BLOCK不可以存储8K的数据.一个BLOCK可以存储多少数据,由PCTFREE,PCTUSED参数控制(对于以前的手工管理的表空间而言).

PCTFREE:是指BLOCK保留空闲空间的百分比,用于UPDATE。对于已经插入到BLOCK的行而言，后面的UPDATE操作有可能使行的长度增加，PCTFREE就是用于容纳增加的那部分长度而保留的空闲空间。如果UPDATE时PCTFREE再也不能够容纳行增加的长度，则ORACLE会将整个行迁移到一个新的BLOCK，行的ROWID保留(不是太明白为什么ORACLE不改变ROWID)，原来的BLOCK有一个指针指向ROW存放的新BLOCK。这就是行迁移。可见，行迁移是由于UPDATE操作所导致。从字面上理解，所谓迁移，肯定先有存在这一行，才能叫着迁移.

 

PCTUSED：是指BLOCK用于INSERT的百分比。对于INSERT操作，BLOCK可用于容纳新行的最大空间为Blocksize-pctfree-overhead.当BLOCK数据存储已高于PCTUSED，ORACLE会将该块从自由链表中移除，直到该块已使用空间降到PCTUSED以下，才会再次将此块重新加入到Freelist(这是ORACLE以前手工管理的表空间管理空闲块的原理,现在ORACLE推荐使用ASSM).

行迁移和行链接的检测：

除了我写的存储过程可以检测以外，一个简单的检测方法是：

  select b.NAME,a.VALUE from v$mystat a,v$statname b
    where a.STATISTIC#=b.STATISTIC#
    and b.NAME  like 'table fetch continued row'

当有返回值时，可以知道表的数据存在行迁移和行链接。

行迁移和行链接的清除：

能过REBUILD数据来清除行迁移：

create table MM_PM_temp as select * from MM_PM;
truncate table MM_PM;
insert into MM_PM select * from MM_PM_temp

再重新分析表：

analyze table MM_PM compute statistics;

分析过后再查看：

select t.table_name,
       t.num_rows,
       t.chain_cnt,
       t.avg_row_len,
       round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %"
  from user_tables t
 where t.chain_cnt > 0

如果该表的chain_cnt变为0时，表示原先的chain_cnt全部是行迁移，而不是行链接。如果REBUILD数据后chain_cnt变少，但还大于0，则可以证明，这个表即包含行迁移，又包含真正的行链接。事实证明，行迁移是可以通过REBUILD数据和增加PCTFREE%来清除和减少发生频率的。注意，对于ASSM，PCTUSED，FREELIST，FREELIST GROUPS参数会被忽略。

但对于真正的行链接，只能通过将表移植到大的BLOCSIZE的表空间上，下面是我的实险：

创建一个16K的表空间：

CREATE TABLESPACE LARGETBS   BLOCKSIZE 16 K
    LOGGING
    DATAFILE '/data/app/oracle/oradata/ora33/LARGETBS_01.dbf' SIZE 64M AUTOEXTEND ON NEXT  10M
     MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT  AUTO ;

在创建的时候报了个ORA-的错，原因我们没有设定16的DB_Buffer_cache,我们设定一下：

  alter system set db_16k_cache_size=34603008;

将表MOVE到16K的表空间：

alter table SRC_CS move tablespace LARGETBS;

alter table MM_PM move tablespace LARGETBS;

由于进行了迁移，表的索引会失效，所以我们要REBUILD索引：

alter index PK_SRC_CS rebuild;

alter index PK_MM_PM rebuild;

再重新分析：

analyze table SRC_CS compute statistics;

analyze table MM_PM compute statistics;

重新查询：

select t.table_name,
       t.num_rows,
       t.chain_cnt,
       t.avg_row_len,
       round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %"
  from user_tables t
 where t.chain_cnt > 0
 order by t.table_name

发现，这些表都没有ROW CHAIN了。可见，MOVE到16K的表空间可以清除ROW CHAIN。

一般来讲，一个表如果有多于256个字段，则发生ROW CHAIN的频率比较高。

对于包含long,long raw类型的行，发生ROW CHAIN的概率也比较大。因为long,long raw会尽量先在同一行中存储字段值。而对于CLOB，BLOB等对象，一般来讲，会单独用另外的表空间来存放（oracle推荐这么做），发生ROW CHAIN的概率小一些。但是，有文档指出，当你没有为CLOB指定单独的表空间时，如果CLOB的实际值小4000 BYTE，ORACLE还是会将CLOB字段存放到同一个BLOCK，只有当CLOB的值大于4000 BYTE，ORACLE才会将该字段单独存放。如果真是这样的话，CLOB也会引起ROW CHAIN(如果CLOB和其它字段是同一表空间的话)。

附件：

create or replace procedure p_analyze_schema is
  cursor getdata is
    select table_name
      from user_tables
     order by table_name;
 v_sqlstr varchar2(4000);
begin
  for rec in getdata loop
    v_sqlstr:= 'analyze table  '||rec.table_name||' compute statistics';
    execute immediate(v_sqlstr);
  end loop;
end p_analyze_schema;

请慎重使用下面这个存储过程：

create or replace procedure p_calculate_rowlen is
  cursor getdata is
    select table_name
      from user_tables
     where table_name not in
           ('mm_pm', 'mm_pm_history', 'src_cs', 'src_cs_history')
     order by table_name;
  cursor getdata2(p_tabname varchar2) is
    select t.table_name, t.column_name
      from user_tab_columns t
     where t.table_name = p_tabname
       and t.data_type not in ('clob', 'blob')
     order by t.column_id;
  v_sqlstr clob;
begin
  dbms_output.enable(1000000);
  for rec in getdata loop
    v_sqlstr := ' ';
    for rec2 in getdata2(rec.table_name) loop
      v_sqlstr := v_sqlstr || 'nvl(vsize(' || rec2.column_name || '),0)+';
    end loop;
    v_sqlstr := substr(v_sqlstr, 1, (length(v_sqlstr) - 1));
    v_sqlstr := 'insert into t_tab_row_len select  ''' || rec.table_name ||
                ''',rl from ' || ' (select  (' || v_sqlstr ||
                ') as rl  from  ' || rec.table_name || ')  where rl>=1000';
    begin
      execute immediate (to_char(v_sqlstr));
      commit;
    exception
      when others then
        dbms_output.put_line(rec.table_name);
        return;
    end;
  end loop;
end p_calculate_rowlen;

create table t_tab_row_len
(
  table_name varchar2(60),
  row_len    number(20)
)