orcle效能調整(轉)

資料庫的等待事件，發現前幾名是：

log file parallel write

db file scattered read

log file sync

db file sequential read

SQL*Net more data to client

發現前面的4項都是影響到資料庫效能的問題：

log file sync：

這個等待時間是指等待oracle的前臺的commit和rollback操作程式完成，有時候這個等待時間也會包括等待LGWR程式把一個會話事務的日誌記錄資訊從日誌緩衝區中寫到磁碟上的重做日誌檔案中。因此當前臺程式在等待這個事件的時候，LGWR程式同時也在等待事件log file parallel write。

理解什麼造成這個等待事件的關鍵在於：對比這個等待事件和log file parallel write等待事件的平均等待時間：

l 如果他們的等待時間差不多，那麼就是重做日誌檔案的I/O引起了這個等待事件，則需要調整重做日誌檔案的I/O，

l 如果log file parallel write等待事件的平均等待時間明顯小於log file sync等待事件的等待時間，那麼就是一些其他寫日誌的機制在commit和rollback操作時引起的等待，而不是I/O引起的等待。例如重做日誌檔案的latch競爭，會伴隨出現latch free或者LGWR wait for redo copy等待事件

在V$SESSION_WAIT中，這個等待事件有3個引數：

P1	代表在日誌緩衝區中需要被寫入到重做日誌檔案中的快取數量，寫入的同時會確認事務是否已經被提交，並且保留提交資訊到例項意外中斷前，因此必須等待LGWR將P1數量的快取寫入重做日誌檔案為止。
P2	無用
P3	無用

如果這個等待事件在整個等待事件中佔了比較大的比重，可以從3個方面來進行調整

1. 調整LGWR程式時期具有更好的磁碟I/O吞吐量，例如不要將日誌檔案放在RAID5的磁碟上

2. 如果存在很多執行時間很短的事務，可以考慮將這些事務合併成一個批量事務以減少提交的次數，因為每次提交都需要確認相關的日誌寫入重做日誌檔案，因此使用批量事務來減少提交的次數是一種非常行之有效的減少I/O的方法

3. 產看是否有一些操作可以安全的使用NOLOGGING或者UNRECOVERABLE選項，這樣可以減少日誌檔案的產生

Log file parallel write

這個等待事件出現在黨LGWR後臺程式從日誌緩衝區寫日誌資訊到磁碟上的重做日誌檔案的時候。只有啟用了非同步I/O的時候，LGWR程式才會並行寫當前日誌組內的充作日誌檔案，否則LGWR指揮迴圈順序逐個的寫當前日誌組重做日誌檔案。LGWR程式不得不等待當前日誌組所有的重做日誌檔案成員全部寫完，因此，決定這個等待事件的等待時間長短的主要因素是重做日誌檔案所在磁碟的I/O讀寫速度。

如果是當前LGWR程式寫的速度不夠快導致這個等待事件，可以通過檢視一些和重做日誌相關的統計值來判定當前的LGWR程式是否效率低下，具體的可以看 redo writes, redo blocks written, redo write time, rdo wastage, redo size等統計值，這些都是和LGWR程式效能直接相關的一些統計值。

在V$SESSION_WAIT中，這個等待事件的3個引數：

P1	代表正在被寫入的重做日誌檔案組中的重做日誌檔案號
P2	代表需要寫入重做日誌組中每個重做日誌檔案的重做日誌block數量
P3	代表I/O請求次數，需要被寫入的block會被分成多次分別請求

如果這個等待事件佔用比較多的時間，可以做如下調整

1． 採用UNRECOVERABLE/NOLOGGING操作儘量減少重做日誌的產生

2． 在保證不會同時對市重做日誌檔案的前提下，儘量減少重做日誌組中的成員個數，減少每次寫重做日誌檔案的時間

3． 除非在備份情況下，否則不要在江表空間置於熱備份的模式下，因為在表空間處於熱備的模式下會產生更多的重做日誌檔案

4． 對於使用LogMiner、Logical Standby或者Streams，在能夠滿足要求功能的前提下，儘量使用最低階別的追加日誌以減少重做日誌的產生

5． 儘量將同一個日誌組內的重做日誌檔案分散到不同的硬碟上，減少並行寫重做日誌檔案時產生的I/O競爭

6． 不要將重做日誌檔案置於RAID5的磁碟上，最好放在裸裝置上。

