DBAplus社群 | 2015-12-29 07:01

轉載宣告：本文為DBA+社群原創文章，轉載必須連同本訂閱號二維碼全文轉載，並註明作者名字及來源：DBA+社群（dbaplus）。

目錄

術語說明

概述

什麼SQL會被SQL MONITORING監控到

找到Real Time SQL Monitoring入口

詳解Real Time SQL Monitoring

1術語說明

在正式介紹Real Time SQL Monitoring之前，我們先對接下來要用到一些術語做集中的介紹。

Table Queue，訊息緩衝區，在並行操作中使用，用於PX程式之間的通訊，或者PX程式與QC程式之間的通訊，是記憶體中的一些page，每個訊息緩衝區的大小由引數parallel_execution_message_size控制，11GR2版本預設為16K，之前的各個大版本這個值都不一樣，詳細請參考ORACLE官方文件。

牆面時間、持續時間指的是物理時間、鐘錶時間。

HASH JOIN左邊，the build side of hash join，一般為小表。

HASH JOIN右邊，the probe side of hash join，一般為大表。

M代表百萬

行源 row source，指的是執行計劃特定的一行操作，例如：

上面執行計劃的第一列，Id列0-5，每一行都是一個行源

2概述

Oracle每個版本總有一些新特性驚豔到我們，SQL MONITORING對我來說就是這樣一個新特性，雖然它還未廣為人知，它在11GR1版本被提供，而且後續的幾個版本（11GR2，12CR1）這個功能也被不斷的加強，說明ORACLE對它非常的重視，它能夠把查詢涉及到的所有關鍵效能統計資訊集中在一個頁面上，特別是對於並行查詢的語句會自動啟用這個特性。這個功能在國外的ORACLE 使用者組被多次的分享，但是目前國內對它的介紹還非常少，本文主要介紹Oracle Real Time SQL Monitoring的核心特性，意圖使DBA能夠有一種新的手段（更先進的手段）來診斷SQL效能，進而提升最佳化效率。

SQL最佳化是一個DBA必備的技能，然而即使一個有豐富SQL最佳化經驗的老DBA估計碰到幾十行甚至上百行的執行計劃也要皺皺眉頭，他如何能快速知道：

在這麼龐大的執行計劃中哪一行源消耗的資源最多。如果一個SQL的執行計劃包含5個行源，行源1消耗的DB TIME佔取了3%，那你即使把這3%的DB TIME全部消滅掉，也只讓SQL的效能提升了3%，對於整體的DB TIME提升效果並不明顯。

如何知道整個SQL執行過程中消耗的哪一類資源最多，IO？CPU?，這讓我們對SQL的效能有一個整體的認識，你可能觀察效能指標後會說，奧，這是一個IO比較重的SQL，如果需要大幅提升SQL效能，也許要考慮提升資料庫系統IO的能力。

對於一個正在執行的SQL語句，如何知道它當前執行到哪一步了？甚至知道執行完這一步還需要多久？

如何知道執行這個SQL語句都經歷了哪些等待事件，甚至知道這些等待裡哪一類等待最為嚴重？

要想知道這些問題的答案，在11G之前都是非常不容易的，要透過各種V$檢視的關聯去獲取，而且展示的結果不夠一目瞭然。 11G以後這些資訊全部可以在SQL MONITORING中找到答案，SQL MONITORING提供的功能還不僅僅是上面提到的這些，透過SQL MONITORING還可以輕鬆獲取語句的繫結變數、監控索引的整個建立過程及建立完索引剩餘的工作量。 文字會著重講解SQL MONITORING的核心功能，其他的相關資訊就請讀者們去盡情挖掘吧。

3什麼SQL會被SQL MONITORING監控到

對於絕大多數OLTP系統來說，SQL相對比較簡單，每次的執行時間都非常快，絕大部分SQL的響應時間都應該在10MS以下，最佳化的複雜度也比較低，SQL MONITORING功能的出現並不是為了幫助DBA發現、診斷OLTP SQL的效能問題，而是為了加快DBA最佳化資料倉儲類SQL的效率，這些SQL是偏OLAP系統的，特點是併發量低、執行時間久、SQL複雜度高。滿足以下條件的任意SQL都會被SQL MONITORING監控到：

