Oracle 9i 整體效能優化概述草稿之四:調整磁碟I/O (zt)

5 調整磁碟I/O
通過適當的確定SGA的大小來儘可能的減少物理I/O；在必要時，儘可能快的執行任何物理剩餘的物理I/O。

磁碟I/O的來源：
１） Database buffer cache 到datafile的DBW0寫操作和逆向的使用者讀Server Process。
２） 寫資料到UNDO上維護讀一致的DBW0。
３） Redo log buffer到logfile的LGWR寫操作。
４） 日誌內容到歸檔的ARC0操作。
５） 應用的磁碟排序操作。

基礎概念：
段：Oracle的表或索引
範圍：為段提供的儲存實際資料的邏輯單位，是一個或多個連續的資料塊。
Oracle塊：具體儲存實際資料的Oracle單位，由一個或多個連續的作業系統塊組成。
表空間：儲存資料的邏輯檔案。
資料檔案：儲存資料的物理檔案。

5.1 資料檔案I/O調整
5.1.1 測量資料檔案IO
v$filestat,v$datafile,v$tempfile
l 蒐集至資料庫啟動以來所發生的
確定：init.ora parameters的timed_statistics設定為TRUE
select s.file#||'D',d.name,s.phyrds,s.phywrts,s.avgiotim,s.miniotim,s.maxiowtm,s.maxiortm
from v$filestat s,v$datafile d
where s.file# = d.file#
union
select s.file#||'D',t.name,s.phyrds,s.phywrts,s.avgiotim,s.miniotim,s.maxiowtm,s.maxiortm
from v$filestat s,v$datafile t
where s.file# = t.file#
order by 3 desc;
注：
s.phyrds：物理讀次數
s.phywrts：物理寫次數
s.avgiotim：執行I/O所花費的平均時間（毫秒）
s.miniotim：執行I/O所花費的最短時間（毫秒）
s.maxiowtm：執行寫I/O所花費的最長時間（毫秒）
s.maxiortm：執行讀I/O所花費的最長時間（毫秒）

l 蒐集某個時間以來所發生的
$ORACLE_HOME/rdbms/admin/utlbstat.sql
$ORACLE_HOME/rdbms/admin/utlestat.sql
SQL>Rem Sum IO operations over tablespaces
SQL>Rem I/O should be spread evenly across drives…
如上兩段描述了表空間和資料檔案分別的I/O資訊。

 定時蒐集statspackl

5.1.2 改進資料檔案I/O
5.1.2.1 保證非Oracle I/O不干涉資料檔案I/O
不要將非Oracle檔案與資料檔案放在相同的磁碟驅動器或邏輯捲上。否則不僅可能會存在對磁碟資源的競爭，而且此時的I/O統計資料不能反映真實的磁碟I/O資料。

檢查：
使用作業系統命令。

5.1.2.2 使用locally Managed tablespace減少I/O
使用LMT建立的表空間，不再使用free list管理範圍，從而減少free list 競爭。這樣就不在需要訪問SYSTEM表空間中的系統資料目錄，而在表空間頭使用點陣圖的方法，能更快的分配表空間內的空間。

檢查：
SQL>select tablespace_name,status,contents,extent_management from dba_tablespaces
where extent_management != 'LOCAL';

5.1.2.3 把資料檔案均分到許多裝置、邏輯卷和控制器上
１） 不應把任何應用程式段儲存在SYSTEM表空間。保證資料庫使用者不使用SYSTEM作為預設或臨時表空間。由於執行遞迴的SQL存放在SYSTEM表空間，所以注意SYSTEM表空間可能有頻繁的讀活動。
檢查：
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024
from dba_segments where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS','MDSYS');

