關於我

我的相片
用心思考、保持熱情,把工作上的經驗作分享與紀錄。希望能夠跟大家一起不斷的成長~

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

    clip_image001

    clip_image002

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

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

    clip_image004

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

    clip_image005

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

    clip_image006

    例如: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_image007

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

    clip_image008

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

    CPU Used

    clip_image009

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

    clip_image010

    clip_image011

    clip_image012

    clip_image013

    2.4 Metrics and Alerts

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

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

    clip_image014

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

    clip_image015

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

    clip_image016

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

    clip_image017

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

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

    clip_image018

    clip_image019

    clip_image020

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

    clip_image021

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

    clip_image022

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

    clip_image023

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

    2.5 找出問題SQL

    監控問題SQL可以由:

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

    clip_image024

    2. ADDM整段結果:

    clip_image025

    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_image026

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

    clip_image027

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

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

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

    clip_image028

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

    clip_image029

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

    clip_image030

    Tuning Information:

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

    clip_image031

     

沒有留言:

張貼留言