老白對oracle效能的io調優－－（摘自老白-一個金牌DBA的故事）

關於io調優
   在海量資料的情況下，資料庫的效能問題有80%以上和IO有關，因此I/O最佳化是貫穿海量資料庫管理全過程的重要工作。
I/O最佳化牽涉的面比較廣，現在就從Oracle 資料庫最佳化的一些主要方面詳細闡述一下。在海量資料庫環境下，Oracle資料庫最佳化面臨的最重要的任務是I/O最佳化，因為絕大多數大型資料庫都或多或少存在I/O效能問題。
資料庫的I/O效能涉及面十分廣，因此I/O效能最佳化也是ORACLE資料庫最佳化工作中最複雜的工作。進行I/O效能最佳化，基本的步驟是這樣的：
1、採集系統資料，定位I/O效能問題；
2、分析並制訂解決方案；
3、調整系統，解決問題；
4、評估最佳化結果。
  最佳化ORACLE資料庫的I/O效能，不能簡單地從I/O入手，需要遵循資料庫最佳化工作的基本原則。首先資料庫最佳化的目的是提高系統的整體處理能力，提高事務響應速度。所有的最佳化工作都需要圍繞這個目的來進行。資料庫效能最佳化的手段有兩個方面，一是減少處理時間，二是減少等待事件。減少處理時間要求應用編寫得高效合理。減少等待時間要求系統的各種資源合理分配，不出現任何瓶頸。

資料庫使用的系統資源包括CPU、記憶體、儲存和網路。資料庫最佳化的目的是合理分配這些資源，確保任何一種資源都不出現瓶頸。

  根據上述原則，Oracle資料庫的I/O效能最佳化不能只透過重新組合系統資源來達到提升資料庫總體效能（包括I/O效能）的目的。
另一個方面，在最佳化I/O時也要考慮到其他資源的情況。

  如果I/O單方面提升導致其他資源出現瓶頸，那麼也會導致系統總體效能下降。特別是針對資源有限的系統的最佳化，如何有效利用不足的系統資源進行最最佳化的組合，在保證沒有一種資源過載的情況下儘可能利用資源，是系統最佳化的關鍵。
  如何確定系統的主要問題是I/O問題，並進一步定位I/O問題的根本原因是解決Oracle I/O效能問題的關鍵。I/O效能不佳，可能是多方面的問題。進行最佳化的第一步就是確定關鍵的效能瓶頸。

   影響ORACLE資料庫I/O效能的問題覆蓋面十分廣，根據作者多年從事Oracle資料庫管理的經驗，下面幾個方面是影像資料庫I/O效能的主要問題。
  儲存效能瓶頸：控制器不足、cache偏小，Cache設定不合理、I/O通道容量不足等。
  磁碟效能瓶頸：磁碟數量過少、使用了速度比較低的磁碟等
  使用了不合理的RAID模式。
  在使用RAID的情況下，存在I/O熱點，多個熱點檔案使用同一個磁碟。
  非同步I/O配置不正確
  資料庫各種緩衝區設定不合理，緩衝命中率過低
  PGA的各種快取設定過小，（對於Oracle 9i，在使用自動管理模式的情況下，PGA設定過小），導致大量的臨時表空間操作
  重做日誌存在效能瓶頸
  重做緩衝區設定不合理
  存在熱點資料
  表空間碎片嚴重
  表和索引的儲存引數不合理
  行遷移比較嚴重
  存在大量大表掃描的SQL
  SQL執行選擇了不好的執行計劃

  當系統出現I/O問題時，資料庫管理員最大的挑戰是如何儘快找到問題的最根本原因。I/O問題的調整是十分複雜的，在沒有找到根本原因之前進行調整往往無法達到最終的最佳化目標。需要注意的是I/O問題往往和大型的SQL語句有關。如果某個系統突然發生I/O效能問題，第一步需要排除一切I/O之外的問題。

  確定I/O效能瓶頸的存在，並定位存在I/O效能瓶頸的裝置是解決I/O效能問題的第一步。使用作業系統的監控工具可以實時監控I/O的情況。第一種方法是使用vmstat工具。使用該工具，可以檢視b列的值，如果該值比較高，那麼說明等待I/O的程式比較多，I/O可能存在問題：

$vmstat 1  10
     procs             memory
r    b    w          avm    free
2    12   0     14572705   92752
2    12   0     14572705   93548
2    12   0     14572705   92910
2    12   0     14572705   93467
2    12   0     14572705   93546
2    12   0     14572705   93864
2    12   0     14572705   94557
2    12   0     14572705   93952
2    12   0     14572705   94017
2    12   0     14572705   93047