如果序列執行的SQL，消耗的CPU時間或IO時間超過5秒，那麼這些SQL 將會被監控到，透過修改隱含引數_sqlmon_threshold可以控制這一行為，預設為5秒，如果設定為0將關閉SQL MONITORING功能 。注意我這裡提到的是SQL消耗的CPU時間或IO時間，而不是SQL的執行時間，之所以需要限制CPU時間或IO時間是為了防止資料庫某一時刻如果有大量lock/latch的話，那麼將有大量的SQL滿足5秒執行時間的條件，而SQL監控本身比較消耗資源，需要複製執行時的效能統計資訊到SGA，每一個受監控的SQL都有一個單獨的記憶體結構，在11G可能會導致大量的latch競爭，CPU飆高，12C對這個問題做了最佳化不存在該問題了。如果你發現你的SQL執行時間明顯超過了5秒但是卻沒被SQL MONITORING監控到，那麼你該仔細檢查是否是由於SQL本身消耗的CPU或IO並沒有超過5秒（由於鎖、網路？）。

並行執行的SQL將全部被監控到，不需要等待CPU或IO時間超過5秒。對於這一點也比較好理解，一般並行查詢的SQL都是報表類或比較重的任務類的SQL，因此會自動開啟SQL MONITORING的功能。

增加HINT /+ monitor /的SQL會立即開啟SQL MONITORING功能。

除了以上條件外，你還需要檢查一些系統引數是否設定正確：

statistics_level需要為TYPICAL（預設）或者ALL.

control_management_pack_access需要為DIAGNOSTIC+TUNING（預設）

SQL MONITORING並不會監控非常大的執行計劃，預設不會監控超過300行的執行計劃，可以透過隱含引數_sqlmon_max_planlines來控制。具體請參照MOS ID:1613163.1

4找到Real Time SQL Monitoring入口

可以從這幾個位置找到、使用SQL Monitoring的功能：

Enterprise Manager

Enterprise Manager Cloud Control(EMCC)

SQL Developer

DBMS_SQLTUNE包

這裡我們主要介紹透過Enterprise Manager Cloud Control(EMCC)頁面來使用Real Time SQL Monitoring，文章的後面也會有單獨一節簡單介紹如何透過SQL包DBMS_SQLTUNE來獲取Real Time SQL Monitoring的輸出。

首先登陸Oracle EMCC，進入到【資料庫】頁面，選擇你需要監控的資料庫，這裡以clouddb1為例：

進入到clouddb1資料庫後，選擇【效能】下的SQL監視功能。

然後就進入到了SQL MONTORING的主介面，這裡包含了最近被監控到的SQL，檢視狀態列，可以知道SQL當前的執行狀態。

狀態列一般包含以下4種狀態：執行、完成、排隊、錯誤，將滑鼠放在狀態列的符號上面，會看到符號所代表的狀態。這幾個狀態除了【排隊】狀態都比較好理解，排隊這個狀態只會在大於11GR2版本出現。ORACLE 11GR2在並行設定方面起了很大變化：自動並行度調整、並行語句佇列、記憶體並行執行，啟用此三項新特性需要設定引數parallel_degree_policy為auto，預設值為MANUAL。設定為auto後，三項新特性全部被開啟，這裡我們主要說下並行語句佇列，啟用該特性後，透過設定parallel_servers_target來指定總的可用的並行子程式的目標數，執行語句時，如果發現可用的並行程式數已經小於待執行語句的目標數，那麼語句將會放入佇列中等待直到擁有足夠可用的並行程式。一旦被加入佇列，你就會在SQL MONITORING的監控頁面上看到，語句的當前執行狀態為【排隊】。

SQL監視頁面顯示的SQL列表不是針對特定的SQL文字的，而是針對特定SQL語句的一次特定的執行，因此如果兩個會話執行相同的語句，你會在此列表中看到兩個單獨的條目，因此它與v$sql的表現行為不一樣，如果2個會話在執行同一個SQL，在 v$sql裡只能對應到彙總了SQL執行統計資訊的一個記錄。針對SQL MONITOR的這個特點，允許你針對一個特定的使用者（他正在抱怨效能問題）檢查這個語句到底出了什麼問題，而不是首先觀察彙總了所有使用者的語句級的效能指標，像v$sql裡的，再試著從中發現你所關注的使用者問題。

5詳解Real Time SQL Monitoring

找到Real Time SQL Monitoring的入口後，可以點選SQL_ID進入到Real Time SQL Monitoring的展示頁面，是不是很炫酷？

也許你對頁面上的很多指標還感覺很陌生，彆著急，我接下來會對頁面上的核心部分進行介紹。

5.1 一般資訊