select username,user_id,account_status,default_tablespace,temporary_tablespace,created,profile
from dba_users where username not in
('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS','MDSYS','ORDPLUGINS')
and (default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM');

(轉移物件：alter table .. move tablespace .. ;
alter index .. rebuild tablespace ..;)

２） 應設法把應用於各表空間的資料檔案放到不同的磁碟驅動器、卷組或磁碟控制器上，最大限度減少磁碟爭用；同時也要把非資料檔案的資料庫元件（如controlfile,redo log file, archive file ..）進行分開，因為他們的I/O特徵是不一樣的。
%df -k
- 將系統、臨時、回滾段和應用的表和索引分開。
- redo log和archive log分開。
- controlfile與datafile分開。
- dump file由於隨機的，且操作不多，可忽略。
- 不在SYSTEM上設定回滾段和臨時段。
- 可把段按功能或大小及應用進行分開。

5.1.2.4 在資料檔案I/O發生時，儘可能快的完成全部資料檔案I/O操作
１） 將高頻率的I/O資料檔案放到不同的磁碟驅動器和控制器上。
　　
２） 條狀化表空間。
把資料檔案放在RAID裝置上，已經隱含使用了條狀化，不需要DBA做相關調整（只需要注意條寬度和條深度）。
手工條狀化：
建立一個由幾個資料檔案組成的表空間，每個資料檔案放在不同的物理裝置上，接著建立一個段，使它的範圍被儲存在這些資料檔案上。
例：
建立表空間
SQL>create tablespace app1_data
Datafile ‘/u01/oradata/prod/app1_data01.dbf’ size 5M
Extent management local
Autoextend on next 5M maxsize 2000M;
建立emp表的初始範圍
SQL> create table emp (emp_id number,last_name varchar2(20))
Storage (initial 4M next 4M pctincrease 0)
Tablespace app1_data;
給app1_data表空間新增資料檔案
SQL>alter tablespace app1_data
Add datafile ‘/u02/oradata/prod/app1_data02.dbf’ size 5M;
手工分配emp表的新範圍
SQL>alter table emp
Allocate extent (datafile ‘/u02/oradata/prod/app1_data02.dbf’ size 4M);

（調整的表的時候可以使用；但一般與管理他的時間相比，手工條狀化費時且好處不多）

３） 調整init.ora 的　DB_FILE_MULTIBLOCK_READ_COUNT。
　　DB_FILE_MULTIBLOCK_READ_COUNT引數指定一個使用者server process在執行全表掃描時在單個I/O中讀取的最多資料塊個數。預設值是８。
通過增加該值，可以每次I/O訪問更多的塊，減少I/O次數。
（１， CBO可能會受該引數影響，認為使用全表掃描比使用索引有更少的I/O時候，會選擇全表掃描而不使用索引
（２， 使用RAID條狀時，應將該值設成條大小的一個倍數）。
　　
　　先確定應用程式執行全表掃描的頻率：
SQL>select name,value from v$sysstat where name = 'table scans (long tables)';
　　如果值很大，說明增加該值可能效能會有優化。

同時，
select * from v$session_longops　where time_remaining > 0;
監視執行時間很長的選定操作相關的活動情況（每當一個程式掃描了10,000個以上的　Ｏracle塊時，table scan操作就會出現在該檢視中，並不表示該操作一定是全表掃描），“time_remaining > 0”說明檢查當前活動的。
如果需要檢查具體執行時間，可以使用DBMS_APPLICATION_INFO.SET_SESSION_LONG_OPS向v$session_longops註冊一個使用者會話的方法瞭解。
（這個監視應發揚光大）

5.1.2.5 日誌檔案
建立大小合適的日誌檔案以最小化競爭；
提供足夠的日誌檔案組以消除等待現象；
將日誌檔案存放在獨立的、能快速訪問的儲存裝置上（日誌檔案可以建立在裸裝置上）。

日誌檔案以組的方式組織管理，每個組裡的日誌檔案的內容完全相同。
5.1.2.6 歸檔日誌檔案
如果選擇歸檔模式，必須要有兩個或兩個以後的日誌組，當從一個組切換到另一個組時，會引起兩種操作：DBWn進行Checkpoint；一個日誌檔案進行歸檔。

歸檔有時候會報錯：
ARC0：Beginning to archive log# 4 seq# 2772
Current log# 3 seq# 2773……
ARC0: Failed to archive log# 4 seq# 2772
ARCH: Completed to archiving log#4 seq# 2772
建議init引數修改如下：
log_archive_max_processes=2
#log_archive_dest = ‘/u05/prodarch’
log_archive_dest_1 = "location=/u05/prodarch MANDATORY’
log_archive_dest_state_1 = enable

log_archive_dest_2 = "location=/u05/prodarch2 OPTIONAL reopen=10" （或其它目錄）
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest=1

log_archive_dest_state_3 = DEFER
log_archive_dest_state_4 = DEFER
log_archive_dest_state_5 = DEFER

5.2 DBW0
DBW0負責把database buffer cache寫到資料檔案上，在checkpoint和使用者server process在database buffer cache搜尋自由緩衝區時觸發DBW0。
5.2.1 測量DBW0效能
l 系統檢視檢查至資料庫啟動以來的資料
１）v$system_event
select event,total_waits,average_wait
from v$system_event
where event in
('buffer busy waits','db file parallel write','free buffer waits','write complete waits');
註釋：
buffer busy waits：針對database buffer cache中的緩衝區等待正發生，可能包括DBW0寫髒緩衝區效率不高所致。
db file parallel write：DBW0並行寫遇到等到。可能是因為datafile駐留在一個速度慢的裝置上引起，也可能DBW0的處理速度跟不上要求他寫的那些請求時間引起。
free buffer waits：使用者server process在把髒緩衝區放到dirty list上，同時搜尋LRU的一個自由緩衝區時遇到等待。
write complete waits：使用者會話一直遇到DBW0從database buffer cache中寫緩衝區等到。
如果查詢的total_waits很高，且不住增長，所以DBW0沒有有效的執行操作。

2）v$sysstat
select name,value from v$sysstat
where name in ('redo log space requests','DBWR buffers scanned','DBWR lru scans');
注：
redo log space requests：表示在日誌切換後，等候redo log可用的等待事件。（說明了redo log的組數，成員數，大小需要好好的考慮了）
DBWR buffers scanned：Database buffer cache中檢查詢出待寫髒緩衝區的總數量。
DBWR lru scans：lru掃描次數。
Value以毫秒為單位。

select scanned.value/scans.value "avg.num. buffers scanned"
from v$sysstat scanned,v$sysstat scans
where scanned.name = 'DBWR buffers scanned'
and scans.name = 'DBWR lru scans';
如果"avg.num. buffers scanned"值很高或不斷增長，說明DBW0在底效的執行寫操作。

 STATSPACKl
 Utlbstat.sql utlestat.sqll

5.2.2 改進DBW0效能
兩個init.ora引數用於改進DBW0效能：
DBWR_IO_SLAVES：啟動DBWR0的從屬程式
DB_WRITER_PROCESSES：啟動DBWR同樣的程式

注意：如果DBWR_IO_SLAVES非０，則DB_WRITER_PROCESSES設定無效。
至於是啟動從屬程式，還是啟動DBWRn程式，則需要根據具體等待時間是否需要做buffer cache管理等待等進行判斷，即如果僅僅是寫等待，則啟動從屬程式；如果是如free buffer waits，則啟動DBWRn程式。

5.2.2.1 DBWR_IO_SLAVES
預設值是０，可以配置的最大值由作業系統決定。
如，Ora_i102_prod，i表示是從屬程式，1表示使用第一個介面卡（一個記憶體池），03是使用１號適配置器的第３個程式。
從屬DBWR只會執行寫操作，不能完成如把緩衝區從LRU list轉移到database buffer cache內的dirty list上。
DBWR工作原理：
在有寫操作請求時，DBWR0協調從屬程式處理，如果所有可用的從屬程式都忙，則啟動一個從數程式處理（最多啟動的從屬程式不會超過本引數指定的數值），如果無法啟動，則產生等待事件。

注意：
在配置了該引數非０時，每當database writer產生I/O從屬程式時，執行磁碟I/O的其他程式也會產生從屬程式，如log writer、archiver、recovery manager。

5.2.2.2 DB_WRITER_PROCESSES
預設值是１，最大值是10。
本引數指定啟動多少個DBWR程式，而非啟動DBWR的從屬程式；
啟動的DBWR程式，具有與DBWR0完成相同的功能。

5.2.2.3 Checkpoint
Checkpoint進行的操作：DBWn進行IO操作；CKPT更新資料檔案頭和控制檔案。
經常進行Checkpoint的結果：減少恢復所需的時間；降低了系統執行時的效能。

LGWR以迴圈的方式將日誌寫到各個日誌組，當一個日誌組滿時，oracle server必須進行一個Checkpoint，這意味著：DBWn將對應log覆蓋的所有或部分髒資料塊寫進資料檔案；CKPT更新資料檔案頭和控制檔案。如果DBWn沒有完成操作而LGWR需要同一個檔案，LGWR只能等待。
在OLTP環境下，如果SGA很大並且checkpoint的次數不多，在Checkpoint的過程中容易出現磁碟競爭的狀況，在這種情況下，經常進行Checkpoint可以減少每次Checkpoint涉及到的髒資料塊的數目。

調節Checkpoint次數的辦法：
增大日誌檔案；增加日誌組以增加覆蓋的時間間隔。

5.3 單個段資料塊I/O調整
調整目標：最小化檢索被請求的應用資料而必須訪問的塊數量。

有兩種塊大小需要注意：
１） 主塊：在資料庫建立時由init.ora的db_block_size引數指定，至少用於建立SYSTEM和TEMP表空間。改變主塊大小的唯一辦法是重新建立資料庫。
一般預設win2000使用2k，SUN使用8k。
２） 區域性塊：在建立表空間時，指定blocksize關鍵字（如果沒有指定blocksize關鍵字，則使用主塊大小）。

塊大小可指定的值的範圍為db_nk_cache_size引數的n的數值（要求是作業系統塊的整數倍；要求、不超過作業系統最大的I/O大小）。

select tablespace_name,block_size from dba_tablespaces;

一個範圍是一組連續的Oracle塊，當建立一個段時，將至少分配一個範圍（叫初始範圍）給段，初始範圍的第一個塊（也叫頭部塊）含有一個線路圖，描述了該範圍內其他所有塊的位置。

建立表空間，建立表，建立索引的所有可指定的引數和關鍵字，值得仔細研究一下。

一個Oracle塊分為塊頭區、保留區（pctfree參考）和自由區（pctused參考）。
l 塊頭區：每個塊一般使用分配給他的50到200個位元組來儲存關於該塊內容的一些頭部資訊。（包括：initrans產生在表建立時指定的transaction slot，該塊內含有的行的一個目錄和管理該塊需要的其他一般性頭部資訊）
l 保留區和自由區：一個塊，除了分配給塊頭區的外，剩下的都是保留區和自由區，其中pctfree引數指定整個塊大小的百分之多少留做保留區儲存行被更新，剩下的都是可儲存行的自由區。

塊剛被分配的時候，是加在free list的最頂端等待被使用，當行被insert資料到自由區，整個塊大小隻剩下pctfree的時候，將從free list中被移走，不再插入新行。
此時如果有對該塊的update操作，則使用pctfree中指定的空閒空間來擴充套件；如果發生delete操作，則一旦刪除資料行後，如果整個塊的空閒空間佔整個塊的pctused，則重新把該塊放到free list的最頂端（當不連續的自由空間足於可以被insert一新行時，在執行insert前，Oracle會自動合併自由區）。

Pctfree （預設10%）和 pctused（預設40%），可以在段建立時指定，也可在段建立後alter，但對已儲存了資料而又沒有進入free list的塊無效。

Oracle建議的設定方法：
PCTFREE = 100*Avg.Update Size(bytes)/Avg.Row length
PCTUSED = 100 – PCTFREE – 100*Num.Rows in Table*(Avg.Row Length) / Block Size

select * from dba_tables; //查詢表pctfree和pctused

5.3.1 測量段效能和調整
5.3.1.1 動態範圍分配與效能
Oracle 9i自動使用management local區域性管理表空間的方法，在資料檔案標頭檔案中使用點陣圖來管理和分配範圍，只簡單的改變點陣圖的值來表示資料檔案內塊的狀態，只涉及極少的底歸I/O，可忽略一向。

而如果不是使用management local，則使用了free list，此時段的可用範圍滿了後，擴充套件新段，需要查詢SYSTEM表空間的相關管理資訊和範圍空間的實際獲取，會增加更多的I/O操作。只能經常檢查，範圍使用到一定比例的表，手工分配更多的範圍，但Oracle建議，為了效能，一個段的範圍數量不應該超過1000個（而management local可以有數千個範圍）。

SQL>select owner,table_name,1-(empty_blocks/(empty_blocks+blocks)) "%blocks used"
from dba_tables
where owner != 'SYS'
and 1-(empty_blocks/(empty_blocks+blocks)) > 0.95
order by 1;
查詢出的表需要手工分配了。（如果表沒有經過analyze,則block和empty_blocks為空值）

SQL>alter　table　app1.sales　allocate　extent;
手工擴充套件範圍。
5.3.1.2 範圍大小的確定與效能
越大的範圍比越小的範圍提供更好一點的效能，因擴充套件的次數就少了；而且可以在初始範圍頭部由一個統一的塊-----範圍地圖（extent map）中找出所以範圍的位置。如果
db_file_multiblock_read_count的設定正確，讀取範圍的I/O次數就會減少。
　　大範圍的缺點：空間可能會浪費，及在需要一個範圍時沒有足夠大的連續Oracle塊可使用。

１） 有隨機存取特性的OLTP，小塊更好。
２） 小塊減少塊爭用，因每塊只含有教少的行。但增加database buffer cache開銷，必須訪問更多的塊。
３） 小塊對小行的排序更好。
４） 大塊用在DSS比較好。大塊增加塊爭用，並要求較大的database buffer cache大小。

5.3.1.3 行連線和行遷移
行連線和行遷移由塊大小所引起。

行連線：插入的行超出Oracle塊大小，該行將儲存在兩個或兩個以上的塊中，這種橫跨多塊叫行連線。
對效能的影響是：　操作一行需要讀取更多的塊。
唯一處理辦法：增加塊大小或減少插入的大小。

行遷移：只由update操作引起。如果更新的行大小超過pctfree指定的可用範圍，Oracle會把該行完全遷移放到一個新塊上，而在原來的塊上保留一個指向新塊的指標。
對效能的影響：操作一行，至少需要讀兩個塊。

 確定行連線或行遷移l
檢查的兩種辦法：
１） dba_tables中的chain_cnt列。
必須先執行analyze分析表該欄位才有資料，而且不區分行連線或是行遷移。
SQL>select owner,table_name,chain_cnt from dba_tables where chain_cnt > 0;

２） v$sysstat中table fetch continued row的出現。
SQL>select * from v$sysstat where name = 'table fetch continued row';

由於行遷移只在update時出現，所以可以通過簡單的刪除、插入來糾正（糾正後剩下的肯定就只是行連線了）：
１） export，delete或truncate，然後重新import該表。
２） 使用alter table .. move命令重建該表。
３） 找出並重新插入遷移行。例：
分析表，得出是否存在行連線或行遷移
SQL>analyze table xxxx compute statistics;
SQL>select table_name,chain_cnt from dba_tables where table_name = ‘xxxx’;
執行$ORACLE_HOME/rdbms/admin/ tulchain.sql檔案建立chained_rows表。
使用list chained rows重新分析表。
SQL>analyze table xxxx list chained rows;
可在chained_rows中查詢出有行連線或行遷移的行頭等資訊。現在把這部分行備份出來。
備份行
SQL>create table temp_t as select * from xxxx
Where rowed in (select head_rowid from chained_rows);
刪除行
SQL>delete from xxxx
where rowed in (select head_rowid from chained_rows);
恢復行
SQL>insert into xxxx select * from temp_t;
行遷移處理完成，此時再使用analyze table xxxx compute statistics;分析表後檢視select table_name,chain_cnt from dba_tables where table_name = ‘xxxx’;的chain_cnt的數，都將是行連線的數目。

5.3.1.4 高水位（High Water Mark，HWM）與效能
HWM：建立段的時候分配範圍，此時Oracle跟蹤已用來儲存段資料的最高塊的ID，該ID就叫HWM。記錄在segment header block中，在segment建立的時候設定在segment的起始位置，當記錄被插入的時候以5個block的增量增加，truncate可以重設high water mark的位置，但delete不能。

效能影響：不管中間是否有空塊，每次全表掃描都會把HWM以上所有塊掃描一次（很多空塊可能就這樣被掃描，讀取了比實際資料更多的塊）。

HWM的更改只有：
１） exp,drop或truncate，然後重新匯入該表。
２） 使用alter table .. move重建該表（此時需要重建索引）。
有一種情況：一個表被插入很大很大，此時HWM被擴充套件的很高，然後刪除了這個表大量的資料，由於此時HWM不會改變，所以如果該表沒有資料再被插入，則刪除後實際有資料的塊到HWM之間的空間將被浪費掉。此時只有執行analyze 命令分析該表後，使用alter table xxxx deallocate unused;命令來把他們釋放給表空間。

使用analyze分析表後，可以使用dba_tables的empty_block來確定HWM塊數量，使用
alter table tab_name deallocate unused;命令來釋放給表空間。

（也可使用DBMS_SPACE.UNUSED_SPACE來確定）。分析後，dba_tables的empty_blocks列只顯示HWM以上有多少塊，並不表示這些塊是否有資料,blocks顯示HWM以下的塊，他們加在一起就是一共分給段的範圍了。

如果非要估計一個表的空塊，則使用：
SQL> select blocks totol_blocks,round((t.avg_row_len * t.num_rows)/s.block_size,0) needed_blocks,
blocks - round((t.avg_row_len*t.num_rows)/s.block_size,0) free_blocks
from dba_tables t,dba_tablespaces s
Where t.tablespace_name = s.tablespace_name
And t.table_name = 'PERF_TCH_QJ'
And t.owner = 'PERF';

5.4 排序操作和臨時段優化
5.4.1 排序
優化的最大目標是最大限度的減少排序操作，如不可避免，則儘可能在記憶體中進行排序。（排序只在記憶體或磁碟（temp表空間）內進行）

導致排序的SQL：
1) ORDER BY
2) GROUP BY
3) SELECT DISTINCT
4) UNION
5) INTERSECT
6) MINUS
7) ANALYZE
8) CREATE INDEX
9) 表間非索引上的連線

每個使用者server process，都會被分配一個指定大小的記憶體用來排序，如果要排序的內容小於該記憶體，則在記憶體排序；如果大於該記憶體，則內容會

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-374867/，如需轉載，請註明出處，否則將追究法律責任。