彬綺部: [Oracle][Tuning]Oracle10g效能調校參考手冊#1

今天看到一篇相當不錯的Oracle效能調教手冊，轉帖至此收藏~

本文轉貼自 <http://www.dotblogs.com.tw/ricochen/archive/2010/01/07/12884.aspx>

感謝Oracle大師William的指導，如果沒有你的校正也就不會有整份手冊。

一、文件說明

本文件說明如何監控診斷Oracle 10g DB效能。閱讀本文件前，必須先對Oracle DB架構有所瞭解（如Memory結構、Oracle背景程序等）。而本文範圍主要在單一instance Oracle DB運作上，不包含DB備份、RAC架構以及OS層面的監控和調整。

監控調整DB可以使用SQL指令或Enterprise Manager（簡稱EM）圖形介面來完成，由於EM圖形介面的方便性和一致性，在此將以EM進行說明。

二、監控DB And SQL

2.1統計資料

DB運作和監控、效能調整都仰賴DB統計資訊，需確定統計資訊有更新。DB統計資料由GATHER_STATS_JOB這個系統的排程進行收集，其工作窗口屬於MAINTENANCE_WINDOW_GROUP，在週一~週五PM10:00開始啟動收集DB統計，有8小時工作窗口，週末工作窗口整天都可執行，請確定統計收集排程在目標DB中正常運作：

統計資訊不正確， DB將無法有良好效能。

另外，為確保DB監控程序能收集到足夠資訊，檢查初始參數與設定值符合：

STATISTICS_LEVEL=TYPICAL or ALL（預設為TYPICAL）

TIMED_STATISTICS=TRUE（預設為TRUE）

設定不正確，統計資訊會收集不完全，無法進行問題診斷和調整。

2.2 DB Time

Oracle DB使用DB time來記錄執行所花的時間，並提供一個一致性的比較單位。DB time是由執行的CPU時間+等待時間組成：DB time = DB CPU time + DB wait time。

當DB time中的CPU time比重太高，代表可能需要進行SQL tuning，如wait time過高，則是可能有資源爭用需進行instance/RAC tuning：

下圖顯示一個session送出request並且取得DB回應的時間。整個時間可以包含兩個部分：網路傳送/接收資料的時間，DB處理花費的時間。

因DB會同時服務許多使用者，所以DB全部的DB Time是由所有User花費的DB Time所累加。由於User可能連上DB卻沒有做任何事情（處於Idle狀態），計算DB Time時，Idle等待時間不會計算到DB Time中。此概念可以用下圖表示：

DB Time根據所執行的工作區分成不同統計項目，這些統計項目是有階層性的：

例如：SQL execute elapsed time就包含Repeated bind elapsed time，以DB time觀看統計資料或報表時，部分項目會同時顯示出來，故DB time結果可能會重複加總，導致DB time大於實際的DB time。

2.3 Wait Event

Oracle根據不同的等待狀況定義了許多事件來代表這些狀況，例如：不透過SGA方式直接讀取/寫入資料的方式被定義為direct path read/write。當session進行等待時，會依照等待情境對應到一個等待事件中，透過這些事件的統計可以知道系統等待時間都花在哪，是否有瓶頸存在。

等待事件會依照特性進行分類：

這些分類會顯示在EM Performance tab的Average Active Sessions畫面

點選進入不同事件分類中，可以得到此類別中實際發生事件的名稱並列出最常發生的SQL和Session：

CPU Used

SQL和Session都可以進一步點選進入，以不同觀點交互驗證效能瓶頸。

2.4 Metrics and Alerts

Metric代表累計統計值的變化率，可以是系統產生的或使用者自己定義，使用者自己定義的Metric可以用SQL方式設定，此時要監控什麼目標和警戒值都是由SQL查詢對象和結果決定。Alert為一個事件，當監控的Metric超過警戒值時產生，產生的Alert內容也會在ADDM中顯示。

使用Metric的好處在於快速瞭解時間與統計變化，減少人工比對的麻煩：

EM中All Merics連結可以查看所有的Metrics：

Metrics會依照分類顯示，展開可以看到實際的項目：

Thresholds欄位顯示項目是否可以設定警戒值，Collection Scheduler說明多久收集一次資訊。點選項目連結可以查看每項Metric詳細內容：

圖表中呈現Metric的值變化率、最高、最低、平均值，如有設定警戒值還會顯示不同顏色區間，而超過警戒值的Alert也會顯示在此， ADDM的警告中也能看到。

Metric需與警戒值搭配才能產生Alert，警戒值在Manage Metrics中可以設定：

使用Metrics能得知潛在有問題的項目以及數值的變化率，但無法判斷這問題是否發生於局部session或整個系統，而Histograms圖表可以顯示這樣的資訊，Histograms圖表可以從EM中的Performance Tab的Average Active點入：

進入Wait Event的項目後，再點選想要看的事件：

Histograms圖表Y軸為該事件的等待次數，X軸為等待時間，單位為ms，每一刻度為前一刻度的2次方直到4K ms：

Histograms圖表中需要留意的是等待時間長且次數多的狀況，這類的等待會造成效能問題。

2.5 找出問題SQL

監控問題SQL可以由：

1. EM Performance tab的Top Active連結中觀察：

2. ADDM整段結果:

3.使用者的回報

4.AWR Report

觀察得知。

DB執行的SQL都有一個SQL ID辨別，在EM中取得的SQL可以直接點選連結進入SQL Details頁面取得詳細資訊，但由使用者回報的SQL則需要查詢SQL內容取得SQL ID，下面SQL可以由SQL字串取得SQL ID：

select sql_id,sql_text,sql_fulltext from V$SQLAREA where sql_text like '%PODT%'--條件

請注意，sql_text欄位只會列出SQL前1000個字元，而選出完整的SQL句子可以查看sql_fulltext（clob類型），確認是使用者回報的SQL。

取得SQL ID後需進入SQL Details頁面，用SQL ID進行切換到該SQL的詳細內容：

SQL Details頁面中，相同的SQL句子可能因為執行的context或其它因素會導致不同的執行計畫，故在畫面上可以拉選不同的Plan：

不同的Plan對應下方的tab資訊也會不一樣，在少數狀況下，同一句SQL如果存在不同執行計畫，則必須確定發生問題SQL使用的哪一個執行計畫，通常可以由SQL來源程式碼、SQL最後載入時間與執行次數等判斷可真正造成問題的執行計畫，如果有正在使用該SQL的session，也可以透過正在執行該SQL的session內容做推測。

SQL Details頁面下方有四個tab，內容對於診斷SQL問題十分有用，各tab說明如下：

Statistic：執行的統計資訊，包含SQL所在程式、執行次數、耗用時間，由這些資訊可以評估SQL是否有效能問題。