Oracle數據庫診斷性能問題

　　使用擴展SQL跟蹤數據來了解是什麼在耗費這麼長的時間
　　
　　假如有一天你開車去上班但最後還是沒能及時參加一個重要會議你無法將你的革命性的想法呈現給客戶所以他們也不會采用你的拖拖拉拉使你感到沮喪你發誓決不再犯同樣的錯誤那麼為了不再發生類似情況你怎麼判斷問題的原因呢？按照下面這個列表進行檢查怎麼樣？
　　
　　檢查汽車外表是否有缺陷因為外表有缺陷會使汽車的最高速度降低%或更多
　　
　　檢查車輪定位因為外傾角後傾角或前束角不合適都會導致汽車的操縱不靈活並且耗費時間
　　
　　檢查發動機以確保達到額定馬力的%或更高如果不是這樣則要考慮重裝或更換發動機
　　
　　不你可能不會采用這種檢查方法那樣太可笑了你可能會以完全不同的方式來判斷問題之所在可能只是問你自己一個簡單的問題什麼事情讓我花了這麼長時間？
　　
　　從這個角度出發問題就迎刃而解了如果開車需要分鐘而你在會議開始前分鐘才動身那麼下次就要提前分鐘動身如果因為交通擁堵浪費了分鐘那麼下次要麼再早一些動身換條路線要麼更仔細地查看早點的路況報告如果是你迷了路結果浪費了分鐘去兜圈子那麼下次你大概就要事先看看地圖如此等等
　　
　　我感到奇怪的是那些擅長解決日常性能優化問題的數據庫專業人員在工作中卻使用完全不同的方法來解決數據庫性能問題許多數據庫調優人員從來不問是什麼讓這個程序運行了這麼長時間？相反他們會參考檢查內容清單並試圖阻止錯誤發生
　　
　　檢查所有Oracle塊請求是否都由數據庫緩存提供服務
　　
　　檢查是否有全表掃描
　　
　　檢查所有排序是否都在內存中進行
　　
　　檢查重做日志是否與其他所有數據庫文件進行了適當的隔離等等
　　
　　對於某些工作來說使用檢查內容清單也許很好但是對於判斷性能問題這樣的工作試圖確定理論上可能會出錯的每一件事從而對這個問題進行處理的做法的效率會很低更有效的方法就是找到這個簡單問題的答案
　　
　　是什麼花了這麼長時間？
　　
　　用於優化Oracle程序的好的策略就如同日常生活中用到的策略就像這樣
　　
　　使用專門的儀器來測定程序的性能從而監視運行速度慢的程序
　　
　　為運行慢的程序創建資源描述把程序的響應時間細分為幾種有用的類型
　　
　　通過首先處理響應時間最長的部分來縮短程序的響應時間
　　
　　當你了解了若干技術細節之後這個方法就非常簡單了如果你真的這樣做那麼每次你都能獲得一個有用的方法久而久之你將能在進行性能改進之前預知其結果
　　
　　跟蹤
　　
　　如果你有用於收集程序中每個執行步驟的時間統計信息的高級工具那就用吧但只收集匯總數據（如通過對系統全局區[SGA]或其基礎共享存儲段采樣獲得的數據）的工具對於某些類型的問題就不適合
　　
　　使用昂貴的監控工具時最常見的匯總錯誤是它們會跨整個Oracle數據庫實例來匯總某一給定時間間隔內資源的使用情況但是運行速度慢的程序實際上可能不受資源爭用問題的影響而這個問題卻完全控制著系統中一些不太重要的程序的性能
　　
　　即便是那些在Oracle數據庫會話級上匯總信息的工具在診斷一些重要的問題類型時也存在著缺陷例如假設一個程序運行分鐘調用了次Oracle SQL*Net message from client 這一等待事件會話等待該事件的總用時為分鐘這意味著會話對SQL*Net message from client事件的等待時間平均為秒但是單從匯總數據看你無法知道這次調用是否每次都用秒還是這些調用中也許有一個用了分鐘而其余次調用每次只用秒這兩種情況需要進行完全不同的處理
　　
　　在這種情況下最能為你提供幫助的診斷數據是Oracle的擴展SQL跟蹤數據擴展SQL跟蹤文件按時間順序顯示了Oracle數據庫內核在指定時間內所完成工作的逐條記錄收集擴展SQL跟蹤數據幾乎是免費的最大的花銷是存儲每一個需要引起注意的跟蹤文件所需磁盤空間（很少超過幾兆字節）的費用
　　
　　跟蹤自己的代碼如果能訪問程序的源代碼則打開其擴展SQL跟蹤就非常容易首先必須確保會話的TIMED_STATISTICS和MAX_DUMP_ FILE_SIZE參數設置正確
　　
　　alter session
　　set timed_statistics=true
　　alter session
　　set max_dump_file_size=unlimited
　　
　　如果沒有設置TIMED_STATISTICS=TRUE則數據庫內核將把值而不是真正的持續時間發送到跟蹤文件中如果對MAX_DUMP_ FILE_SIZE嚴加限制則會在跟蹤文件中生成下面這樣的消息而不是你想要的時間數據
　　
　　*** DUMP FILE SIZE IS LIMITED TO BYTES ***
　　
　　接下來是激活跟蹤有幾種方法可以采用過去的方法是使用ALTER SESSION命令如下所示
　　
　　alter session set events
　　 trace name context forever level
　　/* code to be traced goes here */
　　alter session set events
　　 trace name context off
　　
　　更好的方法是使用DBMS_SUPPORT包來激活擴展SQL跟蹤
　　
　　dbms_supportstart_trace(waits=>true binds=>true)
　　/* code to be traced goes here */
　　dbms_supportstop_trace()
　　
　　請注意DBMS_SUPPORT 沒有文檔說明可能也不是數據庫默認安裝的一部分要了解DBMS_SUPPORT的信息請參考MetaLink ( )
　　
　　跟蹤別人的代碼如果你想跟蹤沒有讀/寫權限的代碼則激活擴展SQL跟蹤就有點麻煩了但也不會難很多你首先要獲得你想跟蹤的會話的V$SESSIONSID和V$SESSIONSERIAL#值然後使用下面的過程調用可以設置所選會話的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE參數
　　
　　dbms_systemset_bool_param_in_session(
　　sid　　 =>
　　serial# =>
　　parnam　=> timed_statistics
　　bval　　=> true)
　　
　　dbms_systemset_int_param_in_session(
　　sid　　 =>
　　serial# =>
　　parnam　=> max_dump_file_size
　　intval　=> )
　　
　　（對於Oracle 以前的版本你可以用ALTER SYSTEM命令處理這些參數）
　　
　　接下來要激活跟蹤有幾種方法可以采用包括下面兩個
　　
　　方法一是使用DBMS_SUPPORT
　　
　　dbms_supportstart_trace_in_session(
　　sid　　 =>
　　serial# =>
　　waits　 => true
　　
　　binds　 => true)
　　/* code to be traced executes during this time window */
　　dbms_supportstop_trace_in_session(
　　sid　　　=>
　　serial　 => )
　　
　　若想激活擴展SQL跟蹤請不要使用名為SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT過程該過程不允許在跟蹤文件中指定等待和綁定的數據
　　
　　第二種方法更為精致但在Oracle數據庫g之前的版本中並不支持這種方法 DBMS_MONITOR包的引入解決了許多復雜診斷數據收集問題這些問題是由連接共享和多線程操作所引起的你可以在Oracle數據庫g中指定要跟蹤的服務模塊或行動而不指定要跟蹤的Oracle數據庫會話
　　
　　dbms_monitorserv_mod_act_trace_enable(
　　service_name　=> APPS
　　module_name　 => PAYROLL
　　action_name　 => PYUGEN
　　
　　waits　　　　 => true
　　binds　　　　 => true
　　instance_name => null)
　　/* code to be traced executes during this time window */
　　dbms_monitorserv_mod_act_trace_disable(
　　service_name　=> APPS
　　module_name　 => PAYROLL
　　action_name　=> PYUGEN)
　　
　　利用DBMS_MONITOR包Oracle可為要跟蹤的特定的業務操作提供完全支持激活或停止診斷數據收集的方法
　　
　　測試擴展SQL跟蹤試一試吧查看第一個跟蹤文件只需使用一個簡單的SQL*Plus會話就如同下面這樣
　　
　　alter session
　　set timed_statistics=true;
　　alter session
　　set max_dump_file_size=unlimited;
　　
　　alter session
　　set tracefile_identifier=Hello;
　　/* only in Oracle Database
　　and later */
　　alter session
　　set events trace name context forever level ;
　　select Howdy it is ||sysdate from dual;
　　exit;
　　
　　然後在由USER_DUMP_DEST實例參數的值命名的目錄中尋找文件名中包含字符串Hello的最新寫入的trc文件用你最喜歡的文本編輯器打開它 閱讀Oracle MetaLink注釋或（Optimizing Oracle Performance《優化Oracle性能》）一書以便大概了解原始跟蹤文件中有些什麼一定要運行跟蹤文件上的tkprof並研究其輸出但也不要由於有了tkprof就不再看原始的跟蹤文件跟蹤文件中還有許多tkprof沒有向你展示的內容
　　
　　如果你不僅需要一個由簡單的SELECT from DUAL 生成的跟蹤文件還需要一個更感興趣的跟蹤文件那麼需要跟蹤下面這條SQL語句
　　
　　select object_type owner object_name from dba_objects;
　　
　　由此得到的跟蹤數據會讓你感到很滿意因為Oracle數據庫內核替你完成了驚人的工作量
　　
　　創建資源描述
　　
　　有了正確而詳細的診斷數據之後你需要以摘要的形式對其進行查看這有助於你以最快的速度做出響應至少是從世紀年代開始計算機程序員使用的摘要格式就是資源描述資源描述只是一張表它將所用時間分解為若干有用的子集並按各子集所用時間降序排列下面是一個資源描述的例子
　　
　　Response Time Component　　 Duration
　　　
　　Freeway at <% speed limit m　%
　　Finding a parking spot　　　 m　%
　　Waiting at traffic lights　　m　%
　　Freeway at ≥% speed limit　m　 %
　　Other　　　　　　　　　　　　m　 %
　　　
　　Total　　　　　　　　　　　 m %
　　
　　這個資源描述說明買一輛速度更快的車不會使你能夠更快地到達工作地點
　　
　　要從跟蹤文件創建資源描述有兩種方法可以采用
　　
　　自己動手《Optimizing Oracle Performance》一書中有所說明
　　
　　使用別人的工具Oracle的tkprof和trcanalyzer（跟蹤分析器）工具可為你完成一部分工作但不是全部
　　
　　對數據做出響應
　　
　　有了詳細的診斷數據及其要點就要決定對所看到的東西如何做出響應對資源描述做出響應的經驗做法非常可靠且相當簡單首先減少花費時間最長的部分方法是減少調用它的次數 下一步
　　
　　這種方法幾乎總是正確的理解減少給定組件的調用次數的方法需要對不同等待事件名稱的含義有所了解例如當被跟蹤的Oracle會話等待buffer busy waits這個等待事件時該會話會向跟蹤文件發送會生成足夠多的信息並顯示正在等待哪一個緩沖區以及為什麼要等待當一個會話等待SQL*Net message from client事件時跟蹤文件中生成的數據的位置會告訴你執行過的數據庫調用哪個是多余的
　　
　　在Oraclei第版中有多個不同的等待事件在Oracle數據庫g中幾乎有個等待事件但不必擔心你根本不必知道它們都是什麼意思你只需知道你的重要程序花費大部分時間所等待的那些事件是什麼意思
　　
　　看看你能做些什麼
　　
　　有了合適的診斷數據你就能迅速解決相應的問題或者證明這些問題不值得解決
　　
　　下面給出診斷數據能夠解決的一部分問題清單
　　
　　整個系統的問題以及個別用戶（業務）操作的具體問題
　　
　　查詢錯誤包括寫得不好的SQL語句有問題的索引以及數據密度問題
　　
　　A應用程序錯誤包括解析過度不使用數組運算等等在內的應用程序
　　
　　串行化錯誤包括不必要的頻繁發生或費時的鎖定鎖存或存儲緩沖區活動
　　
　　網絡錯誤如選擇的協議不當網絡設備有問題
　　
　　磁盤輸入/輸出錯誤如高速緩存大小不適當負載不平衡以及配置不當
　　
　　容量不足如交換分頁和CPU占用過多
　　
　　使用Oracle的擴展SQL跟蹤數據以及提出什麼如此費時？這種問題的方法能帶來的最好結果是在開始診斷和解決問題之前你將不必再猜測性能問題會是什麼
From:http://tw.wingwit.com/Article/program/Oracle/201311/17972.html