【轉】Oracle資料庫優化之資料庫磁碟I/O

【IT168 技術文件】

　　概述

　　影響資料庫效能的因素包括：系統、資料庫、網路。

　　資料庫的優化包括：優化資料庫磁碟I/O、優化回滾段、優化Rrdo日誌、優化系統全域性區、優化資料庫物件。

　　監控資料庫的效能

　 　在init.ora引數檔案中設定TIMED_STATISTICS=TRUE 和在你的會話層設定ALTER SESSION SET STATISTICS=TRUE 。執行svrmgrl 用 connect internal 註冊，在你的應用系統正常活動期間，執行utlbstat.sql 開始統計系統活動，達到一定的時間後，執行utlestat.sql 停止統計。統計結果將產生在report.txt 檔案中。(utlbstat.sql utlestat.sql 一般存放在$ORACLE_HOME/RDBMS/ADMIN 子目錄下)

　　優化資料庫磁碟I/O

　　檢查系統的I/O問題

　　在UNIX系統中工具sar-d能檢查整個系統的iostat(IO statistics)，在NT系統上則使用效能監視器(Performance Monitor).

　　反映oracle檔案I/O的程式

　　

　　使用V$FILESTAT確定oracle資料檔案I/O

　　SELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE DF.FILE#=FS.FILE# ;

　　使用分佈I/O減少磁碟競爭

　　 將資料檔案和redo log檔案分開

　　 Striping 表資料

　　 分開表和索引

　　 減少與oracle無關的磁碟I/O

　　避免動態空間管理

　　在建立如表或回滾段的資料庫實體時，在資料庫中會為這些資料分配空間，該空間被稱為段。如果資料庫操作引起資料增加並超出了分配的表空間，oracle會擴充套件該段，動態擴充套件會降低系統效能。

　　 確定動態擴充套件

　　select name,value from v$sysstat where name=’recursive calls’ ;

　　 分配分割槽

　　確定實體的最大大小;

　　選擇儲存引數值，使oracle分配足夠大的分割槽，在建立實體時可以裝入所有資料

　　 避免回滾段的動態空間管理

　　回滾段大小由其儲存引數所決定，回滾段必須能儲存所有交易的回滾入口;

　　使用set transaction 命令可以為回滾段賦予交易的合適的大小;

　　對長的查詢的修改資料，應賦予大的回滾段，以保持所有的回滾入口;

　　對OLTP交易，由於頻繁交易，每個交易只修改小量的資料，因此賦予小的回滾段。

　　 減少遷移和連結行

　　1. 使用ANALYZE 收集遷移和連結行的資訊;

　　2. 查詢輸出表：chained_rows;

　　3. 如果有許多遷移和連結行，就需要消除遷移行，方法如下：

　　A. 建立與原表相同列的中間表，以儲存遷移和連結行;

　　B. 從原表中刪除遷移和連結行;

　　C. 將中間表中的行插入到原表中;

　　D. 刪除中間表

　　4. 刪除第一步收集的資訊;

　　5. 重新使用ANALYZE命令查詢輸出表

　　6. 在輸出表中出現的行都是連結行，只能通過增加資料塊的大小來清除。

調整排序

　　記憶體中排序

　　使用動態表V$SYSSTAT的資訊反映排序

　　SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN (‘SORTS(MEMORY)’,’SORTS(DISK)’) ;

　　SORTS(MEMORY)-不需要使用I/O操作而完全在記憶體完成的排序數;

　　SORTS(DISK)-需要使用I/O操作與磁碟臨時段才能完成資料的排序數目。

　　增大SORT_AREA_SIZE以避免磁碟排序

　　使用NOSORT建立非排序的索引

　　CREATE INDEX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;

　　調整Checkpoints

　　一個checkpoint是oracle自動執行的一種操作，當檢查點操作時，資料庫中的所有緩衝區會寫回磁碟，所有資料庫的控制檔案被更新。Checkpoint頻繁發生會加快資料庫的恢復，但是增加了I/O次數，會降低系統的效能。