一般資訊部分顯示了SQL執行的一些基本的細節，例如SQL的文字，SQL語句採用的並行度，SQL的執行開始時間，本次統計值最後一次的重新整理時間，執行SQL的使用者、取數操作等。注意【SQL文字】後帶有…的標記（紅色框標記），點選它，你會獲得完整的SQL語句文字，從版本11.2.0.2開始，你還會看到繫結變數的值。

例如：

上面的程式碼我對一個帶有繫結變數的SQL以並行度6執行了查詢，然後透過EMCC的SQL MONITORING頁面點選【SQL文字】後面部分的…，這時會出現一個新視窗：

點選show SQL Binds：

這時就可以看到繫結變數的列表，是不是非常的方便？

當然就像上面提到的，SQL MONITORING主要用於監控資料倉儲型別的SQL，對於長時間執行查詢，是否應該使用繫結變數是另外一個單獨的話題，在資料倉儲中，一般的最佳實踐為：不該為長時間執行的查詢使用繫結變數，因為相對於整個SQL的執行時間，SQL解析只佔到可以忽略不計的時間，而且資料倉儲的系統本身的SQL執行頻率也非常低，因此我們可以考慮對所有查詢都進行硬解析，即使這樣會消耗掉一些響應時間（可以忽略不計），但我們儘可能保證每一組引數組合都能生成一個最優的執行計劃，因為就繫結變數和文字常量來說，文字常量是最適合產生最優執行計劃的。

言歸正傳，就像上面已經演示的，得到被監控查詢的繫結變數值在Oracle 11.2.0.2或更高版本上變得容易，以前你可能需要查詢v$sql_bind_capture，dba_hist_sqlbind來獲取繫結變數的值（歷史繫結值），甚至透過oradebug errorstack這類命令來獲得當前執行SQL的正在使用的繫結變數的值，現在你終於可以拋棄這些方法了。

【一般資訊】模組還包含了取數操作（fetch calls）的次數，簡單來說它代表了資料庫和應用端網路互動的次數，如果是count,sum這類操作，一般這個值為1，只需要一次網路互動，但是對於select *這樣的查詢，返回的資料量越大，這個值也會越大。

例如從上圖我們知道，返回1000條記錄一共分了11次才傳輸完成。每次傳輸90條記錄。

取數操作我們在後面的章節還會繼續講到，這裡先點到為止。

5.2 時間和等待統計資訊

這一部分內容非常少，但是資訊量非常大，顯示的是持續時間、資料庫時間和等待活動百分比。把滑鼠移動到資料庫時間的條狀圖上，會看到消耗在各類等待事件和CPU上的時間，這讓你知道你的SQL時間都花哪了，是IO操作多，還是CPU操作多，這非常的棒。等待活動百分比條狀圖則顯示了詳細的等待事件分佈，例如上圖中藍色部分代表整個語句執行過程中，45%的等待發生在direct path read temp這個等待事件上，注意這裡等待活動百分比條狀圖的100（100%）指的是資料庫裡的等待事件時間，而不是整個資料庫時間，即並不包括CPU時間，這部分的資訊可以告訴DBA在SQL執行過程中，遭遇了哪些等待事件，以及這些等待事件佔取的比例，當然，你應該關注這些等待事件裡最為惹眼的。

你可能對持續時間和資料庫時間的含義感到疑惑，沒關係，我們接下來就會著重介紹這兩個時間代表的含義和兩者的差異：

5.3 持續時間

持續時間（牆面時間）是使用者非常關注的時間，它顯示一個語句已經處於活動狀態多久，它代表著語句從開始執行直到結束的時間跨度，對於正在執行的語句，則是從開始執行到當前的時間。當然，終端使用者可能還要等待更長的時間，因為除了資料庫的響應時間之外，時間還可能會被花在應用系統上，或者是資料庫和應用伺服器之間的網路上。例如SQL的持續時間為5秒，但是應用本身處理這些資料需要1秒，那麼使用者端感受到的時間就可能是6秒，而不是資料庫端看到的持續時間5秒。

需要再次強調的是，持續時間衡量的是從SQL開始執行即遊標開啟直到遊標被關閉或取消的時間跨度，這意味著如果資料庫1分鐘內完成一個查詢，但隨後產生的數百萬結果每次只能返回幾行，從應用的角度看，這個查詢將需要很長的時間（需要數百萬次的網路傳輸）才能完成，但是資料庫端只花了一點時間來處理。對於網路傳輸的情況，SQL MONITORING會有一個指標進行反應，就是上面已經提到過的取數操作（fetch calls），如果網路傳輸量比較大的話，這個值會比較大。

例如我透過monitor這個hint強制讓ORACLE監控這個SQL，這個SQL會返回大量的結果集給客戶端，我們透過EMCC來監控這個SQL的相關監控資訊：

