[Oracle][Tuning]Oracle10g效能調校參考手冊#1

[Oracle][Tuning]Oracle10g效能調校參考手冊#1

感謝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中正常運作:

clip_image002

clip_image002[5]

統計資訊不正確, 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: 

clip_image002[7]

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

clip_image002[9] 

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

clip_image002[11]

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

clip_image002[13] 

例如: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進行等待時,會依照等待情境對應到一個等待事件中,透過這些事件的統計可以知道系統等待時間都花在哪,是否有瓶頸存在。

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

clip_image002[15]

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

clip_image002[17] 

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

CPU Used

clip_image002[19]

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

image 

image

image

image

 

2.4 Metrics and Alerts

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

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

clip_image002[21] 

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

clip_image002[23]

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

clip_image002[25] 

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

clip_image002[27]

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

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

clip_image002[29] 

clip_image002[31]

clip_image002[33]

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

clip_image002[35] 

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

clip_image002[37]

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

clip_image002[39] 

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

 

2.5 找出問題SQL

監控問題SQL可以由:

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

clip_image002[41]

2. ADDM整段結果:

clip_image002[43]

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的詳細內容:

clip_image002[45] 

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

clip_image002[47]

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

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

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

clip_image002[49]

Active:顯示目前執行此SQL的Session。可以反向觀察Session是否是因為這SQL執行而有問題。

clip_image002[51]

Plan:顯示SQL執行計畫,可看執行計畫內的操作是否合理,例如:有無不當的Full Table Scan、Join的方式和順序是否有異常。執行計畫可以使用Cost欄位來分析計畫的成本:

clip_image002[53]

Tuning Information:
SQL句子如果有用Oracle SQL Tuning Advisor調整或建立outline(穩定執行計畫用的資訊,避免環境改變造成SQL執行計畫變異)則會在此Tab中顯示,並可以點選連結查看過去調整的細節: 

clip_image002[55]