　　調整LGWR和DBWn I/O

　　調整LGWR I/O

　 　每次I/O寫的大小依賴於LOG緩衝區的大小，該大小由LOG BUFFER 所設定，緩衝區太大會延遲寫操作，太小可能導致頻繁的小的I/O操作。如果I/O操作的平均大小很大，那麼LOG檔案就會成為瓶頸，可以使用STRIPE REDO LOG檔案避免這個問題。

　　調整DBWN I/O

　　使用初始引數DB_WRITER_PROCESSES，可以建立多個資料庫寫程式。

　　調整競爭

　　由多個程式同時請求使用相同的資源時，就產生了競爭

　　確定競爭問題

　　檢視V$RESOURCE_LIMIT提供了一些系統資源的使用限制。

　　如果系統存在無反應的現象，檢查V$SYSTEM_EVENT，檢查最大平均等待時間的事件;

　　如果存在過量的緩衝區等待，檢查V$WAITSTAT，確定哪個型別的塊有最多的等待次數和最長的等待時間，再查詢V$SESSION_WAIT得到每個緩衝區的等待時間。

　　減少回滾段的競爭

　　通過檢查V$WAITSTAT可以確定回滾段的競爭：

　　SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS IN (‘SYSTEM UODO HEADER’,’SYSTEM UODO BLOCK’,’UODO HEADER’,’UODO BLOCK’) ;

　減少排程程式的競爭

　　檢查排程程式的busy率

　　SELECT NETWORK”PROTOCOL”, SUM(BUSY)/(SUM(BUSY)+SUM(IDLE)) “TOTAL BUSY RATE” FROM V$DISPATCHER GROUP BY NETWORK ;

　　如果指定協議的排程程式忙的時間超過50%的有效工作時間，那麼，增加排程程式可以提高使用該協議連線到oracle的效能。

　　檢查排程程式相應佇列的等待時間

　 　SELECT NETWORK “PROTOCOL” DECODE(SUM(TOTALQ),0.’NO RESPONSES’,SUM(WAIT)/SUM(TOTALQ)||’HUNDREDTHS OF SECONDS’) “AVERAGE WAIT TIME PER RESPONSE” FROM V$QUEUE Q,V$DISPATCHER D WHERE Q.TYPE=’DISPATCHER’ AND Q.PADDR=D.PADDR GROUP BY NETWORK ;

　　增加排程程式：使用MTS_DISPATCHERS引數和ALTER_SYSTEM命令可以增加排程程式

　　減少共享伺服器程式的競爭

　　共享伺服器程式競爭可以由不斷增加的請求等待時間所反映，使用如下查詢：

　 　select decode(totalq,0,’No Requests’, wait/totalq||’hundredths of seconds’) “Average Wait Time Per Requests” from v$queue where type=’COMMON’ ;

　　使用如下查詢可以得到當前執行的共享服務程式數：

　　select count(*) “Shared Server Processes” from V$shared_servers where status!=’QUIT’;

　　oracle能自動增加共享服務程式，但是MTS_MAX_SERVERS的值可以更改。

　　減少redo log緩衝區latches競爭

　　在LGWR程式將redo入口從redo log緩衝區寫入redo log檔案後，該入口就會被新入口覆蓋，供其他log的使用。

　　V$SYSSTAT中redo buffer allocation retries 反映使用者程式等待redo log空間的次數：

　　Select name,value from v$sysstat where name=’redo buffer allocation retries’ ;

　　redo buffer allocation retries的值應該接近0，如果該值持續增加，那麼，說明程式需要等待緩衝區的空間。增大引數LOG_BUFFER的值可以增大redo log的大小。

　　確定redo log緩衝區latches競爭

　　redo分配latch;

　　redo複製latches。

　 　一次只能有一個使用者分配緩衝區中的空間，在分配了redo入口的空間後，使用者程式將入口複製到緩衝區，其最大大小是由 LOG_SMALL_ENTRY_MAX_SIZE指定。Redo複製latches的數目由引數LOG_SIMULTANEOUS_COPIES指定。

