1 Oracle中IO的產生
IO當然包括了讀、寫兩部分，先介紹Oracle中寫操作的產生。
1.1 寫
介紹寫操作之前，先簡單的看下Oracle的物理結構：Oracle的物理文件包括以下三種文件：控制文件（Control Files）、重做日志文件（Redo Log Files）、數據文件（datafiles）。而數據文件中，根據功能的不同，還可以分為系統數據文件、臨時空間文件、回滾段文件和用戶數據文件。另外，如果數據庫的Archive Log模式被激活，還存在歸檔日志文件。Oracle的IO產生，就是對這些文件的數據讀、寫操作。下面再詳細看下幾種主要寫操作的產生及其過程。
1.1.1 控制文件
控制文件中記錄了整個數據庫的物理結構信息，如數據庫名字、數據文件及日志文件名字和位置、事件戳信息等等。任何數據庫的結構變化（如果創建新的數據文件）都會引起Oracle修改控制文件。同時控制文件還記錄系統和各個數據文件的SCN（System Change Number，關於SCN可以參見文章《Oracle SCN機制詳解》）信息，以用於數據恢復，因此數據文件上的SCN變化後，Oracle也會相應修改控制文件上的SCN信息。
1.1.2 用戶數據修改
由於內存的讀寫效率比磁盤的讀寫效率高萬倍，因此，為了降低IO wait，oracle會將數據cache在內存（Buffer Cache，對Buffer Cache的詳細介紹可以參見《Oracle內存全面分析》）中，對數據的讀寫盡量在內存中完成。當Buffer Cache中的數據緩存塊被修改過了，它就被標記為“髒”數據。根據LRU（Least Recently Used）算法，如果一個數據塊最近很少被使用，它就稱為“冷”數據塊。進程DBWn（系統中可以存在多個DBW進程，n為序號）負責將“冷”的“髒”數據寫入數據文件中去。DBWn進程會在以下兩種情況下將“髒”數據寫入磁盤中去：
當服務進程掃描一定數量（閥值）的Buffer Cache後還沒有找到干淨、可重用的緩存塊後，它會通知DBWn進程將“髒”數據寫入文件中去，以釋放出空閒緩存；
當發生檢查點（Checkpoint）時。
1.1.3 Redo Log
在非直接寫（Direct Write）的情況下，事務中的寫操作都會產生Redo Log，作為數據塊異常關閉時的恢復記錄。同樣，和寫用戶數據類似，Redo Log也不會被直接寫入Redo Log文件，而是先寫入Log Buffer中。
Log Buffer是一個可以循環重用的緩存區。LGWR進程負責將Log Buffer中的記錄寫入Redo Log File中去。一旦Log Buffer中的條目被寫入了Redo Log文件中，就可以被重用了。
為了保證事務盡快獲得Log Buffer，LGWR進程一般會盡快將Log Buffer中的數據寫入Redo Log文件中去。在以下幾種情況下，LGWR回將一個連續的Log Buffer寫入Redo Log文件中去：
當一個事務提交（COMMIT）時；
每3秒鐘寫一次Log Buffer；
當Log Buffer到達1/3滿時；
當DBWn進程將“髒”數據寫入磁盤時；
1.1.4 Archive Log
當據庫的Archive Log模式被激活後，所有Redo Log數據都會被寫入Archive Log文件中以便日後進行恢復。當發生日志組切換時，ARCn（Archive進程，可以存在多個）進程就會Redo Log文件拷貝到指定存儲目錄中去，成為Archive Log文件。
1.1.5 臨時表空間
當Oracle在執行一些SQL時，會需要一些臨時空間來存儲執行語句時產生的中間數據。這些臨時空間由Oracle從指定的臨時表空間中分配給進程。主要有三種情況會占用臨時空間：臨時表/索引操作、排序和臨時LOB操作。
臨時表/索引
在會話中，當第一次對臨時表進行INSERT（包括CTAS）時，Oracle會從臨時表空間中為臨時表及其索引分配臨時空間一存儲數據。
排序
任何會使用到排序的操作，包括JOIN、創建（重建）INDEX、ORDER BY、聚合計算（GROUP BY）以及統計數據收集，都可能使用到臨時表空間。
排序操作首先會選擇在內存中的Sort Area進行（Sort In Memory），一旦Sort Area不足，則會使用臨時空間進行排序操作（Sort In Disk）。看以下例子：
SQL> alter session set sort_area_size = 10000000;
Session altered.
SQL> select owner, object_name from t_test1
2 order by object_id;
47582 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1312425564
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47582 | 1486K| 155 (4)| 00:00:02 |
| 1 | SORT ORDER BY | | 47582 | 1486K| 155 (4)| 00:00:02 |
| 2 | TABLE Access FULL| T_TEST1 | 47582 | 1486K| 150 (1)| 00:00:02 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
658 consistent gets
0 physical reads
0 redo size
1566184 bytes sent via SQL*Net to clIEnt
35277 bytes received via SQL*Net from clIEnt
3174 SQL*Net roundtrips to/from clIEnt
1 sorts (memory)
0 sorts (disk)
47582 rows processed
SQL> alter session set sort_area_size = 10000;
Session altered.
SQL> select owner, object_name from t_test1
2 order by object_id;
47582 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1312425564
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47582 | 1486K| | 1251 (1)| 00:0
0:16 |
| 1 | SORT ORDER BY | | 47582 | 1486K| 4136K| 1251 (1)| 00:0
0:16 |
| 2 | TABLE Access FULL| T_TEST1 | 47582 | 1486K| | 150 (1)| 00:0
0:02 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
20 db block gets
658 consistent gets
629 physical reads
0 redo size
1566184 bytes sent via SQL*Net to clIEnt
35277 bytes received via SQL*Net from clIEnt
3174 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
1 sorts (disk)
47582 rows processed
臨時LOB對象
LOB對象包括BLOB、CLOB、NCLOB、和BFILE。在PLSQL程序塊中，如果定義了LOB變量，則這些LOB變量就是臨時LOB對象。臨時LOB對象被創建在臨時表空間上，直到LOB數據被釋放，或者會話結束。
1.1.6 回滾段
我們知道，一個事務在未被提交前，其做的任何修改都是可以被回滾（Rollback）的。這些回滾數據就被放到回滾段（Rollback Segment）上。此外，一致性讀（Read Consistency）、數據庫恢復（Recover）都會用到回滾段。
任何數據塊的修改都會被記錄在回滾段中，甚至Redo Log也會產生回滾記錄。當任何一個非只讀（只有查詢）的事務開始時，oracle會自動為其指定下一個可用的回滾段。事務中任何數據變化都被寫入回滾段中。如果事務回滾，oracle根據回滾段中的回滾記錄將buffer cache中的“髒”數據恢復，釋放回滾段空間。當事務被提交，由於要保證一致性讀，Oracle並不會立即釋放回滾段中的數據，而是會保留一段時間。
1.1.7 Direct-Path Insert
這裡，我們還要介紹一種特殊的寫操作——Direct-Path Insert（直接路徑插入）。Direct-Path Insert通過直接在表中已存在的數據後面添加數據，直接將數據寫入數據文件中，而忽略掉了Buffer Cache。
我們前面提到，為了能在意外時恢復數據，每一個數據修改都會被記錄到Redo Log中。然而，由於Redo Log需要寫入到物理文件中去，是一個比較消耗性能的操作。為了提高性能，我們在批量寫入數據時就可以通過Direct-Path Insert的指定NOLOGING的方式來避免寫Redo Log。
有多種方法可以指定Direct-Path Insert：CTAS（CREATE TABLE AS SELECT）；SQL*Loader指定Direct參數；在語句中指定APPEND提示。
1.2 讀
產生物理讀主要有以下幾種情況：
第一次讀取
當數據塊第一次被讀取到，Oracle會先將其從磁盤上讀入Buffer Cache中，並將他們放在LRU（Last Recently Used）鏈表的MRU（Most Recently Used）端。再次訪問數據塊時就可以直接從Buffer Cache中讀取、修改了。看以下例子：
SQL> select owner, index_name from t_test3; 2856 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 2878488296 -----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2856 | 68544 | 22 (0)| 00:00:01 || 1 | TABLE Access FULL| T_TEST3 | 2856 | 68544 | 22 (0)| 00:00:01 |----------------------------------------------------------------------------- Statistics---------------------------------------------------------- 407 recursive calls 32 db block gets 344 consistent gets 89 physical reads 0 redo size 103888 bytes sent via SQL*Net to clIEnt 2475 bytes received via SQL*Net from clIEnt 192 SQL*Net roundtrips to/from clIEnt 9 sorts (memory) 0 sorts (disk) 2856 rows processed SQL> select owner, index_name from t_test3; 2856 rows selected. Elapsed: 00:00:00.03 Execution Plan----------------------------------------------------------Plan hash value: 2878488296 -----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2856 | 68544 | 22 (0)| 00:00:01 || 1 | TABLE Access FULL| T_TEST3 | 2856 | 68544 | 22 (0)| 00:00:01 |----------------------------------------------------------------------------- Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 276 consistent gets 0 physical reads 0 redo size 103888 bytes sent via SQL*Net to clIEnt 2475 bytes received via SQL*Net from clIEnt 192 SQL*Net roundtrips to/from clIEnt 0 sorts (memory) 0 sorts (disk) 2856 rows processed
如果有新的數據需要被讀入Buffer Cache中，而Buffer Cache又沒有足夠的空閒空間，Oracle就根據LRU算法將LRU鏈表中LRU端的數據置換出去。當這些數據被再次訪問到時，需要重新從磁盤讀入。SQL> select owner, table_name from t_test2
2 where owner = 'SYS'; 718 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1900296288--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 99 | 2178 | 10 (0)| 00:00:01 || 1 | TABLE Access BY INDEX ROWID| T_TEST2 | 99 | 2178 | 10 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T_TEST2_IDX1 | 99 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------- Predicate Information (identifIEd by Operation id):--------------------------------------------------- 2 - Access("OWNER"='SYS') Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 145 consistent gets 0 physical reads 0 redo size 21690 bytes sent via SQL*Net to clIEnt 902 bytes received via SQL*Net from clIEnt 49 SQL*Net roundtrips to/from clIEnt 0 sorts (memory) 0 sorts (disk) 718 rows processed SQL> select * from t_test1; --占用Buffer Cache 47582 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1883417357 -----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 47582 | 3996K| 151 (2)| 00:00:02 || 1 | TABLE Access FULL| T_TEST1 | 47582 | 3996K| 151 (2)| 00:00:02 |----------------------------------------------------------------------------- Statistics---------------------------------------------------------- 195 recursive calls 0 db block gets 3835 consistent gets 5 physical reads 0 redo size 5102247 bytes sent via SQL*Net to clIEnt 35277 bytes received via SQL*Net from clIEnt 3174 SQL*Net roundtrips to/from clIEnt 5 sorts (memory) 0 sorts (disk) 47582 rows processed SQL> select owner, table_name from t_test2 2 where owner = 'SYS'; 718 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1900296288 --------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 99 | 2178 | 10 (0)| 00:00:01 || 1 | TABLE Access BY INDEX ROWID| T_TEST2 | 99 | 2178 | 10 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T_TEST2_IDX1 | 99 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------- Predicate Information (identifIEd by Operation id):--------------------------------------------------- 2 - Access("OWNER"='SYS') Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 145 consistent gets 54 physical reads 0 redo size 21690 bytes sent via SQL*Net to clIEnt 902 bytes received via SQL*Net from clIEnt 49 SQL*Net roundtrips to/from clIEnt 0 sorts (memory) 0 sorts (disk) 718 rows processed全表掃描
當發生全表掃描（Full Table Scan）時，用戶進程讀取表的數據塊，並將他們放在LRU鏈表的LRU端（和上面不同，不是放在MRU端）。這樣做的目的是為了使全表掃描的數據盡快被移出。因為全表掃描一般發生的頻率較低，並且全表掃描的數據塊大部分在以後都不會被經常使用到。
而如果你希望全表掃描的數據能被cache住，使之在掃描時放在MRU端，可以通過在創建或修改表（或簇）時，指定CACHE參數。
邏輯讀指的就是從（或者視圖從）Buffer Cache中讀取數據塊。按照訪問數據塊的模式不同，可以分為即時讀（Current Read）和一致性讀（Consistent Read）。注意：邏輯IO只有邏輯讀，沒有邏輯寫。
即時讀
即時讀即讀取數據塊當前的最新數據。任何時候在Buffer Cache中都只有一份當前數據塊。即時讀通常發生在對數據進行修改、刪除操作時。這時，進程會給數據加上行級鎖，並且標識數據為“髒”數據。
SQL> select * from t_test1 where owner='SYS' for update; 22858 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 3323170753 ------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 22858 | 1919K| 151 (2)| 00:00:02 || 1 | FOR UPDATE | | | | | ||* 2 | TABLE Access FULL| T_TEST1 | 22858 | 1919K| 151 (2)| 00:00:02 |------------------------------------------------------------------------------ Predicate Information (identifIEd by Operation id):--------------------------------------------------- 2 - filter("OWNER"='SYS') Statistics---------------------------------------------------------- 44 recursive calls 23386 db block gets 2833 consistent gets 0 physical reads 5044956 redo size 2029221 bytes sent via SQL*Net to clIEnt 17138 bytes received via SQL*Net from clIEnt 1525 SQL*Net roundtrips to/from clIEnt 0 sorts (memory) 0 sorts (disk) 22858 rows processed一致性讀
Oracle是一個多用戶系統。當一個會話開始讀取數據還未結束讀取之前，可能會有其他會話修改它將要讀取的數據。如果會話讀取到修改後的數據，就會造成數據的不一致。一致性讀就是為了保證數據的一致性。在Buffer Cache中的數據塊上都會有最後一次修改數據塊時的SCN。如果一個事務需要修改數據塊中數據，會先在回滾段中保存一份修改前數據和SCN的數據塊，然後再更新Buffer Cache中的數據塊的數據及其SCN，並標識其為“髒”數據。當其他進程讀取數據塊時，會先比較數據塊上的SCN和自己的SCN。如果數據塊上的SCN小於等於進程本身的SCN，則直接讀取數據塊上的數據；如果數據塊上的SCN大於進程本身的SCN，則會從回滾段中找出修改前的數據塊讀取數據。通常，普通查詢都是一致性讀。
下面這個例子幫助大家理解一下一致性讀：
會話1中：
SQL> select object_name from t_test1 where object_id = 66; OBJECT_NAME------------------------------I_SUPEROBJ1 SQL> update t_test1 set object_name = 'TEST' where object_id = 66; 1 row updated.
會話2中：
SQL> select object_name from t_test1 where object_id = 66; OBJECT_NAME------------------------------I_SUPEROBJ1 Execution Plan----------------------------------------------------------Plan hash value: 1883417357 -----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 27 | 151 (2)| 00:00:02 ||* 1 | TABLE Access FULL| T_TEST1 | 1 | 27 | 151 (2)| 00:00:02 |----------------------------------------------------------------------------- Predicate Information (identifIEd by Operation id):--------------------------------------------------- 1 - filter("OBJECT_ID"=66) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 661 consistent gets 0 physical reads 108 redo size 423 bytes sent via SQL*Net to clIEnt 385 bytes received via SQL*Net from clIEnt 2 SQL*Net roundtrips to/from clIEnt 0 sorts (memory) 0 sorts (disk) 1 rows processed在一個查詢操作中，大量的讀操作都產生於數據的查找過程中。減少查找過程是我們優化IO性能問題的重要目標。
下面介紹幾種主要的數據查找方式。
Full Table Scan
當查詢條件無法命中任何索引、或者掃描索引的代價大於全表掃描代價的某一比例時(由參數optimizer_index_cost_adj設定)，Oracle會采用全表掃描的方式查找數據。當發生全表掃描時，Oracle會自下向上一次讀取一定數量（由參數db_file_multiblock_read_count設定）的數據塊，一直讀取到高水位標志（HWM，High Water Mark）下。Full Table Scan會引起db file scattered read事件。
INDEX UNIQUE SCAN
全表掃描查找數據的效率是非常低的。而索引能大幅提高查找效率。普通索引的數據結構是B-Tree，樹的葉子節點中包含數據的ROWID，指向數據記錄，同時還有指針指向前一個/後一個葉子節點。索引掃描每次讀取一個數據塊，索引掃描是“連續的”（Sequential）。當索引為UNIQUE索引時，每個葉子節點只會指向一條數據。如果Oracle能預知掃描結果只有0或1條記錄時，會采用INDEX UNIQUE SCAN。當對Unique Index中的所有字段進行完全匹配時，會發生INDEX UNIQUE SCAN。
SQL> select object_name from t_test1 2 where object_id = 66; Execution Plan----------------------------------------------------------Plan hash value: 2634232531 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)|00:00:01 || 1 | TABLE Access BY INDEX ROWID| T_TEST1 | 1 | 27 | 1 (0)|00:00:01 ||* 2 | INDEX UNIQUE SCAN | T_TEST1_PK | 1 | | 1 (0)|00:00:01 |---------------------------------------------------------------------------------INDEX UNIQUE SCAN的查找過程如下：
INDEX RANGE SCAN
如果通過索引查找數據時，Oracle認為會返回數據可能會大於1，會進行INDEX RANGE SCAN，例如Unique Index中字段不完全匹配查找時、非Unique Index查找時。
SQL> select object_name from t_test1 2 where object_id < 66; 64 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1635545337 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 57 | 1539 | 2 (0)|00:00:01 || 1 | TABLE Access BY INDEX ROWID| T_TEST1 | 57 | 1539 | 2 (0)|00:00:01 ||* 2 | INDEX RANGE SCAN | T_TEST1_PK | 57 | | 1 (0)|00:00:01 |---------------------------------------------------------------------------------INDEX RANGE SCAN分為閉包（有前後查找邊界）和非閉包（只有一邊或者沒有邊界）。返回數據會依據索引增序排序，多個相同值則會按照ROWID的增序排序。以下的查找條件都是閉包的：
WHERE column = 'Value'WHERE column like 'value%'WHERE column between 'value1' and 'value2'WHERE column in ('value1', 'value2')以下查找條件非閉包：
WHERE column < 'value1'WHERE column > 'value2'閉包條件下的INDEX RANGE SCAN的查找過程如下：
INDEX UNIQUE SCAN和INDEX RANGE SCAN都會引起db file sequential read事件。
TABLE Access BY INDEX ROWID
當發生索引掃描時，如果需要返回的字段都在索引上，則直接返回索引上的數據，而如果還需要返回非索引上的字段的值，Oracle則需要根據從索引上查找的ROWID到對應的數據塊上取回數據，這時就是TABLE Access BY INDEX ROWID。
INDEX FAST FULL SCAN & INDEX FULL SCAN
索引快速全掃描和全表掃描類似，一次讀取db_file_multiblock_read_count個數據塊來描所有索引的葉子節點。INDEX FAST FULL SCAN和其他索引掃描不同，它不會從樹的根節點開始讀取，而是直接掃描所有葉子節點；也不會一次讀取一個數據塊，而是一次讀取db_file_multiblock_read_count個數據塊。INDEX FAST FULL SCAN會引起db file scattered read事件。
SQL> select count(1) from t_test1 where object_id < 21314; Execution Plan----------------------------------------------------------Plan hash value: 1586700957 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1| 4| 24 (5)| 00:00:01|| 1 | SORT AGGREGATE | | 1| 4| | ||* 2 | INDEX FAST FULL SCAN| T_TEST1_PK | 18264| 73056| 24 (5)| 00:00:01|---------------------------------------------------------------------------------
在某些情況下，如db_file_multiblock_read_count值過小、強制使用索引掃描時，會發生INDEX FULL SCAN。INDEX FULL SCAN和INDEX FAST FULL SCAN不同，它是一種索引掃描，按照B-Tree的查找法從樹的根節點開始掃描，遍歷整棵樹，並且一次讀取一個數據塊。它會引起db file sequential read事件。
SQL> select /*+index(a t_test1_pk)*/count(1) from t_test1 a; Execution Plan----------------------------------------------------------Plan hash value: 138350774 -----------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 61 (2)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FULL SCAN| T_TEST1_PK | 47582 | 61 (2)| 00:00:01 |-----------------------------------------------------------------------2 IO系統的設計和配置
要控制好數據庫的整體IO性能，在規劃數據庫架構時就需要做好IO系統的設計和配置。例如，將對IO要求不同的文件放置在不同的存儲設備上；規劃數據文件的分布、均衡IO負擔等。
2.1 OS和存儲相關
IO性能是直接和操作系統已經硬件性能相關的。如果能利用操作系統的一些高級IO特性，或者采用更高速的磁盤設備，能大大提高IO性能。下面介紹一些OS的IO配置、不同的磁盤硬件設備以及存儲技術。
2.1.1 文件系統（File System）和裸設備（Raw Device）
我們知道，內存的讀寫效率比磁盤高近萬倍，因此Oracle在內存中開辟了一片區域，稱為Buffer Cache，使數據的讀寫盡量在Buffer Cache中完成。同樣，在文件系統中，操作系統為了提高讀寫效率，也會為文件系統開辟一塊Buffer Cache用於讀寫數據的緩存。這樣，Oracle的數據會被緩存2次。為了避免OS的這次緩存，我們可以采用裸設備做為數據文件的存儲設備。裸設備，也稱為裸分區（Raw Partition），它是一個沒有被加載（Mount）到操作系統的文件系統上、也沒有加載到Oracle集群文件系統（OCFS Oracle Cluster File System）的磁盤分區，它通過字符設備驅動來訪問。裸設備的文件讀寫不由操作系統控制，而是由應用程序（如Oracle RDBMS）直接控制。
2.1.2 IO方式
OS和文件系統對IO的控制存在多種方式，不同的IO方式下對於數據庫的IO性能影響也不同。
2.1.2.1 Direct IO & Concurrent IO
除了裸設備，某些文件系統可以支持Direct IO，以避開讀寫緩沖。如果要使用Direct IO，需要指定Oracle參數“filesystemio_options”來設置支持Direct IO。但是要注意，不同OS中的不同文件系統對Direct IO的支持也不同：
Windows 在Windows中不需要做特別設置可以直接使用Direct IO；
AIX 在AIX中，JFS文件系統需要通過設置“filesystemio_options”為“SETALL”或者“DIRECTIO”來支持Direct IO；
LINUX Linux在內核版本為2.4.9以上才支持Direct IO。NFS或者OCFS文件系統支持Direct IO。需要設置“filesystemio_options”為“SETALL”或者“DIRECTIO”；
Solaris Solaris需要在操作系統中設置“forcedirectio”選項，並設置“filesystemio_options”為“SETALL”或者“DIRECTIO”。
參數“filesystemio_options”支持4種值：
ASYNCH: 使Oracle支持文件的異步（Asynchronous）IO；
DIRECTIO：使Oracle支持文件的Direct IO；
SETALL：使Oracle同時支持文件的Asynchronous IO和Direct IO；
NONE：使Oracle關閉對Asynchronous IO和Direct IO的支持。
在AIX的JFS2文件系統上，如果“filesystemio_options”為“SETALL”，則會支持Concurrent IO。CIO比DIO的性能更高，因為JFS2的CIO支持多個進程同時對一個文件進行讀寫。
2.1.2.2 Asynchronous IO & Synchronous IO
通常，用的比較多的IO模型是同步IO（Synchronous IO）。在這種模式下，當請求發出之後，應用程序就會阻塞，直到請求滿足為止。這種模式最大好處就是調用應用程序在等待 I/O 請求完成時不需要使用CPU資源。但是，對於一些強調高響應速度的程序（如DB）來說，希望這種等待時間越短越好，我們這時就可以考慮采用異步IO（Asynchronous IO）模式。異步IO模式下，進程發出IO請求後無需等待IO完成，可以去處理其它事情；IO請求被放入一個隊列中，一旦IO完成，系統會發出信號通知進程。
異步IO可以使需要大量寫的Oracle進程（如DBWn進程）將IO請求隊列化，以充分利用硬件的IO帶寬，從而使它們能最大程度實現並行處理。異步IO還可以使那些需要進行大量計算的操作（如排序）在它們發出IO請求前預先從磁盤取出數據，以使IO和計算並行處理。
確認操作系統已經設置支持AIO後，還需要設置Oracle初始化參數"DISK_ASYNCH_IO"為“true”以支持異步IO。
2.1.3 負載均衡及條帶化（Striping）
當多個進程同時訪問一個磁盤時，會出現磁盤沖突。大多數磁盤系統都對訪問次數（每秒的IO操作）和數據傳輸率（每秒傳輸的數據量）有限制。當達到這些限制時，後面要訪問磁盤的進程就需要等待，這時就是所謂的磁盤沖突。
避免磁盤沖突是優化IO性能的一個目標，這就需要將一個熱點磁盤上的IO訪問負載分擔到其他可用磁盤上，也就是IO負載均衡。在一些成熟的磁盤負載均衡技術出現之前，DBA需要了解/預測各系統的IO負載量，通過手工配置每個數據到不同存放位置以分擔IO負載來達到負載均衡的目的。
條帶化技術就是將數據分成很多小部分並把他們分別存儲到不同磁盤上的不同文件中去。這就能使多個進程同時訪問數據的多個不同部分而不會造成磁盤沖突。很多操作系統、磁盤設備供應商、各種第三方軟件都能做到條帶化。通過條帶化，DBA可以很輕松的做到IO負載均衡而無需去手工配置。
2.1.4 RAID
RAID的全稱是獨立磁盤冗余陣列（Redundant Array of Independent Disks）。它通過將多個相對比較便宜的磁盤組合起來，並相互連接，同時都連到一個或多個計算機上，以組成一個磁盤組，使其性能和容量達到或超過一個價格更昂貴的大型磁盤。RAID分為6級。
RAID-0
RAID-0只提供純粹的條帶化（Stripping）。條帶可以使一個大文件被多個磁盤控制器同時訪問，因此支持對數據的並發訪問。RAID-0不提供數據冗余和奇偶保護，它只關注性能。如果RAID-0中任何一個磁盤出錯，整個數據庫都會崩潰。
RAID-1
RAID-1提供磁盤鏡像（Disk Mirror）。在RAID-1中，所有數據都會被寫入兩個獨立的磁盤中，以實現對數據的冗余保護。兩塊磁盤的數據是同時寫入的，以保證其速度不會低於寫入單獨磁盤的速度。RAID-1實現了數據的完全冗余，它提供了所有RAID級別中最安全可靠的數據保護。在這種模式下，寫的性能下降了，但讀的性能被提升了。此外，RAID-1也是最占用磁盤空間的模式
RAID 0+1
RAID-0能提供更好的性能，RAID-1提供最佳的數據保護。如果把兩者結合在一起就能同時提供高性能和數據保護，但是也會同時提高磁盤陣列造價。
RAID-3
在RAID-3中，會有一塊專門的磁盤驅動被用作存儲錯誤修正或者奇偶校驗數據。而其他的磁盤驅動則被條帶化。RAID-3的並行處理能力比較低，它適合於主要是讀操作的系統（如決策分析系統 DSS，但是DSS會存在大量復雜查詢，需要做JOIN，同樣也會存在一些臨時的寫操作），不適合存在大量寫操作的系統（OLTP）。
RAID-5
RAID-5不做全磁盤鏡像，但它會對每一個寫操作做奇偶校驗計算並寫入奇偶校驗數據。奇偶校驗磁盤避免了像RAID-1那樣完全重復寫數據。當一個磁盤失效，校驗數據被用來重建數據，從而保證系統不會崩潰。為避免磁盤瓶頸，奇偶校驗和數據都會被分布到陣列中的各個磁盤。盡管讀的效率提高了，但是RAID-5需要為每個寫操作做奇偶校驗，因此它的寫的效率很差。
RAID-S
RAID-S是EMC公司的RAID-5的實施方案，它和純粹的RAID-5存在以下區別：
(1) 它條帶化奇偶校驗，但不條帶化數據；
(2) 它與一個帶有寫緩存的異步硬件環境合並。
這個緩存主要是一種延遲寫的機制，因此它能讓系統在相對不忙的時候計算和寫奇偶校驗信息。
RAID-7
RAID-7也同樣引入了緩存機制，這個緩存是被一個內嵌式操作系統控制。但是，RAID-7中數據是被條帶化的，而奇偶校驗不被條帶化。奇偶校驗信息被存放著一個或者多個專門的磁盤上。
2.1.5 SAN
SAN（Storage Area Network，存儲區域網）是一個高速的子網，這個子網中的設備可以從你的主網卸載流量。通常SAN由RAID陣列連接光纖通道（Fibre Channel）組成，SAN和服務器和客戶機的數據通信通過SCSI命令而非TCP/IP，數據處理是“塊級”（block level）。
SAN通過特定的互連方式連接的若干台存儲服務器組成一個單獨的數據網絡，提供企業級的數據存儲服務。 SAN是一種特殊的高速網絡，連接網絡服務器和諸如大磁盤陣列或備份磁帶庫的存儲設備，SAN置於LAN之下，而不涉及LAN。利用SAN，不僅可以提供大容量的存儲數據，而且地域上可以分散，並緩解了大量數據傳輸對於局域網的影響。SAN的結構允許任何服務器連接到任何存儲陣列，不管數據置放在哪裡，服務器都可直接存取所需的數據。
2.1.6 NAS
NAS是Network Attached Storage（網絡附加存儲）的簡稱。在NAS存儲結構中，存儲系統不再通過I/O總線附屬於某個服務器或客戶機，而直接通過網絡接口與網絡直接相連，由用戶通過網絡訪問。它是連接到一個計算機網絡的文件層的數據存儲，它可以為不同網絡客戶端提供數據存儲服務。NAS的硬件與傳統的專用文件服務器相似。它們的不同點在於軟件端。NAS中的操作系統和其他軟件只提供數據存儲、數據訪問功能，以及對這些功能的管理。與傳統以服務器為中心的存儲系統相比，數據不再通過服務器內存轉發，直接在客戶機和存儲設備間傳送，服務器僅起控制管理的作用。
2.2 IO配置
在借助各種成熟的存儲技術的基礎上，合理配置系統的IO分布及系統IO配置能大量減少系統在生產運行中出現IO性能及相關問題的幾率。當然，這些配置是我們在布置數據庫系統時初始建議，對於復雜的系統來說，很多配置（如一些存儲相關的參數）是需要根據系統的運行狀況進行調優的。
在數據庫系統中，如果某個文件或者某塊磁盤上存在遠遠高於其他文件或磁盤的大量IO訪問，我們就稱這個文件或磁盤為熱點文件/磁盤。我們在做IO規劃時的一個重要目標就是要消除系統中熱點文件/磁盤的存在，使整個系統的IO負載相對平衡。
由於現在的存儲技術成熟、成本降低，大多數系統都采用條帶化來實現系統的IO負載分擔。如果操作系統有LVM（Logical Volume Manager邏輯卷管理器）軟件或者硬件條帶設備，我們就可以利用這些攻擊來分布IO負載。當使用LVM或者硬件條帶時，決定因素是條帶深度（stripe depth）和條帶寬度（stripe width）：
需要根據系統的IO要求來合理的選擇這些數據。對於Oracle數據庫系統來數，比較合理的條帶深度是從256K到1M。下面分析影響條帶深度和條帶寬度的影響因素。
為了提高IO效率，我們要盡量使一次邏輯IO請求由一塊磁盤的一次物理IO請求。因而影響條帶的一個重要因素就是一次邏輯IO請求的大小。
此外，系統中IO的並發度不同我們對條帶的配置要求也不同。例如，在高並發度且IO請求的大小都比較小的情況下，我們希望一塊磁盤能同時響應多個IO操作；而在那些存在大IO請求的低並發度系統中，我們可能就需要多塊磁盤同時響應一個IO請求。無論是一個磁盤還是多個磁盤響應IO請求，我們的一個原則是讓一次邏輯IO能被一次處理完成。
下面先看下影響IO大小的操作系統和Oracle的相關參數：
其中，前面兩個是最關鍵的兩個參數。
在OLTP系統中，會存在大量小的並發的IO請求。這時就需要考慮選擇比較大的條帶深度。使條帶深度大於IO大小就稱為粗粒度條帶（Coarse Grain Striping）。在高並行度系統中，條帶深度為（n * db_block_size），其中n為大於1的整數。
通過粗粒度條帶能實現最大的IO吞吐量（一次物理IO可以同時響應多個並發的邏輯IO）。大的條帶深度能夠使像全表掃描那樣的多數據塊讀操作由一個磁盤驅動來響應，並提高多數據塊讀操作的性能。
在低並發度的DSS系統中，由於IO請求比較序列化，為了避免出現熱點磁盤，我們需要避免邏輯IO之由一塊磁盤處理。這是，粗粒度條帶就不適合了。我們選擇小的條帶深度，使一個邏輯IO分布到多個磁盤上，從而實現IO的負載均衡。這就叫細粒度條帶。條帶深度的大小為（n * db_block_size），其中n為小於多數據塊讀參數（db_file_multiblock_read_count）大小的整數。
另外，IO過程中，你無法保證Oracle數據塊的邊界能和條帶單元的大小對齊。如果條帶深度大小和Oracle數據塊大小完全相同，而它們的邊界沒有對齊的話，那麼就會存在大量一個單獨的IO請求被兩塊磁盤來完成。
在OLTP系統中，為了避免一個邏輯IO請求被多個物理IO操作完成，條帶寬度就需要設置為兩倍或者兩倍以上於Oracle數據塊大小。例如，如果條帶深度是IO大小的N倍，對於大量並發IO請求，我們可以保證最少有（N-1）/ N的請求是由一塊磁盤來完成。
正如我們前面所述，無論是一個還是多個磁盤響應一個邏輯IO，我們都要求IO能被一次處理。因而在確定了條帶深度的基礎上，我們需要保證條帶寬度 >= IO請求的大小 / 條帶深度。
此外，考慮到以後系統容量的擴充，我們也需要規劃好條帶寬度。
如今大多數LVM都支持在線動態增加磁盤。也就是在磁盤容量不足時，我們可以隨時將新磁盤加入到一個已經使用的邏輯卷中。這樣的話，我們在設置邏輯卷時就可以簡單地將所有磁盤都歸入到一個卷中去。
但是，有些LVM可能還不支持動態增加磁盤。這時我們就需要考慮以後的容量擴充對IO均衡的影響了。因為你新增加的磁盤無法加入原有卷，而需要組成一個新的卷。但一般擴充的容量和原有容量比較相對比較小，如果原有卷的條帶寬度比較大的話，新增加的卷的條帶寬度無法達到其大小，這樣就會使新、舊卷之間出現IO失衡。
例如，一個系統的初始配置是一個包含64塊磁盤、每塊磁盤大小為16G的單一邏輯卷。磁盤總的大小是1T。隨著數據庫的數據增長，需要增加80G的空間。我們把新增加的5個16G磁盤再組成一個邏輯卷。這樣就會導致兩個卷上的IO失衡。為了避免這種情況。我們可以將原有磁盤配置成每個條帶寬度為8個磁盤的8個邏輯卷，這樣在新增加磁盤時可以也增加為8個磁盤的新卷。但必須要保證8個磁盤的條帶寬度能夠支持系統的每秒IO吞吐量。
如果你的條帶寬度設置得比較小，就需要估算出你的各個數據庫文件的IO負載，並根據負載量不同將他們分別部署到不同卷上一分擔IO負載。
如果系統不支持LVM或者硬件條帶，IO負載就必須由DBA根據數據庫文件的IO負載不同手工將他們分散到各個磁盤上去以保證整個系統的IO負載均衡。
有許多DBA會將哪些使用頻率非常高的表和它的索引分開存儲。但實際上這種做法並不正確。在一個事務中，索引會先被讀取到然後再讀取表，它們的IO操作是有前後順序的，因此索引和表存儲在同一個磁盤上是沒有沖突的。僅僅因為一個數據文件即包含了索引又包含了數據表而將它分割是不可取的。我們需要根據文件上的IO負載是否已經影響到了數據庫的性能來決定是否將數據文件分割。
為了正確分布文件，我們首先必須先了解各個數據庫文件的IO負載需求以及IO系統的處理能力。鑒定出每個文件的IO吞吐量。找出哪些文件的IO吞吐率最高而哪些IO量很少，將它們分散分布到所有磁盤上去以平衡IO吞吐率。
如果你不了解或者無法預計文件的IO負載，就只能先估計他們的IO負載來規劃文件分布，在系統運行過程中再做調整。
無論是采用操作系統條帶化還是手工IO分布方式，如果IO系統或者IO規劃布置無法滿足IO吞吐率的要求，我們就需要考慮將高IO吞吐率的文件和其他文件分離。我們可以在存儲規劃階段或者系統運行階段找出那樣的文件。
除了IO吞吐率，在決定是否分割文件時，我們還需要考慮可恢復性以及數據容量擴張問題。
但是在分割文件之前，一定要確認存在IO瓶頸，然後再根據產生IO瓶頸的數據定位到存在高IO吞吐率的文件（熱點文件）。
如果具有高IO吞吐率的數據文件屬於包含表和索引的表空間，我們就需要找出這些文件的IO是否可以通過SQL語句調優或者優化應用程序來降低。
如果具有高IO吞吐率的數據文件屬於臨時表空間，那我們就需要檢查是否可以通過避免或調優SQL語句的排序操作來降低IO。
經過應用調優後，如果IO分布仍然無法滿足IO吞吐的要求，我們就需要考慮分離高IO吞吐率的數據文件了。
如果具有高IO吞吐率的文件是Redo Log文件，則需要考慮將Redo Log文件與其他文件分離，可以通過以下配置來實現：
Redo Log文件是由LGWR進程序列化的寫入的。如果在同一個磁盤上不存在並發的其他IO操作，寫入效率就更高。我們需要確認已經沒有其他優化調整空間再考慮分割Redo Log文件。如果系統支持AIO但還沒有激活該特性，可以考慮激活AIO看是否能解決Redo Log的IO性能瓶頸。
如果歸檔變慢，我們也許可以通過使LGWR的寫操作與Archive進程的讀操作分離來避免LGWR進程魚Archive進程直接的IO沖突。我們可以同交替成組存放Redo Log文件來實現。
例如，我們有四組Redo Log，每組包含兩個Log文件：（A1，A2）、（B1，B2）、（C1，C2）、（D1，D2）。我們就可以以下面這種存放方式將它們分布存儲到四個磁盤上去來實現磁盤分離訪問：（A1，C1）、（A2、C2）、（B1，D1）、（B2，D2）。
當LGWR進程做日志切換時，如從A組切換到B組，LGWR開始向B組寫Redo Log（第三、四塊磁盤），而Archive進程則從B組讀取數據（第一、二塊磁盤）寫入歸檔文件中去，他們分別訪問的是不同磁盤，因而避免了IO沖突。
這裡給出三種簡單的操作系統IO配置的例子，包括如何簡單地計算來決定磁盤的拓撲結構、條帶深度等等。
IO配置最簡單的方法就是建立一個大的邏輯卷，將所有磁盤都條帶化到這個卷中去。考慮到可恢復性，這個卷需要被鏡像（RAID 1）。每個磁盤的條帶深度必須大於頻繁執行的IO操作的最大IO大小。這種配置對大多數情況都能提供足夠的性能支持。
在歸檔模式下，如果歸檔文件也和其他文件放在同一個條帶化的卷中，那麼當歸檔進程對Redo Log進行歸檔時，會大大增加磁盤的IO負載。將歸檔日志轉移到其他磁盤上有如下好處：
歸檔日志的磁盤數由歸檔日志產生的頻率以及歸檔存儲容量決定。
在更新非常頻繁的OLTP系統中，Redo Log的寫操作非常頻繁。將Redo Log文件轉移到其他磁盤上可以有如下好處：
Redo Log的磁盤數量有Redo Log的大小決定。由於現在的磁盤容量都非常大，通常配置兩個磁盤（如果做鏡像則需要四塊）就足夠了。並且，根據我們前面的分析，將Redo Log文件交互的存放到兩塊磁盤上去能避免LGWR進程的寫操作與ARCH進程的讀操作之間的IO沖突。
3 Oracle中的IO問題及其解決思路
對於負載偏重點不同，我們可以簡單的將數據庫系統分為CPU負載系統（CPU Bound System）和IO負載系統（IO Bound System）。顧名思義，CPU負載系統的資源瓶頸在於CPU，而IO負載系統的瓶頸在於磁盤IO。
我們可以通過操作系統的一些命令來確認一個系統是否是存在IO負載。在UNIX下，可以使用"iOStat"或者"sar -d"來看系統的IO情況；在Windows下，可以通過系統的性能監視器查看，但由於性能監控器中看到的IO是靜態的IO總量信息，並不直觀，因此也可以用本站的TopShow工具來查看實時的IO信息。
在UNIX系統下，發現CPU IDEL很低並不一定代表這是一個CPU負載系統。一個IO負載系統在表面上看CPU的IDEL值也可能很低：
oracle@db01:/export/home/Oracle> sar -u 1 10
HP-UX hkhpdv45 B.11.23 U ia64 10/24/07
09:43:05 %usr %sys %wio %idle
09:43:06 43 25 30 1
09:43:07 44 36 19 1
09:43:08 23 27 44 6
09:43:09 12 37 50 1
09:43:10 10 36 51 3
09:43:11 15 34 42 9
09:43:12 18 36 44 3
09:43:13 17 35 46 2
09:43:14 12 32 52 4
09:43:15 12 31 56 1
Average 21 33 43 3
我們可以注意到，實際上WIO是引起CPU IDEL過低的主要原因。WIO是當一個進程需要運行或已經運行後，因為需要等待IO事件而被阻塞了。事實上CPU是處於IDEL狀態（在某些系統中，已經將WIO取消並歸為IDEL），真正的原因是系統中存在IO瓶頸。
通過iOStat或者sar -d我們可以找出存在IO瓶頸的磁盤設備，如果該磁盤設備是用於Oracle 數據庫存儲文件的，我們可以判斷出是數據庫存在IO問題。在Windows下，可以通過TopShow來找出哪個進程正在進行大量IO傳輸，如果是Oracle進程，也可以判斷為是數據庫存在IO問題。
確認系統存在IO問題後，我們就需要定位到底是什麼引起的IO問題，該采取什麼措施來解決問題。根據我們前面的介紹，Oracle中存在各種IO，要定位IO，最好的工具是statspack（在10g以後，可以用AWR）。通過statspack report的Top 5 Events，我們可以看到對系統系能影響最大的5個等待event，而不同的IO問題會對應不同Event，所以，我們可以根據這些event采取不同的措施來解決IO問題。下面是一個典型的IO負載系統的Top 5 Event：
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 70,575,969 344,200 53.34
db file scattered read 11,240,748 163,242 25.30
log file sync 657,241 36,363 5.64
CPU time 35,290 5.47
log file parallel write 833,799 20,767 3.22
可以看到，前兩個時間“db file sequential read”和“db file scattered read”分別占了總等待時間的53.34%和25.30%，而我們前面提到這兩個事件分別是由索引掃面和全表掃面（或快速索引掃面）引起的，因此，能解決索引掃面問題和全表掃面問題就能解決這個系統的IO瓶頸。
IO問題到底對CPU有多大影響呢？我們用以上例子中的數據分析一下。從等待時間統計數據中，我們看到的是時間在總等待時間中所占的比例。而系統的“總響應時間 ”= “等待時間 ”+ “CPU工作時間”（注意，上面Top 5事件中的“CPU Time”不是指CPU的工作時間，而是指CPU的等待時間）。“CPU工作時間”的數據我們可以在“Instance ActivitIEs Stats for DB”這一分類統計數據中找到：
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 17,136,868 396.7 15.5
先計算出“總等待時間” = 344,200 * 100% / 53.34% = 645,294s
“總響應時間” = “總等待時間” + “CPU工作時間” = 645,294 + 17,136,868 = 17,782,162s
我們可以算出“CPU工作時間”、“db file sequential read”和“db file scattered read”分別在“總響應時間中所占的比例為：
CPU工作時間 = 17,136,868 / 17,782,162 = 96.4%
“db file sequential read” = 344,200 / 17,782,162 = 1.9%
“db file scattered read” = 163,242 / 17,782,162 = 0.9%
可見，IO事件所引起的等待時間在總響應時間所占比例並不大。因此，我們在做系統優化之前先分析系統是CPU負載系統還是IO負載系統對於我們的優化方向和最終的優化效果起很大的作用。
以下事件是可能由IO問題引起的等待事件，在IO負載系統中，我們要特別關注這些事件：
與數據文件相關的IO事件
'db file sequential read'
'db file scattered read'
'db file parallel read'
'direct path read'
'direct path write'
'direct path read (lob)'
'direct path write (lob)'
與控制文件相關的IO事件
'control file parallel write'
'control file sequential read'
'control file single write'
與Redo日志相關的IO事件
'log file parallel write'
'log file sync'
'log file sequential read'
'log file single write'
'switch logfile command'
'log file switch completion'
'log file switch (clearing log file)'
'log file switch (checkpoint incomplete)'
'log switch/archive'
'log file switch (archiving needed)'
與Buffer Cache相關的IO事件
'db file parallel write'
'db file single write'
'write complete waits'
'free buffer waits'
下面我們就分別介紹如何解決IO問題。
3.1 IO調優的思路及常用手段
通過對statspack或者awr報告的分析，我們可以得知是那些IO相關事件引起的IO問題。針對不同的事件，可以采取不同的分析、處理方法。而有一些通用的方法並不是針對特定的事件的。我們這裡先介紹一下這些方法。
3.1.1 通過SQL調優來減少IO請求
一個沒有任何用戶SQL的數據庫幾乎不產生任何IO。基本上數據庫所有的IO都是直接或間接由用戶提交的SQL所導致的。這意味著我們可以通過控制單個SQL產生的IO來降低數據庫總的IO請求。而通過SQL調優來降低SQL查詢計劃中的IO操作次數則是降低SQL產生IO的最好方法。數據庫的性能問題通常是由少數幾個SQL語句所導致的，它們產生了大量IO導致了整個數據庫的性能下降。優化幾條問題語句往往就能解決整個數據庫的IO性能問題。
從Oracle 10g開始，ADDM能夠自動檢測出問題語句，同時，再通過查詢優化建議器能夠自動優化語句並降低它們對IO的消耗。關於ADDM和查詢優化建議器可以參考文章《Oracle 10G 新特性——ADDM和查詢優化建議器》。
3.1.2 通過調整實例參數來減少IO請求
在這種方法中，主要有兩種途徑來實現對IO的優化。
使用內存緩存來減少IO
通過一些內存緩存，如Buffer Cache、Log Buffer、Sort Area，可以降低數據庫對IO的請求。
當Buffer Cache被增大到一定大小時，絕大多數結果可以直接從緩存中獲取到，而無需從磁盤上讀取了。而在進行排序操作時，如果Sort Area足夠大，排序過程中產生的臨時數據可以直接放在內存中，而無需占用臨時表空間了。
調整multiblock IO（多數據塊IO）的大小
控制Multiblock IO的參數叫DB_FILE_MULTIBLOCK_READ_COUNT，它控制在多數據塊讀時一次讀入數據塊的次數。適當增加這個參數大小，能夠提高多數據塊操作（如全表掃描）的IO效率。例如，讀取100M數據，如果每次讀取1M一共讀取100次的效率就比每次讀取100K一共讀取1000次更快。但是這個數字達到一定大小後，再增加就作用不大了：每次10M一共讀100次來讀取1G的數據的效率和單獨一次讀取1G數據的效率是沒有多大區別的。這是因為IO效率受到2個因素的影響：IO建立時間和IO傳輸時間。
IO建立時間對於不同IO大小來說都是相同的，它決定了對小IO的總的IO時間，增大Multiblock IO大小可以減少IO建立時間；
IO傳輸時間與IO大小是成正比的，在小IO時，IO傳輸時間一般比IO建立時間少，但對於大IO操作來說，IO傳輸時間決定了總的IO時間。因此Multiblock IO大小增大到一定大小時，它對總的IO時間影響就不大了。
3.1.3 在操作系統層面優化IO
如我們前面所介紹的，利用一些操作系統提供的提升IO性能的特性，如文件系統的異步IO、Direct IO等來優化數據庫系統的IO性能。另外一種方法就是增加每次傳輸的最大IO大小的限制（大多數Unix系統中，由參數max_io_size控制）。
3.1.4 通過Oracle ASM實現對IO的負載均衡
ASM（Automatic Storage Manager自動存儲管理）是從Oracle 10g開始引入的。它是一個建立在數據庫內核中的文件系統和卷管理器。它能自動將IO負載均衡到所有可用的磁盤啟動器上去，一避免“熱區”。ASM能防止碎片，因此無需重建數據來回收空間。數據被均衡分布到所有硬盤上。
3.1.5 通過條帶化、RAID、SAN或者NAS實現對IO的負載均衡
這個方法通過一些成熟的存儲技術，如條帶化、RAID、SAN和NAS，來將數據庫IO分布到多個可用的物理磁盤實現負載均衡，以避免在還存在空閒可用磁盤時出現的磁盤爭用和IO瓶頸問題。
關於這幾種存儲技術，我們文章的前面部分都有做介紹。
3.1.6 通過手工布置數據庫文件到不同的文件系統、控制器和物理設備上來重新分布數據庫IO
當數據庫系統中缺乏以上各種存儲技術手段時，我們可以考慮使用這種方式。這樣做的目的是使數據庫的IO得到均勻分布，從而避免在還有空閒磁盤時出現磁盤爭用和IO瓶頸問題。當然這種手工分布IO方法是無法達到以上的自動分布IO的效果的。
3.1.7 其他手段
系統中總會存在一些IO是無法消除或降低的。如果采用以上手段還不能滿足IO性能要求的話，可以考慮這兩種方法：
將老數據移除你的生產數據庫（Housekeep）
采用更多、更快的硬件
3.2 數據文件相關的IO事件
數據庫系統中的大多數的IO請求都是針對數據文件的。因此大多數情況下，與數據文件相關的IO事件是引起系統IO性能的主要原因。這些事件也是我們文章需要重點介紹的事件。下面分別針對不同事件介紹問題的解決思路。
3.2.1 db file sequential read
這個事件是是最常見的IO等待事件。它一般發生在讀取單獨數據塊時，如讀取索引數據塊或者通過索引訪問一個表數據塊，另外在讀取數據文件頭數據塊時也會發生db file sequential read等待事件。
當發現這個等待事件成為系統等待事件中的主要事件，我們可以通過一下方法來處理：
3.2.1.1 優化Top SQL
從statspack或者awr報告中的“SQL ordered by Reads”部分或者通過V$SQL視圖找出系統中的Top SQL，對SQL進行調優以減少IO請求。
當SQL中存在Index Range Scan時，如果訪問的索引的選擇性不好就會導致需要訪問過多的數據塊，這時可以通過建立一個、或強制SQL使用一個已經存在的選擇性更好的索引。這樣使我們訪問更少的數據塊來獲取到需要的數據。
SQL> select object_id, object_name
2 from t_test1
3 where owner = 'SYS'
4 and created > sysdate - 30;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4014220762
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 11 (0)
| 00:00:01 |
|* 1 | TABLE Access BY INDEX ROWID| T_TEST1 | 1 | 39 | 11 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX1 | 576 | | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
658 consistent gets
45 physical reads
0 redo size
339 bytes sent via SQL*Net to clIEnt
374 bytes received via SQL*Net from clIEnt
1 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> create index t_test1_idx2 on t_test1(owner, created);
Index created.
SQL> select object_id, object_name
2 from t_test1
3 where owner = 'SYS'
4 and created > sysdate - 30;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3417015015
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1911 | 2 (0)
| 00:00:01 |
| 1 | TABLE Access BY INDEX ROWID| T_TEST1 | 49 | 1911 | 2 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX2 | 49 | | 1 (0)
| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
2 - Access("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
339 bytes sent via SQL*Net to clIEnt
374 bytes received via SQL*Net from clIEnt
1 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
0 rows processed
如果索引存在碎片，那每個索引數據塊上的索引數據就更少，會導致我們需要訪問更多的索引數據塊。這時，我們需要考慮重建索引來釋放碎片；
判斷一個所以是否需要重建，我們介紹一個簡單的方法：對一個索引進行結構分析後，如果該索引占用超過了一個數據塊，且滿足以下條件之一：B-tree樹的高度大於3；使用百分比低於75%；數據刪除率大於15%，就需要考慮對索引重建：
SQL> analyze index t_test1_idx1 compute statistics;
Index analyzed.
SQL> analyze index t_test1_idx1 validate structure;
Index analyzed.
SQL> select btree_space, -- if > 8192（塊的大小）
2 height, -- if > 3
3 pct_used, -- if < 75
4 del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100 as deleted_pct -- if > 20%
5 from index_stats;
BTREE_SPACE HEIGHT PCT_USED DELETED_PCT
----------- ---------- ---------- -----------
880032 2 89 0
如果使用的索引的聚簇因子（Clustering Factor）很大，說明一條索引記錄指向多個數據塊，在返回結果時需要讀取更多的數據塊。通過重建表可以降低聚簇因子，因而可以在查找索引時減少表數據塊的訪問塊數。
聚簇因子說明了表數據的物理存儲位置相對於一個索引的排序性的符合程度。例如，一個非唯一索引是建立在A字段上的，如果表數據的存儲是以A字段的順序存儲的，則索引與數據的關系如下圖：
R
B1
B2
B3
A3
A4
A5
A1 A2 A2 A2 A2 A2 A3
A3 A3 A3 A3 A3 A4 A4
A2
A1
… 表數據
索引結構
此時，索引的聚簇因子很低，從圖上看到，假如我們需要獲取A=A2的數據，只需要讀取一個數據塊就可以了；
相反，如果表數據物理存儲順序和索引順序相差很大，就會出現下面的情況：
R
B1
B2
B3
A3
A4
A5
A1 A2 A3 A3
A1 A4 A2 A5
A2
A1
… 表數據
索引結構
A4 A3 A2 A1
A2 A3 A3 A5
這時該索引的聚簇因子就很大，可以看到，如果需要獲取A=A2的數據，我們需要讀取4塊或更多的數據塊。
對索引進行分析後，我們可以從視圖DBA_INDEXES中獲取到索引的聚簇因子，字段名為Clustoring_Factor。如果一個索引是一張表主要被使用的索引（或者是該表的唯一索引），且它的聚簇因子過高導致IO請求過高的話，我們可以考慮采取以下措施來降低IO：
1) 以索引字段的順序重建表以降低聚簇因子，可以用以下語句重建表（當然，你還需要重建觸發器、索引等對象，還可能需要重建、重新編譯有關聯對象）：
CREATE new_table AS SELECT * FROM old_table ORDER BY A;
2) 建立基於索引字段IOT（索引表）。
如果該索引不是表的主要索引，只是被少量語句引用到，按照以上方式處理的話反而可能會使其他使用更加頻繁的索引的聚簇因子增大，導致系統性能更差。這時我們可以建立包含返回字段的索引，以避免“TABLE Access BY INDEX ROWID”。如以下例子：
SQL> set autot trace
SQL> select status from t_test1
2 where owner = 'DEMO';
576 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4014220762
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 576 | 6336 | 11 (0)
| 00:00:01 |
| 1 | TABLE Access BY INDEX ROWID| T_TEST1 | 576 | 6336 | 11 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX1 | 576 | | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
2 - Access("OWNER"='DEMO')
Statistics
----------------------------------------------------------
465 recursive calls
0 db block gets
222 consistent gets
43 physical reads
0 redo size
8368 bytes sent via SQL*Net to clIEnt
803 bytes received via SQL*Net from clIEnt
40 SQL*Net roundtrips to/from clIEnt
8 sorts (memory)
0 sorts (disk)
576 rows processed
SQL> create index t_test1_idx3 on t_test1(owner, status) compute statistics;
Index created.
SQL> select status from t_test1
2 where owner = 'DEMO';
576 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2736516725
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 576 | 6336 | 2 (0)| 00:00:01|
|* 1 | INDEX RANGE SCAN| T_TEST1_IDX3 | 576 | 6336 | 2 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
1 - Access("OWNER"='DEMO')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43 consistent gets
3 physical reads
0 redo size
8152 bytes sent via SQL*Net to clIEnt
803 bytes received via SQL*Net from clIEnt
40 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
576 rows processed
通過分區裁剪（partition pruning）技術來減少的SQL對數據塊的訪問。
采用分區裁剪技術，Oracle優化器會先分析FROM和WHERE字句，在建立訪問分區列表時將那些不會被訪問到的分區排除。例如，我們的表T_TEST1的owner字段的值有“SYS、SYSTEM、XDB、DEMO、TEST”，如果我們按照owner字段建立的是分區表：
CREATE TABLE t_test1
(object_id NUMBER(5),
object_name VARCHAR2(30),
owner VARCHAR2(20),
created DATE)
PARTITION BY LIST(owner)
(
PARTITION owner_sys VALUES('SYS', 'SYSTEM'),
PARTITION owner_xdb VALUES ('XDB'),
PARTITION owner_demo VALUES('DEMO'),
PARTITION owner_test VALUES('TEST'),
PARTITION owner_others VALUES(DEFAULT)
);
則對於以下語句：
select object_name
from t_test1
where owner in ('DEMO', 'TEST')
and created > sysdate - 30;
優化器會先將分區owner_sys、owner_xdb、owner_others從分區訪問列表中裁剪出去，只訪問分區owner_demo和owner_test上的數據或者通過這兩個分區上的索引來訪問數據。
3.2.1.2 處理非SQL導致的IO問題
如果從statspack或者AWR報告中找不到明顯產生db file sequential read事件的SQL，則該等待事件可能是由於以下原因導致的：
熱點數據文件或磁盤
數據文件所在的磁盤IO負荷過重導致對IO請求反映慢，這時，我們可以通過statspack或AWR報告中的“File I/O Statistics”部分（或者通過V$FILESTAT視圖）來找到熱點磁盤:
Statspack report：
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
AFW_DATA /export/home/icssprd/data/data17/iCSSprd_afw_data_01
726 0 4.3 1.0 381 0 0
AFW_INDX /export/home/icssprd/data/data18/iCSSprd_afw_indx_01
1,741 0 6.3 1.0 2,104 0 0
CSS_AN_DATA /export/home/icssprd/data/data03/icssprd_CSS_an_data
200,649 5 1.8 3.2 24,192 1 0
/export/home/icssprd/data/data04/icssprd_CSS_an_data
242,462 6 1.6 3.1 26,985 1 3 6.7
CSS_AN_INDX /export/home/icssprd/data/data13/icssprd_CSS_an_indx
70,789 2 5.0 1.6 5,330 0 0
CSS_AUDIT_RESOURCES_DATA /export/home/icssprd/data/data10/icssprd_CSS_audit_r
2,394 0 0.6 1.0 1,781 0 0
CSS_AUDIT_RESOURCES_INDX /export/home/icssprd/data/data11/icssprd_CSS_audit_r
248 0 4.3 1.0 52 0 0
... ...
視圖：
SQL> select b.name, phyrds, phywrts
2 from V$FILESTAT a, V$DATAFILE b
3 where a.file# = b.file#;
NAME
--------------------------------------------------------------------------------
PHYRDS PHYWRTS
---------- ----------
C:\Oracle\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSTEM_20TFOB4Q_.DBF
132767 11565
C:\Oracle\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_UNDOTBS1_20TFQP78_.DBF
1943 19924
C:\Oracle\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSAUX_20TFSGC6_.DBF
659458 100811
... ...
找到熱點數據文件（磁盤）後，我們可以考慮將數據文件轉移到性能更高的存儲設備上去，或者利用我們上述說的條帶化、RAID等存儲技術來均衡IO負荷。
熱點數據段
從Oracle9.2開始，出現了數據段的概念。每個表和索引都存儲在自己的數據段中。我們可以通過視圖V$SEGMENT_STATISTICS查找物理讀最多的段來找到熱點數據段。通過對熱點段的分析，考慮采用重建索引、分區表等方式來降低該數據段上的IO負荷。
SQL> select owner, object_name, tablespace_name, object_type, value
2 from V$SEGMENT_STATISTICS
3 where statistic_name = 'physical reads'
4 order by value desc;
OWNER OBJECT_NAME
------------------------------ ------------------------------
TABLESPACE_NAME OBJECT_TYPE VALUE
------------------------------ ------------------ ----------
SYS CONTEXT$
SYSTEM TABLE 71
SYS I_CONTEXT
SYSTEM INDEX 70
... ...
另外，我們還可以根據視圖v$session_wait中的P1（熱點段所在的數據文件號）、P2（發生db file sequential read事件的起始數據塊）、P3（數據塊的數量，db file sequential read讀取數據塊數量為1）來定位出熱點段：
先找出文件號、起始數據塊、數據塊數量：
SQL> select p1 "fileid", p2 "block_id", p3 "block_num"
2 from v$session_wait
3 where event = 'db file sequential read';
fileid block_id block_num
---------- ---------- ----------
396 44869 1
然後根據找出的文件號、起始數據塊、數據塊數量來定位出數據段：
SQL> select
2 segment_name "Segment Name",
3 segment_type "Segment Type",
4 block_id "First Block of Segment",
5 block_id+blocks "Last Block of Segment"
6 from dba_extents
7 where &fileid = file_id
8 and &block_id >= block_id
9 and &block_id <= block_id+blocks;
Enter value for fileid: 396
old 7: where &fileid = file_id
new 7: where 396 = file_id
Enter value for block_id: 44869
old 8: and &block_id >= block_id
new 8: and 44869 >= block_id
Enter value for block_id: 44869
old 9: and &block_id <= block_id+blocks
new 9: and 44869 <= block_id+blocks
Segment Name
--------------------------------------------------------------------------------
Segment Type First Block of Segment Last Block of Segment
------------------ ---------------------- ---------------------
CSS_TP_SHMT_QUEUE_ACTIVITY
TABLE 44841 44873
3.2.1.3 調整Buffer Cache
如果系統中即不存在性能有問題的SQL語句，而且所有磁盤的IO負載也比較均衡（不存在熱地磁盤），則我們需要考慮增加Buffer Cache來降低磁盤IO請求。
在8i，主要是根據緩存命中率（Buffer Cache Hit Ratio）來調整buffer cache。當Buffer Cache調整到一定大小，對命中率沒什麼影響了時，就沒有必要在增大Buffer Cache了。可以通過以下語句來查看Buffer Cache命中率：
SQL> select 1-(physical_reads)/(consistent_gets+db_block_gets)
2 from v$buffer_pool_statistics;
1-(PHYSICAL_READS)/(CONSISTENT_GETS+DB_BLOCK_GETS)
--------------------------------------------------
.95628981
在9i中，可以利用statspack report中的Buffer Cache建議部分來調整Buffer Cache的大小。
Buffer Pool Advisory for DB: ICSSPRD Instance: iCSSprd End Snap: 259
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 304 .1 37,715 9.18 5,928,235,496
D 608 .2 75,430 6.88 4,443,709,043
D 912 .3 113,145 5.73 3,699,496,220
D 1,216 .4 150,860 3.87 2,502,670,372
D 1,520 .5 188,575 2.32 1,499,049,228
D 1,824 .6 226,290 1.70 1,099,326,418
D 2,128 .7 264,005 1.41 912,042,579
D 2,432 .8 301,720 1.22 790,925,174
D 2,736 .9 339,435 1.09 703,357,378
D 2,992 1.0 371,195 1.00 645,905,997
D 3,040 1.0 377,150 0.99 636,992,420
D 3,344 1.1 414,865 0.90 583,996,250
D 3,648 1.2 452,580 0.84 542,063,246
D 3,952 1.3 490,295 0.79 508,261,496
D 4,256 1.4 528,010 0.74 480,472,150
D 4,560 1.5 565,725 0.71 455,533,563
D 4,864 1.6 603,440 0.67 434,743,759
D 5,168 1.7 641,155 0.64 416,285,837
D 5,472 1.8 678,870 0.62 400,208,242
D 5,776 1.9 716,585 0.60 385,785,401
D 6,080 2.0 754,300 0.57 365,597,932
-------------------------------------------------------------
這裡，Est Physical Read Factor是估算的從磁盤物理讀取次數與從buffer cache中讀取的次數的比值。從意見估算的圖表中，當Buffer Cache的增長對該因子影響不大時，則說明無需在增大Buffer Cache，我們就可以去相應臨界點的大小作為Buffer Cache的大小。上述例子中，我們可以考慮設置Buffer Cache大小為2992M。
在Oracle10g中，引入了新的內存管理特性——自動共享內存管理（Automatic Shared Memory Management ASMM）。基於這一特性，oracle能夠自動根據當前的負荷計算出最優的Buffer Cache大小。關於ASMM，可以參見文章《Oracle內存全面分析》的SGA_TARGET部分。
我們可以采用多尺寸緩沖池技術將熱點數據段（表或索引）KEEP在緩沖池中：
SQL> alter table t_test1 storage(buffer_pool keep);
Table altered.
關於多尺寸緩沖的更多內容，可以參考文章《Oracle內存全面分析》的“多緩沖池部分”部分。
3.2.1.4 Housekeep歷史數據
對於一些被頻繁訪問到的大表，我們需要定期對其做housekeep，將一些不用的、老的數據從表中移除，以減少訪問的數據塊。定期對含有時間軸的Transaction表做housekeep是降低IO負載的重要措施。
3.2.2 db file scattered read
這是另外一個常見的引起數據庫IO性能問題的等待事件。它通常發生在Oracle將“多數據塊”讀取到Buffer Cache中的非連續(分散的 Scattered)區域。多數據塊讀就是我們上述所說的一次讀取“DB_FILE_MULTIBLOCK_READ_COUNT”塊數據塊，前面提到，它通常發生在全表掃描(Full Table Scan)和快速全索引掃描(Fast Full Index Scan)時。當發現db file scattered read等待事件是系統引起IO性能的主要原因時，我們可以采取以下措施對系統進行優化。
3.2.2.1 優化存在Full Table Scan和Fast Full Index Scan的SQL語句
我們可以首先從statspack或者awr報告中的“SQL ordered by Reads”部分中找出存在Full Table Scan和Fast Full Index Scan的Top SQL。因為這些Top SQL往往是整個系統的瓶頸。
從9i開始，我們還可以通過視圖V$SQL_PLAN來查找系統中存在Full Table Scan和Fast Full Index Scan的SQL語句。查找Full Table Scan的語句：
select sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.Operation='TABLE Access'
and p.options='FULL';
查找Fast Full Index Scan的語句
select sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.Operation='INDEX'
and p.options='FULL SCAN';
Full Table Scan通常是由於以下幾個原因引起的：
條件字段上沒有索引;
在這種情況下，如果表的數據量比較大，我們就需要在相應字段上建立起索引。
CBO中，對象的統計數據不正確
CBO中，如果對象的統計數據或者其柱狀圖(Histogram)信息不正確，會導致優化器計算出錯誤的查詢計劃，從而選擇全表掃描。這種情況下，我們要做的就重新分析(Analyze)表、索引及字段。
CBO中，SQL語句中引用到了無法估算統計數據的對象
在PLSQL中，可以建立一些高級的數據類型，如“TABLE OF”、ARRAY等，通過TABLE、CAST函數可以在SQL語句中將這些對象當成表來處理。而這些對象的數據只存在於調用PLSQL的會話中，因此他們沒有相應的統計數據，Oracle會為他們生產一些假的統計數據以完成查詢計劃代價估算。但是基於這些假的數據計算出的查詢計劃一般是錯誤的。我們可以考慮通過提示來強制SQL使用索引或者強制SQL采用RBO優化器。
此外，如果SQL中引用到了臨時表(Temporary Table)也會產生同樣的問題。其原因和解決方法和上面相同。
優化器認為索引掃描代價過高;
在Oracle中存在一個參數optimizer_index_cost_adj，該參數的值代表一個百分數，如果對索引掃描的代價達到或超過全表掃描的代價的這個百分比值時，優化器就采用全表掃描。
optimizer_index_cost_adj是一個全局性的參數，它的合理值是通過長期調整出來的。一般來說是一個介於1到100之間的數字。我們可以按照以下方法來選取optimizer_index_cost_adj的合理值。
先由以下語句得出optimizer_index_cost_adj的一個初始值：
SQL> select
2 a.average_wait "Average Waits FTS"
3 ,b.average_wait "Average Waits Index Read"
4 ,a.total_waits /(a.total_waits + b.total_waits) "Percent of FTS"
5 ,b.total_waits /(a.total_waits + b.total_waits) "Percent of Index Scans"
6 ,(b.average_wait / a.average_wait)*100 "optimizer_index_cost_adj"
7 from
8 v$system_event a,
9 v$system_event b
10 where a.EVENT = 'db file sequential read'
11 and b.EVENT = 'db file scattered read';
Average Waits FTS Average Waits Index Read Percent of FTS Percent of Index Scans
----------------- ------------------------ -------------- ----------------------
optimizer_index_cost_adj
------------------------
1.25 1.06 .041867874 .958132126
84.8
這裡，84.8是我們系統的初始值。在系統經過一段時間運行後，再次運行上面的語句，重新調整optimizer_index_cost_adj的值。經過多次如此反復的調整之後，最終上面語句得出值趨於穩定，這時這個值就是符合我們系統性能需求的最合理的值。
當然這個數值也可以通過statspack的歷史數據來調整，在9i中：
select to_char(c.end_interval_time, 'MM/DD/YYYY') "Date",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 "Average Waits FTS",
sum(b.time_waited_micro)/sum(b.total_waits)/10000 "Average Waits Index Read",
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of FTS",
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of Index Scans",
(sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 "optimizer_index_cost_adj"
from dba_hist_system_event a, dba_hist_system_event b, dba_hist_snapshot c
where a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read'
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id
group by c.end_interval_time
order by 1;
10g中：
select to_char(c.snap_time, 'MM/DD/YYYY') "Date",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 "Average Waits FTS",
sum(b.time_waited_micro)/sum(b.total_waits)/10000 "Average Waits Index Read",
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of FTS",
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of Index Scans",
(sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 "optimizer_index_cost_adj"
from stats$system_event a, stats$system_event b, stats$snapshot c
where a.event = 'db file scattered read'
and b.event = 'db file sequential read'
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id
group by c.snap_time
order by 1;
當optimizer_index_cost_adj的值對於整個系統來說已經是比較合理的值，而某些語句由於該值選擇了全表掃描掃描導致了IO性能問題時，我們可以考慮通過提示來強制語句命中索引。
建立在條件字段上的索引的選擇性不高，結合上一條導致全表掃描;
當索引的選擇性不高，且其代價過高，系統則會選擇全表掃描來讀取數據。這時我們可以考慮通過選擇/建立選擇性比較高的索引，使查詢命中索引從而避免全表掃描。
SQL> create index t_test1_idx1 on t_test1(owner) compute statistics;
Index created.
SQL> set autot trace
SQL> select object_name
2 from t_test1
3 where owner = 'SYS'
4 and created > sysdate - 30;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1883417357
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1715 | 152 (2)| 00:00:02 |
|* 1 | TABLE Access FULL| T_TEST1 | 49 | 1715 | 152 (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)
... ...
SQL> create index t_test1_idx2 on t_test1(owner, created) compute statistics;
Index created.
SQL> select object_name
2 from t_test1
3 where owner = 'SYS'
4 and created > sysdate - 30;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3417015015
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1715 | 2 (0)
| 00:00:01 |
| 1 | TABLE Access BY INDEX ROWID| T_TEST1 | 49 | 1715 | 2 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX2 | 49 | | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
2 - Access("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)
... ...
3.2.2.2 調整DB_FILE_MULTIBLOCK_READ_COUNT
當SQL已經沒有優化余地後，問題仍沒有解決，我們可以考慮調整DB_FILE_MULTIBLOCK_READ_COUNT大小。其作用我們在3.1.2中有做敘述，這裡不再贅述。不過要注意一點就是，DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE是一次IO讀取的傳輸量，它不能大於系統的max_io_size大小。
從Oracle 10gR2開始，如果沒有設置DB_FILE_MULTIBLOCK_READ_COUNT的大小，Oracle會自動為其調整一個默認值，這個默認值的大小與平台最大IO大小(max_io_size)相關(對大多數平台來說max_io_size是1M)，其大小被設置為(max_io_size / DB_BLOCK_SIZE)。
3.2.2.3 將頻繁訪問的全掃描的表CACHE住
由於通過Full Table Scan和Fast Full Index Scan讀取的數據塊會被放置到Buffer Cache的LRU鏈表的LRU端，從而使數據塊盡快從Buffer Cache中移出。因此，對於那些會被頻繁訪問到全掃描的表，且其數據量不大的情況下，我們可以考慮將它們CACHE住。
SQL> alter table t_test1 cache;
Table altered.
對於Fast Full Index Scan的索引對象，則可以考慮把它放置在KEEP池中。
SQL> alter index t_test1_idx1 storage(buffer_pool keep);
Index altered.
利用V$SESSION_EVENT視圖，我們同樣可以找到當前系統中發生全掃描的對象。
SQL> select p1 "fileid", p2 "block_id", p3 "block_num"
2 from v$session_wait
3 where event = 'db file scattered read';
fileid block_id block_num
---------- ---------- ----------
359 152972 16
SQL> select
2 segment_name "Segment Name",
3 segment_type "Segment Type",
4 block_id "First Block of Segment",
5 block_id+blocks "Last Block of Segment"
6 from dba_extents
7 where &fileid = file_id
8 and &block_id >= block_id
9 and &block_id <= block_id+blocks;
Enter value for fileid: 359
old 7: where &fileid = file_id
new 7: where 359 = file_id
Enter value for block_id: 152972
old 8: and &block_id >= block_id
new 8: and 152972 >= block_id
Enter value for block_id: 152972
old 9: and &block_id <= block_id+blocks
new 9: and 152972 <= block_id+blocks
Segment Name
--------------------------------------------------------------------------------
Segment Type First Block of Segment Last Block of Segment
------------------ ---------------------- ---------------------
CSS_TP_SHMT_QUEUE
TABLE 152969 153001
3.2.2.4 利用分區表減少全掃描操作讀取的數據塊數量
前面我們有介紹分區裁剪(Partition Pruning)技術。將表分區，利用分區裁剪技術，在進行全掃描時只會掃描在WHERE條件中出現的分區，從而可以減少全掃描所讀取到的數據塊數量。
3.2.2.5 Housekeep歷史數據
同樣，housekeep不需要的、歷史的數據，減少數據段中的數據塊數量，也能減少全掃描的IO請求次數。
3.2.3 db file parallel read首先，不要被該事件名稱所誤導——它和並行DML或者並行查詢都無關。當從多個數據文件並行讀取數據到非聯系的內存(PGA、Buffer Cache)緩沖中時，會發生該等待事件。它通常發生在Recovery操作或者利用緩沖預提取(Buffer Prefetching)從數據文件並行讀取數據時。
我們可以通過以下語句找出發生db file parallel read等待事件的數據文件和數據塊：
select p1 "fileid", p2 "block_id", p3 "requests"
from v$session_wait
where event = 'db file parallel read';
優化該等待事件的手段可以參考優化db file sequential read等待事件中非SQL優化方法部分。
3.2.4 direct path read & direct path read (lob)
當直接讀取(Direct Read)數據到PGA(而不是到Buffer Cache)中去時，會發生Direct Path Read等待事件。對Lob數據的直接讀有一個單獨的等待事件——direct path read (lob)。
當Oracle設置支持異步IO時，進程可以在提交IO請求後繼續做其他操作，並且在稍後再提取IO請求返回的結果，在提取結果時就產生了direct path read等待事件。
在沒有啟用異步IO時，IO請求在完成之前會被阻塞，但在執行IO操作時並不會產生等待事件。進程稍後回來提取那些已經讀取到的IO數據，這時盡管能夠很快返回，但仍然會顯示direct path read等待事件。
和其他IO等待事件不同的是，對Direct Path Read等待事件要注意以下兩點：
等待次數並不等於IO請求次數;
統計(如statspack報告中)得出的Direct Path Read的等待時間並不一定代表該事件引起的真正等待時間。
事件中的P1、P2、P3參數分別代表：
P1：發生等待事件的數據塊所在文件號;
P2：發生等待事件的數據塊號;
P3：等待事件涉及的連續數據塊數量。
直接讀(Direct Read)請求一般發生在以下幾種情況：
磁盤排序IO(Sort Area不足時，排序用到的臨時數據會被寫到臨時表空間上去，當讀取這些數據時就使用直接讀);
並行查詢;
預讀取(當一個進程認為某個數據塊將很快被用到而發出IO請求時)
Hash Join(Hash Area不足)
IO負載系統中，服務進程處理緩存的速度比系統IO返回數據到緩存的速度更快時
通過視圖V$SESSION_EVENT我們可以找出當前產生等待的會話，再根據會話中正在進行的操作確定導致等待的原因。針對不同的原因，我們可以采取不同的措施減少Direct Path Read等待事件。
3.2.4.1 磁盤排序
首先我們可以考慮優化語句以減少排序操作。排序一般是由以下操作引起的：
o Order By;
o JOIN;
o UNION;
o Group By;
o 聚合操作;
o Select unique;
o Select distinct;
可以嘗試在語句中減少沒必要的上述操作來避免排序操作。另外，創建索引也會引起排序操作。在專業模式(Dedicated)下，排序所占用的內存是從PGA中分配出來的一塊區域，叫Sort Area，由參數sort_area_size控制其大小;在MTS中，排序區是從Large Pool中分配的。當sort area大小無法滿足排序操作要求時，就會占用臨時表空間來存放排序數據，因而產生Direct Path Read等待事件。我們可以通過適當增加該參數來減少磁盤排序操作。
這個參數可以在系統范圍或會話范圍進行修改。對於一些需要做大量排序操作而且又比較獨立的會話(如Create Index)，我們可以在會話級別為其設置比較大的Sort Area以滿足排序需要：
SQL> alter session set sort_area_size = 10000000;
Session altered.
該參數大小一般推薦設置為1~3M。在9i之後，不推薦設置該參數，我們可以通過設置PGA_AGGREGATE_TARGET進行PGA內存自動管理(設置WORKAREA_SIZE_POLICY為TRUE)。對於PGA_AGGREGATE_TARGET的大小設置，可以參考文章《Oracle內存全面分析》中的PGA_AGGREGATE_TARGET部分。
此外，我們還可以通過以下語句來查找系統中存在磁盤排序的會話及其語句：
SELECT a.sid,a.value, b.name, d.sql_text from
V$SESSTAT a, V$STATNAME b, V$SESSION c, V$SQLAREA d
WHERE a.statistic#=b.statistic#
AND b.name = 'sorts (disk)'
and a.sid = c.sid
and c.SQL_ADDRESS = d.ADDRESS(+)
and c.SQL_HASH_VALUE = d.HASH_VALUE(+)
and value > 0
ORDER BY 2 desc,1;
3.2.4.2 並行查詢
當設置表的並行度非常高時，優化器可能就對表進行並行全表掃描，這時會引起Direct Path Read等待。
在使用並行查詢前需要慎重考慮，因為並行查詢盡管能教師程序的響應時間，但是會消耗比較多的資源。對於低配置的數據庫服務器不建議使用並行特性。此外，需要確認並行度的設置要與IO系統的配置相符(建議並行度為2~4 * CPU數)。在10g中，可以考慮使用ASM。
對於表的並行度，我們不建議直接用ALERT修改表的物理並行度：
ALTER TABLE t_test1 PARALLEL DEGREE 16;
而是推薦針對特定語句使用提示來設置表的並行度：
SQL> SELECT /*+ FULL(T) PARALLEL(T, 4)*/ object_name FROM t_test1 t;
47582 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2467664162
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47582 | 1068K| 42 (3)| 00:00:01
| | | |
| 1 | PX COORDINATOR | | | | |
| | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 47582 | 1068K| 42 (3)| 00:00:01
| Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 47582 | 1068K| 42 (3)| 00:00:01
| Q1,00 | PCWC | |
| 4 | TABLE Access FULL| T_TEST1 | 47582 | 1068K| 42 (3)| 00:00:01
| Q1,00 | PCWP | |
--------------------------------------------------------------------------------
3.2.4.3 Hash Join
Hash Area是用於hash join的內存區域。Hash Area過小會引起Direct Path Read等待。當WORKAREA_SIZE_POLICY為FALSE時，可以考慮增加hash_area_size的大小(建議為sort_area_size大小的1.5倍);當WORKAREA_SIZE_POLICY為TRUE時，可以考慮增加PGA_AGGREGATE_TARGET大小。
3.2.4.4 Direct path read (lob)
為了減少LOB的讀寫時間，通常我們會設置LOB的存儲參數NOCACHE，這時讀取LOB時會引起Direct Path Read (lob)等待事件。但當我們發現Direct path read (lob) 引起了IO性能問題，就需要考慮將那些被經常讀取的LOB字段設置為CACHE。另外，如果操作系統的文件系統有足夠的Buffer Cache時可以考慮將LOB數據段存儲在文件系統上。
3.2.4.5 其他優化措施
當內存資源不足、IO讀取數據到內存效率遠遠低於內存中數據被處理的效率時，會引起Direct Path Read等待事件。作為對上述處理措施的補充，增加內存(PGA)、在確保操作系統支持AIO情況下設置DISK_ASYNCH_IO為TRUE以支持異步IO、采用效率更高的存儲設備都能幫助我們減少Direct Path Read等待。
3.2.5 direct path write & direct path write (lob)
直接寫(Direct Path Write)允許一個會話先將IO寫請求放入一個隊列中，讓操作系統去處理IO，而自身可以繼續處理其他操作。當會話需要知道寫操作是否完成(如會話需要一塊空閒的緩存塊或者會話需要確認內存中所有寫操作都被flush到磁盤了)，會話就會等待寫操作完成從而產生Direct Path Write等待事件。Direct Path Write (lob) 是在對LOB數據段(NOCACHE)直接寫時產生的等待事件。
在沒有啟用異步IO時，IO寫請求在完成之前會被阻塞，但在執行IO寫操作時並不會產生等待事件。進程稍後回來提取那些已經完成的IO操作數據，這時盡管能夠很快返回，但仍然會顯示direct path write等待事件。
和Direct Path Read等待事件相似，對Direct Path Write等待事件也要注意以下兩點：
等待次數並不等於IO請求次數;
統計(如statspack報告中)得出的Direct Path Write的等待時間並不一定代表該事件引起的真正等待時間。
事件中的P1、P2、P3參數分別代表：
P1：發生等待事件的數據塊所在文件號;
P2：發生等待事件的數據塊號;
P3：等待事件涉及的連續數據塊數量。
直接寫請求一般發生在以下幾種情況：
直接數據載入操作(如CTAS、SQL*Loader設置Direct選項等);
並行DML操作;
磁盤排序(排序內存空間不足，數據寫入磁盤);
載入NOCACHE數據段;
對Direct Path Write的優化處理措施基本上和Direct Path Write類似。
3.3 控制文件相關的IO事件
這一類等待事件發生在對控制的IO操作時。對控制文件的IO訪問一般都是由Redo Log文件切換、Checkpoint等(如更新SCN)引起的。因此，對這類事件的優化處理也就主要是對這些操作的調優處理。
3.3.1 control file parallel write
這一等待事件通常發生在一個服務進程在更新所有控制文件時，通常是以下情況：
會話啟動了一個控制文件事務(在提交事務之前更新所有控制文件為最新);
會話提交了一個事務到控制文件;
一個控制文件的條目被修改了，該修改要更新到所有控制文件上去
如果這一事件明顯影響到了系統的IO性能時，可以考慮用以下手段來進行優化：
在保證控制文件的備份數量足夠安全(不會出現控制文件全部丟失)的情況下使控制文件數量最少;
如果操作系統支持AIO，設置數據庫支持AIO;
將控制文件轉移到IO負載比較低的磁盤上去。
3.3.2 control file sequential read
這一等待事件通常發生在對一個單獨的控制的IO讀操作時。通常可能是以下情況：
備份一個控制文件;
RAC中在實例之間共享一個控制文件信息時;
讀取控制文件的頭數據塊或者其他數據塊時。
用以下語句可以找到是訪問哪個控制導致的該等待事件：
select P1 as FileName from V$SESSION_WAIT
where EVENT = 'control file sequetial read' and STATE='WAITING';
我們可以采取以下手段來降低這一等待：
如果操作系統支持AIO，設置數據庫支持AIO;
將控制文件轉移到IO負載比較低的磁盤上去。
3.3.3 control file single write
這一等待事件通常發生在對一個單獨的控制的IO寫操作時。用以下語句可以找到是訪問哪個控制導致的該等待事件：
select P1 as FileName from V$SESSION_WAIT
where EVENT = 'control file single write' and STATE='WAITING';
我們可以采取以下手段來降低這一等待：
如果操作系統支持AIO，設置數據庫支持AIO;
將控制文件轉移到IO負載比較低的磁盤上去。
3.4 Redo Log相關的IO事件
在寫Redo Log時，會發生很多等待事件，大部分和IO相關。其中最重要的要屬“log file parallel write”和“log file sync”。Oracle的後台LGWR進程會等待“log file parallel write”事件而前台進程會等待“log file sync”事件。
3.4.1 log file parallel write
這一等待事件發生在LGWR進程等待完成將Redo記錄寫入Redo Log文件時。在LGWR進程將Log Buffer中的數據寫入Log File時會發生該事件。
當使用了異步IO時，這種寫操作是並行的，否則只會一個接著一個Redo文件的寫入。LGWR進程必須等待所有的Redo Log文件都被寫入。因而Redo Log文件所在磁盤的IO效率就直接影響了該等待事件的總的等待時間。
事件中的P1、P2、P3參數分別代表：
P1：有多少個Redo Log文件在被寫入;
P2：有多少個數據塊被寫入;
P3：IO請求的次數。
降低log file parallel write等待的方法有：
不要使表空間長期處於熱備狀態。當表空間處於熱備狀態時，表空間不再被更新，Redo Log會急劇增加;
將Redo Log文件放在高速存儲設備上，千萬別放在RAID5上，可以考慮放在裸設備上;
Redo log文件所在的磁盤應盡量避免有其他IO操作的存在;
對某些操作，如大批量數據導入，可以設置NOLOGGIN、UNRECOVERABLE選項，或者在SQL語句中使用提示/*+APPEND*/，以減少Redo Log的產生。
在確保Redo Log數據足夠安全(不會發生Log文件丟失)的情況下，盡量減少Redo Log組的成員數;
在配置需要使用到Redo Log的功能時，如Streams復制、LogMiner、邏輯模式的DG，盡量設置為最低級別的補充日志(Supplemental Logging);
適當增加Log_buffer的大小
我們可以按照以下方法來調整Log_buffer的大小，比較Redo Log buffer分配的重試(在請求log buffer時，無足夠buffer，需要重新提交請求)率，如果該比例大於0.1%，我們就需要考慮增加Log_buffer的大小。
select retries.value/entrIEs.value "Redo Log Buffer Retry Ratio"
from V$sysstat retries, V$sysstat entrIEs
where retries.name = 'redo buffer allocation retrIEs'
and entries.name = 'redo entrIEs';
另外，如果系統統計數據中redo log space requests大於0，說明有進程在等待分配Redo Log文件空間，而不是等待Buffer空間。這時我們也需要考慮增加Log_buffer的大小。
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';
但是，Log_buffer的大小不要超過128K*CPU或512K(取兩個數字中最大的一個)數。
3.4.2 log file sync
當Oracle前台進程提交或者回滾事務需要等待提交或回滾完成時會產生該等待事件。部分等待的原因可能是等待LGWR進程將會話事務的Redo記錄從Log Buffer中拷貝到磁盤上去。這時，就會出現前台進程等待Log File Sync，而後台LGWR進程在等待 Log File Parallel Write的情況。
事件中的P1、P2、P3參數分別代表：
P1：發生等待時正在等待哪個Log Buffer數據被寫入Log文件;
P2：無意義;
P3：無意義。
實際上，一個Log File Sync等待事件包含了多個步驟：
1、 如果LGWR空閒則喚醒LGWR進程;
2、 LGWR收集需要寫的Redo記錄並提交IO請求;
3、 等待寫Log的IO完成;
4、 LGWR IO提交處理;
5、 LGWR提交已經完成了寫日志的前台/用戶進程;
6、 前台/用戶進程被喚醒。
如果配置了Data Guard，上述步驟中的第三步還需要將Redo記錄通過網絡寫入到standby數據庫的Redo Log文件中去。
針對不同步驟的等待時間的不同，我們需要采取不同的優化措施：
第二、三步的相關等待數據可以從statspack或awr的“redo write time”統計項獲得;
第三步的等待時間和Log File Parallel Write的等待時間相同;
當系統負載非常高時，第五、六兩步的時間就會很長，因為此時盡管LGWR進程已經通知了前台/用戶進程寫日志已經完成，但是系統負載太高，前台/用戶進程需要等待操作系統安排其運行計劃。
要了解是什麼阻滯了Log File Sync的關鍵是比較Log File Sync和Log File Parallel Write的平均等待時間：
如果他們的等待時間差不多，則說明是Log文件的IO問題(即第三步)導致的Log File Sync等待，我們就需要優化Log文件的IO(如上一節所述的方法);
如果Log File Sync的等待時間遠遠大於Log File Parallel Write的等待時間，則說明Log File Sync是由於在提交或回滾時的其他Redo Log機制(非IO原因)引起的，如Latch Free、LGWR wait for copy等log buffer相關的latch沖突。
可以用下面的語句來獲取Log File Sync和Log File Parallel Write的平均等待時間的比值：
select (sum(decode(name, 'redo synch time', value)) / sum(decode(name, 'redo synch writes', value)))
/ (sum(decode(name, 'redo write time', value)) / sum(decode(name, 'redo writes', value)))
as sync_cost_ratio
from v$sysstat
where name in ('redo synch writes', 'redo synch time', 'redo writes', 'redo write time');
我們還可以采取以下調優手段來降低Log File Sync等待：
按照上一節中的方法減少Redo Log的產生、提供Redo Log的IO效率、減少Redo Log與其他IO的沖突;
將一些小事務合並成批量事務，以減少提交和回滾次數。
3.4.3 log file single write & log file sequential read
Log file single write只會發生在打開或關閉一個Redo Log文件後，向文件頭寫入相關信息時。因為文件頭的信息中包含了文件號，因此文件頭信息不會並行寫入多個文件，而是單獨一個個寫入，因而其等待時間不會被統計到log file parallel write之中。
事件中的P1、P2、P3參數分別代表：
P1：寫入的Redo Log文件號;
P2：寫入的數據塊號;
P3：寫入的數據塊數。
當進程從Redo Log文件中讀取redo記錄時會產生log file sequential read等待事件，如Arch進程讀取Redo Log數據。
事件中的P1、P2、P3參數分別代表：
P1：讀取的Redo Log文件號;
P2：開始讀取的數據塊號;
P3：讀取的數據塊數。
當Redo Log文件存在IO問題時，以上兩個等待事件通常都會和log file parallel write等待事件同時出現。因而可以通過在3.4.1中提到的提高Redo Log文件IO效率、減少Log文件IO沖突的方法來減少這兩個等待。
3.4.4 log file switch completion & switch logfile command & log file switch (clearing log file)
當在產生Redo Log時需要等待LGWR切換Log文件時，會產生log file switch completion等待事件;而switch logfile command則是等待DBA手工執行的切換日志的命令：
SQL> alter system switch logfile;
System altered.
日志切換由以下步驟組成：
從控制文件獲取下一日志文件的文件號;
獲取Redo Copy和Redo Allocation的Latch;
清空Redo，將buffer中的Redo記錄寫入Log文件中去;
關閉當前Redo Log文件;
更新控制文件，包括：
o 設置下一日志文件為當前日志文件;
o 設置之前的日志文件為INACTIVE;
o 如果在Archive模式下，將之前文件加到歸檔文件列中;
o 打開新的日志文件組中的所有文件;
o 將SCN寫入文件頭;
o 打開允許產生Redo Log的開關。
在等待上述的第三步時，則會產生log file switch (clearing log file) 等待事件。
我們可以用以下語句查看當前的日志文件：
SQL> select GROUP#, ARCHIVED, STATUS from v$log;
GROUP# ARC STATUS
---------- --- ----------------
1 NO INACTIVE
3 NO INACTIVE
2 NO CURRENT
我們可以采取前述方法提高Redo Log文件IO效率來降低這三個等待事件。此外，我們還可以增大Log文件大小，降低日志切換頻率(一般來說，在系統運行高峰期以20~30分鐘切換一次為佳)。通過以下語句可以查詢日志的切換記錄及其切換間隔時間：
SQL> SELECT to_char(b.first_time, 'YYYY-MM-DD HH24:MI:SS') as swtich_time,
2 (b.first_time - a.first_time) * 24 as "switch_interval(hr)"
3 FROM v$log_history a, v$log_history b
4 WHERE a.SEQUENCE# + 1 = b.SEQUENCE#
5 AND ROWNUM <= 10
6 ORDER BY 1;
SWTICH_TIME switch_interval(hr)
------------------- -------------------
2007-08-25 00:28:59 2.3975
2007-08-25 06:04:53 5.59833333
2007-08-25 12:15:52 6.18305556
2007-08-25 21:58:13 9.70583333
2007-08-25 23:50:39 1.87388889
2007-08-26 00:28:42 .634166667
2007-08-26 08:32:04 8.05611111
2007-08-26 17:58:05 9.43361111
2007-08-26 23:26:57 5.48111111
2007-08-27 07:21:35 7.91055556
... ...
另外，從Alert日志中，我們也可以找到日志切換記錄。
... ...
Beginning log switch checkpoint up to RBA [0x18106.2.10], SCN: 0x0003.93b3fb7d
Thread 1 advanced to log sequence 98566
Current log# 7 seq# 98566 mem# 0: /export/home/icssprd/data/data02/iCSSprd_redo_07a.rdo
Current log# 7 seq# 98566 mem# 1: /export/home/icssprd/data/data18/iCSSprd_redo_07b.rdo
Mon May 28 12:35:14 2007
ARC0: Evaluating archive log 2 thread 1 sequence 98565
ARC0: Beginning to archive log 2 thread 1 sequence 98565
Creating archive destination LOG_ARCHIVE_DEST_1: '/export/home/iCSSprd/admin/arch/1_98565.dbf'
Mon May 28 12:36:47 2007
Completed checkpoint up to RBA [0x18106.2.10], SCN: 0x0003.93b3fb7d
Mon May 28 12:38:02 2007
ARC0: Completed archiving log 2 thread 1 sequence 98565
Mon May 28 12:41:26 2007
Beginning log switch checkpoint up to RBA [0x18107.2.10], SCN: 0x0003.93b4a3a6
Thread 1 advanced to log sequence 98567
Current log# 8 seq# 98567 mem# 0: /export/home/icssprd/data/data10/iCSSprd_redo_08a.rdo
... ...
3.4.5 log file switch (checkpoint incomplete)
當在做日志切換時，同時會做checkpoint，如果此時有其他checkpoint正在進行時，需要等待正在進行的checkpoint完成，此時就會產生log file switch (checkpoint incomplete)等待事件。通常發生這一等待事件時，日志切換的時間都比平時更長。
要降低該等待事件，就需要降低日志切換時引起的checkpoint遇上系統中其他checkpoint的幾率。我們可以通過以下方法來進行調優：
增加Redo Log文件的大小，使日志切換頻率降低;
增大參數Log_checkpoint_interval大小，該參數設置系統兩次checkpoint之間Redo Log數據塊(該數據塊的大小由操作系統的數據塊大小決定)的數量。但是Oracle會限制這些數據塊總的大小要小於最小log文件的90%。如最小log文件大小為100M，操作系統的數據塊大小為512K，則Log_checkpoint_interval要小於(100 * 90% / 0.5) = 180
3.4.6 log switch/archive & log file switch (archiving needed)
log switch/archive等待事件在會話等待所有Archive線程對當前log文件Archive操作完成。當LGWR進程切換日志時，如果要切入的日志還沒有被Archive，需要等待其被完成Archive，這時會產生log file switch (archiving needed)等待事件。這時，在alert log中我們還能發現以下信息：
Thread 1 cannot allocate new log, sequence 9556
All online logs needed archiving
這兩個事件只有數據庫在Archive模式下才會出現。說明Archive操作太慢。對這兩個事件的調優主要是針對Archive設置的調優。
要提高Archive的效率，可以采取以下方法：
1) 調整Redo Log文件的個數和大小
大多數情況下，Redo Log文件的個數越多、大小越大，就能讓歸檔進程有更多時間做Archive。如果Redo記錄急劇增加，可以考慮加多log文件數量，這樣能使歸檔進程有更多時間均衡歸檔過程。但是，如果ARCH進程無法跟上LGWR進程的處理速度時，增加Log文件數量就於事無補了。
2)調整checkpoint的間隔和效率
增大checkpoint的間隔也可以使歸檔進程有更多時間來處理歸檔。增大參數log_checkpoint_interval可以增大checkpoint的間隔。另外，增加DBWR進程數量、配置AIO都可以提高checkpoint的處理效率。
3)配置多ARCH進程
通過參數log_archive_max_processes可以配置最大ARCH進程數量。我們可以做一個腳本來執行'alter system archive log all;'命令，然後設置一個作業以固定間隔時間來執行該腳本。這個命令可以強制歸檔所有未歸檔的日志文件。這可以幫助均衡ARCH進程的歸檔處理負擔。
4)調整Archive進程
增加Archive進程的buffer大小可以提高Archive效率，其大小是由參數log_archive_buffer_size控制的。該參數的初始設置為4K，最大可以增加到128K。但是，要注意，增加該參數雖然可以提供Archive效率，但是可能會使系統的整體性能下降;
另外，我們還可以通過增加Archive進程的buffer數量來提高Archive效率。Buffer數量由參數log_archive_buffer控制，最大為8.
5)減少系統的IO沖突、提供系統IO效率
系統整體的IO性能及沖突問題也會影響到Archive的效率。我們可以用前面介紹的方法來減少系統中存在的IO沖突、提高整體IO效率來提高Archive的效率。
3.5 Buffer Cache相關的IO事件
Buffer Cache是影響Oracle IO的重要因素。這裡要解決的幾個等待事件都是涉及到DBWR進程和IO從屬進程(Slave)的Buffer Cache操作引起的等待事件。
3.5.1 db file parallel write
該事件和並行DML無關。這個等待事件出現在當DBWR進程提交了多IO請求來並行將Buffer Cache中的髒數據寫入磁盤中後，等待所有提交的IO請求完成。通常是由於操作系統的IO系統導致的該事件的阻滯。
事件中的P1、P2、P3參數分別代表：
P1：(9.2.0.5之前)寫入數據的文件號/(9.2.0.5之後)請求次數;
P2：(9.2.0.5之前)寫入的數據塊號/(9.2.0.5之後)請求中斷的次數;
P3：(9.2.0.5之前)請求次數/(9.2.0.5之後)請求發生了Timeout的時間
這一等待事件一般不會顯著影響用戶會話。但是當用戶會話中有很高的“write complete waits”或“free buffer waits”事件的等待時間時，說明該事件已經影響到了用戶會話。有時候這一事件對操作系統IO的影響也會影響到進程從同一磁盤讀取數據的等待時間。
解決該事件的關鍵在於減少相關磁盤的IO沖突。如果這事件已經影響到用戶會話，我們需要結合其他等待事件信息，考慮采取均衡熱地磁盤負載、提高存儲設備IO效率、增加checkpoint間隔、增大Redo log文件等方法來減低該事件。
3.5.2 db file single write
當DBWR進程請求修改數據文件頭，在等待IO請求完成時，會出現db file single write等待事件。
事件中的P1、P2、P3參數分別代表：
P1：寫入數據的文件號;
P2：寫入的數據塊號;
P3：寫入的數據塊數(一般為1)。
解決這一等待事件的關鍵還是要處理好磁盤的IO沖突問題，特別是發生該事件所在的磁盤。通過相關SQL的調優等手段來降低事件發生的磁盤的IO、采用更高效率的存儲設備、均衡磁盤IO負載等方法是降低這一等待事件的主要方法。
3.5.3 write complete waits
當會話對一個正在被寫寫入磁盤的Buffer數據塊發出請求時，需要等待其被寫入磁盤完成，這時就會產生write complete waits等待事件。
事件中的P1、P2、P3參數分別代表：
P1：要寫入數據的文件號;
P2：要寫入的數據塊號;
P3：無意義
提高Buffer Cache髒數據寫入磁盤的效率、提高整體IO效率是降低該等待事件的主要方法：
配置數據庫支持AIO;
增加db_writer_processes(支持AIO時)或者db_io_slaves(不支持AIO時)大小以增加DBWR進程;
其他提高IO效率(如采用裸設備等)、減少IO沖突的方法
3.5.4 free buffer waits
當會話在Buffer Cache中找不到空閒buffer塊，或者在沒有空閒buffer塊來建立一致性讀時，就會產生free buffer waits等待事件。
事件中的P1、P2、P3參數分別代表：
P1：要讀取數據的文件號;
P2：要讀取的數據塊號;
P3：10gR1之前無意義，10gR1後表示在Buffer Cache中LRU和LRUW列表的SET_ID#
這一等待事件通常表示Buffer Cache不足或者從Buffer Cache中將髒數據寫入磁盤的效率太低。要降低該等待事件，我們就需要分別從這兩方面入手：調整Buffer Cache的大小(如根據statspack的建議器來設置);按照我們前述的方法來提高存儲設備的IO效率。
4 結束語
最後要說的是，一旦數據庫服務器出現了IO問題後，首先要檢查操作系統本身的IO系統是否有問題，然後再確認是否是Oracle出現了IO問題。
其次要注意的一點是，上述等待事件在系統出現一定的等待次數對於系統來說是正常的，我們要解決的是對系統IO影響最大的一個或幾個等待事件，而不是全部事件。
總的來說，要調整Oracle中出現的IO性能問題，我們有兩種手段：一種是針對特定等待事件的相應方法，如相關SQL語句的調優、相關參數的修改;另外一種是通過提升整體IO效率、減少IO沖突來降低IO等待，如均衡IO負載、使用效率更高的存儲設備、激活AIO和重新分布對IO有不同要求的文件。
事實上，數據庫的性能問題大多數是由應用引起的，而其中大部分問題都是Top SQL造成。因此，這裡要說的一句題外話就是：SQL調優是每一個DBA必須具備的最基本的技能。因為很多時候無論采用什麼手段、什麼工具來定位問題，通過各種內部機制來分析問題，但最終解決問題的手段就是SQL調優。
5 參考文章
1、 www.HelloDBA.com
2、 Metalink.Oracle.com
3、 Oracle OTN
4、 Oracle Concept
5、 Oracle Database Performance Tuning Guide
