oracle IO 問題解析

資料庫的作用就是實現對資料的管理和查詢。任何一個資料庫系統，必然存在對資料的大量讀或者寫或者兩中操作都大量存在。IO問題也往往是導致資料庫效能問題的重要原因。在這篇文章中，主要幫助大家在理解Oracle的讀寫操作機制的基礎上，靈活解決遇到的各種常見的IO問題。
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提示。

 

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