一篇關于調優的概念性文章

今天看了一篇關于調優的概念性文章(61998.1： Introduction to Tuning Oracle7 / Oracle8 / 8i / 9i )。

文章的目的是為了可以快速知曉執行過程中哪裡花費了較多的時間，而不是關注不同統計資料的含義。更加關注實際。

1. 為什麼需要調優tuning？

        通常我們關注的是使用者響應時間或批量作業的處理時間。也許是某天當中若幹時間點出現性能問題，或者性能問題一直如此。也許僅僅一些使用者函數執行較差，或者所有的函數執行地都慢。大多數系統都是用來服務使用者的，如果能從系統的視角了解使用者，就需要做出許多工作。

        确信能夠從使用者視角了解所有問題-花時間向使用者了解，確定清楚真實的問題。例如，如果兩條大多數普通使用者交易都會用到的語句出現了較差的執行計劃，就說“系統任何時候都慢”，但是更細緻的調查後發現，一些操作卻可以獲得正常的響應時間，這樣的情況屢見不鮮，這就需要我們在工作的過程中，充分從客戶的角度了解問題的細節，也要注重客觀，這樣才能做出一個有說服力的判斷。

2. 根據時間來判斷性能。

         一旦開始根據響應時間或批處理時間思考性能問題，就可以有基礎地判斷任何建議的改變所帶來的影響。調優的基礎就是确定哪裡花費的時間需要可以更快。一旦知道了哪裡花費了時間，那麼就可以判斷影響的時間，以及潛在節省的時間。以下四個步驟是可以重複執行的：

1. 确定哪裡花費了時間。

2. 挖掘出原因的細節。

3. 嘗試各種可以節省時間的方法。

4. 應用這些改變，然後重複第一步。

3. Oracle能夠告訴我們哪些關于時間的資訊？

         一個用戶端應用程序通常與使用半雙工協定的Oracle影子程序進行通話。每個影子程序可能是如下三種狀态之一：

a. IDLE-等待處理。

b. 運作時代碼-例如正在使用CPU或正在運作隊列中。

c. 等待-i. 等待一些可利用資源。

             ii. 等待請求的完成。

為了明确以上這些，下面是一些舉例：

a. IDLE

使用者影子程序正等待資料包，使用者需要告訴他們接下來做什麼，或者提供資訊允許他們繼續執行。

b. 運作時代碼

程序處于等待使用CPU的運作隊列，需要使用CPU資源。但Oracle不知道其正在使用CPU還是僅僅處于運作隊列中。

c. i. 等待可利用資源。例如enqueue(鎖)或闩。

    ii. 等待事件的完成。例如一次IO讀請求，或者等待LGWR完成redo日志的磁盤寫。

4. 基礎點。

        init.ora參數TIMED_STATISTICS必須設定為TRUE，才能知道上面提到的各種狀态所用時間。

        Oracle記錄的時間僅精确到10毫秒(1/100秒)。Oracle 9i中的一些時間已經可以支援微秒級。

5. 等待事件。

        僅知道上面三種類别的耗時是不夠的，是以Oracle還有一組”等待事件“對應于'a'和'c'，記錄CPU使用率對應于'b'。用Oracle影子程序生命周期花費的些許時間就可以很好地證明以上說法。

狀态 含義

IDLE 等待”SQL*Net message from client“。接收語句請求”parse/execute“的SQL*Net包。

ON CPU 解析SQL*Net包。

WAITING 等待”latch free“獲得一個”library cache“闩。

ON CPU 在共享池中搜尋SQL語句，找到比對的，釋放闩，建立與共享遊标的連結，等等。開始執行。

WAITING 等待”db file sequential read“，因為我們需要一個目前不在buffer cache的資料塊。例如：等待一次IO的完成。

ON CPU 讀到資料塊，開始執行。建構SQL*Net包，将包含第一行資料的資訊傳回給使用者。

WAITING 等待”SQL*Net message to client“确認SQL*Net包已經送達。

IDLE 等待”SQL*Net message from client“進行下一次請求。

        如果我們給以上各步驟配置設定時間，那麼就能得到調優的效果。在獨立語句級别，session級别或系統級别，Oracle都能提供一些有用的資訊。

執行個體用時：

V$SESSION

V$SESSION_WAIT

語句級别：

SQL_TRACE or DBMS_SUPPORT.SET_TRACE output

session級别：

V$SESSION_EVENT

V$SESSTAT

系統級别：

V$SYSTEM_EVENT

V$SYSSTAT

6. 時間視角的觀點。

        如下SQL提供每個session目前的狀态：

SELECT sid, status FROM V$SESSION; 

        可以顯示每個session的狀态是ACTIVE還是INACTIVE。INACTIVE的session通常正在等待來自用戶端的請求。通常我們可以在V$SESSION_WAIT中看到WAIT_TIME參數值是0的”SQL*Net message from client“。

        對于ACTIVE的session：

SELECT sid, wait_time, event FROM V$SESSION_WAIT;

        可以顯示執行個體中每個session狀态的時間。

WAIT_TIME 含義

0 目前正在等待特定事件的session。(Oracle 9.0.1版本中，session目前未等待時，V$SESSION_WAIT顯示

WAIT_TIME是0，但是由于Bug：2117360，可能CPU是有使用的。V$SESSION_WAIT.STATE會顯示一個

除"WAITING"外的值。)

!=0 正使用CPU的session。列出的事件是session等待的最後一個事件。WAIT_TIME指出session等待最後這個事件的

時間。

-1

session需要等待一小段時間。

-2

不知道等待多久(TIMED_STATISTICS=false)。

>0

以10ms計算的實際等待時間。

        總會有一些session一直使用CPU。如果執行上面的語句，SELECT的結果會顯示”on CPU“(WAIT_TIME!=0)。

        如果一個應用使用多個session或執行session轉換，那麼WAIT_TIME就可能是非0值，但session狀态是INACTIVE。這樣的sesson通常不是用戶端目前的session，是以V$SESSION_WAIT顯示session等待的最後事件，但該session目前是INACTIVE狀态。

        V$SESSION_EVENT和V$SYSTEM_EVENT視圖顯示了session級和系統級中每個等待事件花費的總時間。