oracle效能診斷例項-row migration and row chain

美國PVH DB存在行連結.透過我的檢查,發現,我們的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操作所導致。從字面上理解，所謂遷移，肯定先有存在這一行，才能叫著遷移. 行連結:是指一個BLOCK不能容納一行(行的長度太大),而必須將此行存放於幾個BLOCK.行連結一般是在Insert時產生的.一個BLOCK能否用於insert是由PCTUSED控制. 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) )[@more@]