　　檢查redo log活動

　　對redo log緩衝區的頻繁訪問可能導致redo log緩衝區latches競爭，降低系統效能。Oracle在動態表V$LATCH中收集了所有LATCH的統計資訊。

　　其中：表v$latch反映willing-to-wait 請求的列

　　gets-成功的willing-to-wait請求數;

　　misses-初始不成功的willing-to-wait請求數;

　　sleeps-請求不成功的等待時間;

　　表v$latch反映immediate請求的列：

　　immediate gets-成功的immediate請求數

　　immediate misses-不成功的immediate請求數

　　使用如下查詢：

　 　select ln.name,gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchname ln where ln.name in (‘redo allocation ’,’redo copy’) and ln.latch#=l.latch# ;

　　可以計算出各類請求的等待率。

　　減少latch競爭

　 　要減少redo allocation latch競爭，必須減少單個程式佔用latch的時間。要減少這個時間，可以減少該redo allocation latch的複製。減少LOG_SMALL_ENTRY_MAX_SIZE初始引數可以減少在redo allocation latch的redo入口的複製次數和大小。

　　減少redo copy Latches競爭可以用增加LOG_SIMULTANEOUS_COPIES的值來增加LATCH數，最多可以達到CPU的兩倍。

　　減少Free List競爭

　　確定Free List競爭，可以使用以下幾步：

　　1. 檢查V$WAITSTAT，確定DATA BLOCKS的競爭;

　　2. 檢查V$SYSTEM_EVENT,確定BUFFER BUSY WAITS，如果數值高，表明存在競爭;

　　3. 在這種情況下，檢查V$SESSION_WAIT查詢每個緩衝區的忙等待、FILE、BLOCK及ID;

　　4. 使用如下查詢得到實體和FREE LIST的名稱：

　　SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID=file AND BLOCK BETWEEN block_id AND block_id+blocks ;

　　5. 使用如下查詢找到FREE LIST:

　　SELECT SEGMENT_NAME,FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=segment AND SEGMENT_TYPE=type ;

　　增加Free List

　　要減少表的Free list的競爭， 可以使用freelists引數重新建立表，方法有：

　　1. 刪除舊錶，重新建立表;

　　2. 通過從舊錶選擇資料插入到新表，刪除舊錶，重新命名，完成表的重新建立;

　　3. 使用Export 和Import，export舊錶，刪除舊錶，import該表。

　建立和優化資料庫檔案的方針：

　　為表和索引建立不同的表空間。

　　將表和索引的表空間放在不同的磁碟上。

　　將REDO日誌和回滾段放在不同的磁碟上。

　　將Oracle可執行檔案和資料庫檔案放在單獨的磁碟上。

　　確定最常用的表、索引，以及他們的表空間，並放在單獨的磁碟上。

　　不要在Oracle資料磁碟上安裝其他第三方軟體。

　　監控磁碟I/O的方法：

　　用以下查詢語句可以得到各表空間讀寫次數，phyrds+phywrts 即是磁碟I/O量。應按前面講的方針調整資料檔案的分佈方式。

　　select name,phyrds,phywrts from v$datafile,v$filestat where v$datafile.file# = v$filestat.file#

　　結果如下：

　　NAME PHYRDS PHYWRTS

　　-------------------------------------------------- ---------- ----------

　　/u/oracle/OraHome/oradata/pb/system01.dbf 2511 8

　　/u/oracle/OraHome/oradata/pb/tools01.dbf 5 2

　　/u/oracle/OraHome/oradata/pb/rbs01.dbf 18 20

　　/u/oracle/OraHome/oradata/pb/temp01.dbf 4 2

　　/u/oracle/OraHome/oradata/pb/users01.dbf 45 5

　　/u/oracle/OraHome/oradata/pb/indx01.dbf 4 2

　　/u/oracle/OraHome/oradata/pb/drsys01.dbf 4 2

　　7 rows selected.