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)
)
　　
　　行链接:是指一个BLOCK不能容纳一行(行的长度太大),而必须将此行存放于几个BLOCK.行链接一般是在Insert时产生的.一个BLOCK能否用于insert是由PCTUSED控制.
　　本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/rainnyzhong/archive/2010/03/12/5374588.aspx