取數操作值非常大，因為資料庫要跟應用（我們這裡是SQLPLUS客戶端）有大量的網路互動。

5.4 資料庫時間

資料庫時間即DB Time，顯示的是一個查詢在資料庫中執行花費的總時間，就DML操作來說，一般資料庫時間基本等於持續時間，因為DML操作不用返回結果集，沒有網路互動時間，但是如果執行的是一個SELECT語句並返回很多行，那麼持續時間一般會大於資料庫時間，因為很多的網路時間會被統計在持續時間中，資料庫會把資料分批傳送給應用程式，因此對於一個select語句你可能會看到資料庫時間只有30秒，而持續時間卻有50秒，其中30秒用於服務你的請求，其餘的時間資料庫則是空閒的，等待應用取下一批資料。我們再次看一下上面已經使用過的一張圖，對於這個查詢來說，由於要返回大量的結果集給客戶端，因此持續時間遠遠大於資料庫時間。

對於序列查詢來說，持續時間都是大於等於資料庫時間的，但是對於並行查詢來說，情況有所改變：當執行一個並行查詢時，會有多個伺服器程式（甚至可能是多個伺服器上的伺服器程式）參與進來，每個伺服器程式都可以使用單獨的CPU資源，也就是1分鐘的牆面時間(wall time），但是資料庫時間可能有1*N分鐘（N為CPU邏輯核數），並行查詢最終的資料庫時間可能會比持續時間（wall time）長的多。

這個語句的持續時間只有19秒，而資料庫時間則有1.8分鐘，從【一般資訊】的Execution Plan部分可以看到這個語句是以並行度6來執行，這意味著資料庫裡有多個程式同時服務於這個查詢，所有這些會話的資料庫時間加上協調程式的時間都被彙總到了資料庫時間中。資料庫時間代表著大致有多少時間必須在資料庫中完成，但是因為該語句是並行的，因此你其實不必等待那麼長的時間。

5.5 IO統計資訊

顯示了語句執行時的一些關鍵的IO統計資訊。

緩衝區獲取數，即buffer gets，顯示了資料庫層的邏輯IO總數。IO請求包含了總的IO請求次數，例如db file sequential read,db file scattered read,direct path read等等的次數，IO位元組代表了讀取/寫入的IO的總位元組數。透過條狀圖的不同有顏色部分，能獲取到更詳細的統計值，例如上圖中顯示10GB的IO位元組，其中有42%是讀取請求產生的，等等。 需要注意的是，這裡的IO請求次數和IO位元組數統計可能超出你的預期，例如針對一個1G表的全表掃描按照某個欄位排序，不但要統計表本身的IO，而且還要統計排序產生的讀、寫臨時段的IO。

5.6 詳細資訊

詳細資訊頁本身包含了一些子tab頁面，例如上圖中包含了【計劃統計資訊】、【計劃】、【並行】、【活動】、【度量】子tab頁。詳細資訊頁主要包含了執行計劃在在行源（rowsource）細節上的一些統計資訊。

首先我們先看下頁面的最左側一列，如果某個行源操作是被並行執行的，會用多個小人的圖示標識，相反如果是被序列執行的會用一個小人的圖示標識，如果你觀察仔細的話，會發現多個小人的圖示有時也會有顏色的區分（下圖），這是因為並行執行中，生產者和消費者角色的不同導致的。例如圖中的紅色是生產者負責掃描表，而圖中藍色的程式是消費者負責把接收過來的資料做排序。

再者，我們會看到多人小圖示的後面有向右箭頭的指示，這個代表了目前的查詢正在進行，執行計劃正在執行到這個（些）行源（右箭頭所在的行源），也就是透過SQL MONITORING可以知道目前執行計劃執行到了哪一個行源，nice job！如果SQL執行結束，或者這個（些）行源執行結束，這些向右的小箭頭也會消失。

然後我們關注下【估計行數】和【實際行數】，如果兩者差異巨大，可能是由於你的資料庫系統統計資訊缺失或者陳舊，或者是最佳化器自身的演算法導致。這裡我並不會介紹CBO最佳化的相關資訊，讀者可以自行參閱相關文章。【實際行數】列真實的反應了掃描程式需要產生的工作量。

雖然Oracle CBO技術發展了這麼久，相關書籍、文章也非常多，但是筆者認為，閱讀、瞭解CBO最好的書籍仍然是jonathon lewis的基於成本的ORACLE最佳化法則，不過中文版貌似已經買不到了。