7． 如果設定了歸檔模式，不要將歸檔日誌的目的地設定為存放重做日誌的磁碟上，避免引起I/O競爭

關於Log的這2個問題的總結

通過上述對Log這2個問題的描述，以及產生的原因，除了Log file sync可能有其他方面的因素引起的（Latch），主要還是磁碟和使用習慣

1． 磁碟由於這些都是寫磁碟所引起的，所以只有從減少寫磁碟（指資料庫本身的角度，和下列提到的使用者操作習慣不一樣）和加快寫磁碟來減少這些等待時間

a) 儘量不要在RAID5的磁碟上儲存重做日誌檔案，RAID5寫的速度屬於比較慢的

b) 在安全性保證的基礎上，減少重做日誌組成員的個數

c) 同一個日誌組中的不同成員放在不同的磁碟上，加速寫的速度。

d) 對可以採用NOLOGGING/UNRECOVERABLE的操作，使用這些選項減少log的產生

e) 有歸檔的，不要將歸檔的和線上重做日誌放在一個磁碟上

2． 使用習慣如果使用者不斷的進行commit或者rollback，這樣必定引起一次log日誌的寫操作。因此可以通過一些統計資訊判斷是否每次的日誌的寫運算元據量很小，這樣通過調節使用者的操作，將大量的資料更新合併到一個事務中來，這樣增加每次日誌的操作量，減少對日誌的不斷呼叫，提高LGWR的寫的效率。

db file scattered read

這是一個非常常見的等待時間。當oracle從磁碟上讀取多個block到不連續的快取記憶體區的快取中就會發生這個等待事件，Oracle一次最多能夠讀入的block數量由初始化引數DB_FILE_MULTIBLOCK_READ_COUND決定，這個時間一般伴隨著全表掃描或者Fast Full Index 掃描一起出現。

在V$SESSION_WAIT中，這個等待事件的幾個引數：

P1	代表oracle的檔案號
P2	代表從這個檔案中開始讀取的block號
P3	代表從這個block開始需要讀取的block數量

一般從這個3個引數，就可以回頭查詢到是在讀取資料庫的哪個物件，然後分析對這個物件的操作來進行優化Sql語句。

如果這個等待事件佔的比重比較厲害，可以通過以下方法來調整

方法一

找出執行全表掃描或者Fast Full index掃描的Sql語句，判斷這些掃描是否是必要的，是否導致了比較差的執行計劃，進行調整。

從oracle9i開始，提供了一個檢視V$SQL_PLAN，可以通過它幫助我們找到那些全表掃描或者Fast Full Index掃描的Sql語句：

查詢全表掃描的SQL語句

Select sql_text from v$sqltext t, v$sql_plan p Where t.hash_value=p.hash_value And p.operation=’TABLE ACCESS’ And p.option=’FULL’ Order by p.hash-value, t.piece;

查詢Fast Full index 掃描的Sql語句可以這樣；

Select sql_text from v$sqltext t, v$sql_plan p Where t.hash_value=p.hash_value And p.operation=’INDEX’ And p.option=’FULL SCAN’ Order by p.hash-value, t.piece;

如果是Oracle8i的資料庫，可以通過v$session_event檢視中找到關於這個等待事件的程式sid，然後根據這個sid來跟蹤相應會話的SQL

Select sid, event from v$session_event Where event=’db file sequential read’

或者可以通過檢視物理讀取最多的SQL語句的執行計劃，看是否裡面包含了全表掃描和Fast Full Index掃描，可以通過以下語句獲取物理讀取最多的SQL語句

Select sql_text from Select *from v$sqlarea Order by disk_reads) Where rownum<10

方法二：

有時候執行計劃很好也會出現多block掃描的情況，這個時候可以通過調整Oracle資料庫的多block的I/O，來設定一個合理的DB_FILE_MULTIBLOCK_READ_COUNT,使得儘量滿足；

Db_block_size * DB_FILE_MULTIBLOCK_READ_COUNT = max io size of system

這個引數也不是設定的越大越好，設定這個引數之前需要了解一下應用的型別，如果是OLTP型別的，一般來說全表掃描較少，這個時候如果設定了比較大反而會降低資料庫的效能，因為CBO在某些情況下會因為多block讀取導致COST比較低從而錯誤的選用了全表掃描。

其他方法

還可以採用對錶和索引使用分割槽、將快取區的LRU末端的全表掃描和FastFullIndex掃描的block放入到Keep快取池等方法來進行調節。

db file sequential read

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