如果上述命令發現b比較大，那麼說明可能存在I/O等待的現象，透過sar命令或者iostat命令可以進一步確認。如果用sar 命令監控時發現wio 比較大（比如超過40，這個值是經驗值，根據不同的系統，這個值可以調整）,那麼基本可以確定存在I/O效能瓶頸，如下所示

$sar 1  10
HP-UX cuyn16  B.11.11 U 9000/800
15:01:44      %usr       %sys        %wio        %idle
15:01:45        16          3          57           24
15:01:45        15          2          59           19
15:01:45        21          3          57           16
15:01:45        20          2          63           14
15:01:45        17          2          67           12
15:01:45        12          1          75           7
15:01:45        16          2          75           5
15:01:45        10          1          84           1
15:01:45        18          2          79           6

如果發現wio值長時間高於40，那麼說明I/O等待比較嚴重。此時可以透過sar -d 命令來監控，看看哪些I/O裝置存在效能問題。如果發現某個裝置的繁忙度長時間超過90%，就說明該裝置比較繁忙。如果該裝置的avserve 比較大或者比其他裝置高很多，就說明該裝置存在效能問題。比如下面的例子:

15:02:01      device     %busy     avque     r+w/s      blks/s     avwait     avserv
Average      c0t0d0        2.00     0.50         6          27      3.62        6.03
Average      c3t8d0        1.10     0.50         4          16      3.23        5.69
Average     c55t0d5       99.40     0.50        18          73      5.41       54.50
Average     c55t1d0        4.20     0.50         5          15      5.39        8.49
Average     c55t1d1       79.52     0.76        24         810      9.09       81.99
Average    c55t11d0       68.33     0.52        23        2909      5.60       72.40
Average    c55t11d2       31.07     1.14        25        1630     10.95       28.05
Average    c55t11d3       16.98     0.51        22        3075      5.24       13.39
Average    c55t11d5       71.83     2.59        26        1643     42.18       82.78
Average    c55t11d6       76.12     0.50        23        3012      5.58       76.47
Average    c55t12d0       30.57     1.02        26        1637     10.86       30.59
Average    c55t12d1       21.48     0.50        20        2826      5.12       19.55
Average    c55t12d3       80.72     2.74        29        1880     42.78       84.38
Average    c55t12d4       70.03     0.52        23        2887      5.83       66.85
Average    c55t14d1      100.00    54.57       104        6630   1315.98       71.54
Average    c55t13d1       77.72     0.55        19         297      5.79       80.19

從上面的資料可以看出，部分裝置(比如C55t14d1)的等待事件比較長，並且avwait+avserv的時間比較長，說明該裝置存在效能瓶頸。而大部分HDISK的avwait+avserv還比較正常，這說明儲存系統並不存在普遍性的效能瓶頸，而效能瓶頸主要集中在熱點盤組上。

透過Oracle的STACSPACK工具也可以檢查系統的I/O問題。如果系統的效能不佳，並且可從報告中看到的sequential read等待事件是系統等待事件中排在前幾位的事件，佔系統總等待時間的比例比較高，那麼系統很可能存在I/O效能問題。可以透過檢查檔案I/O情況來進一步確認並找出存在效能問題的裝置。方法是透過檢查檔案I/O的平均讀響應時間。如果某個檔案的平均讀響應時間超過20ms，那麼說明該檔案所屬的檔案系統可能存在效能問題。應該注意的是，20ms是一個相對引數，在不同的應用環境下該值可能會有所不同。透過比對作業系統的情況，資料庫管理員應該很快就能確定你所管理的系統的平均讀響應時間和作業系統I/O情況的對應關係。

檢查讀平均響應時間時還要注意幾個問題。第一個問題是在報告時間區域內的I/O量，如果某個檔案在報告時間區間內的I/O數量很小，那麼此平均響應時間可能缺乏代表性，可以透過檢查存放在相同裝置上的其他檔案來進一步確認。另外一種情況是平均每次讀的資料量比較多（每次讀的塊數比較多),那麼略高的平均讀響應時間也是正常的。下面的資料就是從I/O存在問題的資料庫上取下的STATSPACK資料:
Top 5  Timed Events
------------------------
Event                                          waits               Time（s)        %Total Ela Time
--------------------------------------------------------------------------------------------------
db file sequential read                        661,802           45,404                    60.79
SQL*Net more data from dblink                 3180,371            7,894                    10.57
CPU time                                                          5,077                     6.80
db file scattered read                          56,959            3,846                     5.15
buffer busy  waits                              42,376            2,541                     3.40

可以看出，db file sequential read 是等待事件中的第一位事件，佔整個等待事件的60%以上，並且每次平均等待的時間為69ms.這是一個典型的I/O存在效能瓶頸的例子，透過STATSPACK 報告檔案I/O效能分析資料，可以進一步檢查到底哪些檔案出現了問題:
INDEX_SPACE_OTHER                  /dev/vg10xp/rls_2g_vol05
         9,171            2        52.2        1.0    7,911
                                   /dev/vg10xp/rls_2g_vol106
         8,016            2        22.8        1.0    8,292
                                   /dev/vg10xp/rls_2g_vol107
         7,567            2         9.8        1.0    8,058
                                   /dev/vg10xp/rls_2g_vol108
         5,456            1        46.7        1.0    6.180
                                   /dev/vg10xp/rls_2g_vol109
         5,925            2        57.3        1.0    6,265
                                   /dev/vg10xp/rls_2g_vol110
        10,462            3       147.7        1.0    6,867
                                   /dev/vg10xp/rls_2g_vol111
         6,071            2       130.0        1.0    5,638
                                   /dev/vg10xp/rls_2g_vol112
        15,624            4       226.9        1.0   15,736
                                   /dev/vg10xp/rls_2g_vol112
        14,421            4       206.2        1.0   17.136
                                   /dev/vg10xp/rls_2g_vol112
        13,677            4       229.9        1.0   11.828
    ...

   可以看出/dev/vg10xp 上部分檔案的平均讀相應時間超過了200ms，存在嚴重的效能問題。透過驗證，/dev/vg10xp是c55t14d1上的邏輯卷。

STATSPACK 報告之I/O問題分析

I/O效能分析是STATSPACK 分析中的重要部分。對於I/O的分析可以基於兩個方面，第一個方面是在Wait Events for DB中，比如下面的資料
                                                                              Avg
                                                            Total wait        wait            waits
Events                            waits      Timeouts          time（s)        (ms)              /txn
db file sequential             13,409,809           0           424,347         29              93.6
SQL*NET more data to client     1,187,633           0             8,024          7               7.7
buffer busy waits                 212,482           0             5,888         28               1.4
db file scatter read              143,778           0             3,154         22               0.9
從上面看，db file sequential read(29ms) 和 db file scatter read(22ms) 的等待時間都很高，說明I/O存在明顯的效能
問題。對於不同的系統，這些值的正常範圍都不大相同，可以透過長期的積累，形成基線資料。不過，一般來說超過10ms就應該關注了，超過20ms，I/O子系統就可能存在問題了。從本例來說，系統的I/O效能存在一定的問題。為了確定猜測，可以進一步檢查檔案I/O詳細資訊:

File IO Stats DB:HSCP Instance :hcsp Snaps :21 - 33
->ordered by Tablespace,File

Tablespace                         Filename
-----------------------   -----------------------------------------------------------------------
                     Av          Av      Av                           Av              BUFFER     Av  Buf
     Reads        Reads/s     Rd(ms)   Blks/Rd           Writes   Writes/s              Waits      Wt(ms)
-------------    -------     ------   -------    -------------   --------       ------------   ---------
   HAIER_TEST_DATA              /dev/vgrac/rhaier_test_data02
      132            0        163.6     2.5               65        0                   0
   HCSP_AI_DATA                 /dev/vgrac/rHCSP_AI_DATA_01
        1,363            0         19.1     1.0            1,349        0                  0
   HCSP_AI_INDEX                /dev/vgrac/rHCSP_AI_INDEX_01
        4,649            1         17.5     1.0            3.614        0                  2          0.0
   HCSP_BASE_DATA               /dev/vgrac/rhcsp_base_data01
      329,857           38         14.3     2.8          77,415         9              33,510         11.2
   HCSP_BASE_INDX               /dev/vgrac/rhscp_base_index01          
       72,577            8         14.7     1.0             419         0                 111          3.2
   HCSP_COMM_DATA               /dev/vgrac/rhcsp_comm_data01
       7,789             1        112.1     2.7             692         0               3,884          87.5

  從上面的資料可以看出，大多數的檔案訪問的平均讀響應時間都超過20ms.基本上我們可以得出結論，系統的I/O存在問題。