Oracle Real Time SQL Monitoring

wei-xh發表於2015-12-22
OracleSQL
術語說明
  • 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  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    10 | 59167   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE      |      |     1 |    10 |            |          |
|*  2 |   COUNT STOPKEY      |      |       |       |            |          |
|   3 |    NESTED LOOPS      |      |   100 |  1000 | 59167   (1)| 00:00:03 |
|   4 |     TABLE ACCESS FULL| TEST |   126K|   619K|     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TEST |     1 |     5 |   592   (1)| 00:00:01 |
-----------------------------------------------------------------------------

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


概述

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的核心功能，其他的相關資訊就請讀者們去盡情挖掘吧。


什麼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


找到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的監控頁面上看到，語句的當前執行狀態為【排隊】。

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


詳解Real Time SQL Monitoring

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



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


一般資訊

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

SQL>var c numberSQL>
exec :c :=1
SQL>select /*+ parallel(a 6)*/ count(*) from hash_t1 a where id=:c;  
COUNT(*)
----------     
    1

上面的程式碼我對一個帶有繫結變數的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條記錄。 
取數操作我們在後面的章節還會繼續講到，這裡先點到為止。


時間和等待統計資訊


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

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


持續時間

持續時間（牆面時間）是使用者非常關注的時間，它顯示一個語句已經處於活動狀態多久，它代表著語句從開始執行直到結束的時間跨度，對於正在執行的語句，則是從開始執行到當前的時間。當然，終端使用者可能還要等待更長的時間，因為除了資料庫的響應時間之外，時間還可能會被花在應用系統上，或者是資料庫和應用伺服器之間的網路上。例如SQL的持續時間為5秒，但是應用本身處理這些資料需要1秒，那麼使用者端感受到的時間就可能是6秒，而不是資料庫端看到的持續時間5秒。 
需要再次強調的是，持續時間衡量的是從SQL開始執行即遊標開啟直到遊標被關閉或取消的時間跨度，這意味著如果資料庫1分鐘內完成一個查詢，但隨後產生的數百萬結果每次只能返回幾行，從應用的角度看，這個查詢將需要很長的時間（需要數百萬次的網路傳輸）才能完成，但是資料庫端只花了一點時間來處理。對於網路傳輸的情況，SQL MONITORING會有一個指標進行反應，就是上面已經提到過的取數操作（fetch calls），如果網路傳輸量比較大的話，這個值會比較大。

SQL>set autotrace traceonlySQL>select /*+ monitor */ *  from hash_t1;

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

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


資料庫時間

資料庫時間即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來執行，這意味著資料庫裡有多個程式同時服務於這個查詢，所有這些會話的資料庫時間加上協調程式的時間都被彙總到了資料庫時間中。資料庫時間代表著大致有多少時間必須在資料庫中完成，但是因為該語句是並行的，因此你其實不必等待那麼長的時間。

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。


詳細資訊


詳細資訊頁本身包含了一些子tab頁面，例如上圖中包含了【計劃統計資訊】、【計劃】、【並行】、【活動】、【度量】子tab頁。詳細資訊頁主要包含了執行計劃在在行源（rowsource）細節上的一些統計資訊。 
首先我們先看下頁面的最左側一列，如果某個行源操作是被並行執行的，會用多個小人的圖示標識，相反如果是被序列執行的會用一個小人的圖示標識，如果你觀察仔細的話，會發現多個小人的圖示有時也會有顏色的區分（下圖），這是因為並行執行中，生產者和消費者角色的不同導致的。例如圖中的紅色是生產者負責掃描表，而圖中藍色的程式是消費者負責把接收過來的資料做排序。 
再者，我們會看到多人小圖示的後面有向右箭頭的指示，這個代表了目前的查詢正在進行，執行計劃正在執行到這個（些）行源（右箭頭所在的行源），也就是通過SQL MONITORING可以知道目前執行計劃執行到了哪一個行源，nice job！如果SQL執行結束，或者這個（些）行源執行結束，這些向右的小箭頭也會消失。



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

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

【時間表列】（Timeline）是一個很重要的列，通過它可以知道這個SQL執行計劃每一行活躍的時間段。上圖中的Timeline列基於執行計劃每一行的活動構造出了一個視覺化的時間進度圖，它基於ASH的取樣，從ORACLE 11G開始，ASH基於SQL執行計劃的每一行源收集資訊，觀察上圖中Timeline列標題裡的括號內的數字，它顯示了這個SQL總的執行持續時間，在這裡是21秒，現在很容易根據這個，然後觀察每個行源上的條狀圖和條狀圖位置進行視覺化的解析。 
然後將我們的目光移動到【臨時】列，它代表了SQL執行過程中消耗的臨時表空間大小，如果發現SQL消耗的臨時表空間非常大，可以檢視是否並行度不夠或者PGA引數設定過小，不過對於建立索引、大表HASH JOIN、SQL排序等操作來說，完全避免臨時段的使用也是一件可望不可即的事。 
【IO請求】列代表了每一個行源所產生的IO請求數，根據視覺化的條形圖，很容易發現哪一個行源產生了最多的IO請求。 
【活動百分比】列代表了在此行源產生的等待，CPU也會被統計在內，它的資料是從ASH的基表v$active_session_history獲取的，也就是說資料是取樣而來的（1秒取樣一次）。例如：


SQL>select /*+ parallel(a 6)*/ * from hash_t1 a ;
SQL>select sql_plan_line_id,event from v$active_session_history where sql_id='8s2vhn4cwv140';


SQL_PLAN_LINE_ID EVENT
---------------- ----------------------------------------
               6 direct path read
               6 direct path read
               6 direct path read
               6
               6 direct path read
               6 direct path read
               
SQL>desc v$active_session_history
Name                               Null?    Type
---------------------------------- -------- -------------------
SAMPLE_ID                                   NUMBER
SAMPLE_TIME                                 TIMESTAMP(3)
IS_AWR_SAMPLE                               VARCHAR2(1)
SESSION_ID                                  NUMBER
SESSION_SERIAL#                             NUMBER
SESSION_TYPE                                VARCHAR2(10)
FLAGS                                       NUMBER
USER_ID                                     NUMBER
SQL_ID                                      VARCHAR2(13)
IS_SQLID_CURRENT                            VARCHAR2(1)
SQL_CHILD_NUMBER                            NUMBER
SQL_OPCODE                                  NUMBER
SQL_OPNAME                                  VARCHAR2(64)
FORCE_MATCHING_SIGNATURE                    NUMBER
TOP_LEVEL_SQL_ID                            VARCHAR2(13)
TOP_LEVEL_SQL_OPCODE                        NUMBER
SQL_ADAPTIVE_PLAN_RESOLVED                  NUMBER
SQL_FULL_PLAN_HASH_VALUE                    NUMBER
SQL_PLAN_HASH_VALUE                         NUMBER
SQL_PLAN_LINE_ID                            NUMBER
SQL_PLAN_OPERATION                          VARCHAR2(30)
SQL_PLAN_OPTIONS                            VARCHAR2(30)
SQL_EXEC_ID                                 NUMBER
SQL_EXEC_START                              DATE

11G的v$active_session_history檢視新增了SQL_PLAN_LINE_ID、SQL_PLAN_OPERATION、SQL_PLAN_OPERATION列，可以跟蹤到具體發生等待的行源資訊，非常有利用與DBA做效能診斷。上面SQL執行過程中，ASH共取樣到了6條資料，其中五次等待事件為direct path read，一次為空，即為CPU操作。 SQL_PLAN_LINE_ID 代表產生等待的行源，這裡為6，觀察下圖【行ID】列，ID為6的行源執行的正是全表掃描操作，【活動百分比】條狀圖顯示了產生了5次的direct path read等待，與我們從ASH檢視裡查詢的結果完全相符。



因此【活動百分比】列非常重要，通過此列，我們能夠知道DB TIME的去向，例如上圖中，絕大部分的DB TIME都會花在了全表掃描階段。除了全表掃描行源，其他行源都為空，但是這並不代表其他行源沒有花取資料庫時間，而是因為這塊的內容是依據ASH取樣而來的，如果行源操作執行比較快的話，ASH就不容易捕獲到。通過【活動百分比】列，我們可以定位到最消耗資源的行源，然後可以有針對性的做優化，達到事半功倍的效果。 
我們來看【詳細資訊】 的【並行】tab頁，這個頁面彙集了每一個並行程式完成的工作，由於ORACLE的並行採用了生產者消費者模型，首先是按照例項做的分組，然後再按照生產者和消費者把程式做了分組，我的測試例子裡，只有一個RAC節點例項，另一個節點被關閉了。



切換到【詳細資訊】的【活動】頁，展示了從開始執行到結束執行時間跨度內，有多少程式處於活躍狀態。由於我們設定了並行度6，因此可以從頁面上看到SQL執行的後期大部分時間有6個程式處於活躍狀態。



【詳細資訊】的【度量】頁顯示了查詢 過程中對CPU、記憶體、IO的佔用率。



這裡我們基本上把SQL MONITORING的核心內容做了減少，這個頁面包含了大量的資訊，而且這個頁面本身具有非常大的互動性，對這個頁面你可以充分發揮你的好奇心，把你的滑鼠盡情的放在任何一個可以放的位置，盡情發掘裡面的寶藏。


通過SQL MONITORING監控索引建立過程

之所以專門寫一節通過SQL MONITORING監控索引建立過程是源於一個客戶的需求，他由於業務上的需要想要知道重建索引大概需要多久，即使不能知道準確的時間，也希望能大概知道索引建立開始後，已經完成了多少的工作，大約還有多少工作未完成 。如果是使用的11G版本 ，可以很輕鬆的通過SQL MONITORING實現這個需求。 
索引的建立大體上分為兩個階段：第一，全表掃描過程 第二，排序建立索引過程。 
我們這裡給出一個索引建立的例子，為了保證可以讓SQL MONITORING監控到這個語句，我使用了並行語句，當然也可以造取一個足夠大的表來確保索引建立語句執行的時間足夠長來開啟SQL MONITOR功能 。

alter index tt rebuild online parallel 6;


索引建立開始後，我們觀察SQL MONITORING頁面的【詳細資訊】部分，【詳細資訊】頁面的最左邊列的多人圖示顯示了這是一個並行執行的語句，這裡紅色為生產者，負責掃描HASH_T1表，根據全表掃描行源（ID=8）的父操作PX BLOCK ITERATOR可以知道，PX SLAVE按照ROWID劃分工作，每個PX SLAVE負責一部分資料塊的掃描（我這裡為分割槽表，並沒有按照分割槽劃分工作），掃描後的資料寫入TQ10000 table queue，消費者PX SLAVE程式從table queue TQ10000讀取資料，這樣通過table queue TQ10000表，兩組並行程式完成了資料的傳遞。消費者PX SLAVE把接收到的資料做排序並建立索引。



從上圖可以看出，向右的箭頭處於排序建立索引階段（行源3，4，5），全表掃描階段已經結束，正在進行排序和建立索引，根據【實際行數】列可以知道目前排序已經完成的行數是531K，而真正需要的排序量為47M，從其他行源可以知道需要排序的總量（ID=5-8的實際行數列都顯示了這個資訊）。接下來的工作就是重新整理頁面看排序的實際行數什麼時候能夠到47M了。【進度百分比】列顯示了已經完成的百分比，這個對於工作量的估算非常的有意義。有一點比較遺憾，【進度百分比】對於很多操作都不能進行顯示，很多時候【進度百分比】會在SQL執行過程中動態出現。不過對於索引建立來說，這個並不重要，根據真正的資料量和已經完成的排序量就能大概知道剩餘的工作量。


重新整理後已經完成了28M的排序工作。





再次重新整理，發現索引建立已經完成，因為最左邊的已經沒有向右的箭頭，而且實際行數（行源4）已經為47M，完成了所有資料的排序。我們觀察【活動百分比】這一列，整個索引建立過程中，絕大部分的時間都消耗在ID=4這個行源上，即排序和建立索引操作上，從【時間表】（Timeline)列也可以看出它的活躍時間跨度也最長。


一個例子

上面介紹了相關的SQL MONITORING的核心特性，但是它只能協助我們發現效能問題，如何解決問題它並不能幫助到我們，解決問題的過程還需要我們具備各種SQL優化的技能，如CBO的知識，作業系統的知識等等。 
我們來通過一個小例子來進階我們對於SQL MONITORING的理解： 
表的物理資訊： 
hash_t1 大小 4416M 
查詢語句

select /*+ parallel(6) pq_distribute(b broadcast none) */count(*)  from hash_t1 a,hash_t1 b where a.id=b.id;

SQL執行時間2分鐘，由於使用了並行我們從上圖可以看到DB TIME 12.6分鐘遠大於SQL執行時間2分鐘。



根據【活動百分比】這一列看到HASH JOIN本身佔取的資料庫時間最多，達到了51%（ASH取樣的結果），再仔細觀察執行計劃的相關內容，HASH JOIN的左邊使用了廣播的分發方式PX SEND BROADCAST，但是需要廣播的資料量非常的大，有40M,由於採用了6個併發，因此廣播之後，每一個PX程式都接收到了40M的資料量，因此總共的資料量為240M。HASH JOIN右邊的表的資料量也是40M，跟HASH JOIN左邊的表大小一樣，資料的分發方式一般包括廣播和HASH，12C新增加了replicat方式，對於HASH JOIN左邊是小表的情況一般使用廣播方式的分發，對於HASH JOIN的左邊、右邊都是大表的情況，一般使用HASH分發比較好。這裡由於每個PX消費者程式都需要完整持有HASH JOIN左邊表的資料，因此消耗的臨時段的空間也比較大，達到了4GB。



我們來看下嘗試使用HASH 分佈效果怎麼樣：SQL執行時間從2分鐘縮減到了1.3分鐘，DB TIME也從12.6分鐘縮減到了8.3分鐘。



檢視執行計劃發現資料分發方式已經變為PX SEND HASH，使用了HASH分發的方式，觀察【實際行數列】資料量也和表的記錄數相同，臨時表空間也從4GB大小縮減為966MB。HASH JOIN操作本身佔取的活動百分比也從51%下降為22%。




相關檢視

Grid Control上所有漂亮的圖表都來源於v$或DBA_檢視或許沒有必要為日常監控和調優任務經常去訪問這些v$檢視，但知道這些資訊來自哪裡仍人是很有用處的，因為這會讓自定義監控和對問題的高階診斷變得更方便，例如我所在的沃趣科技就在自動研發一套監控系統，也在考慮將SQL MONITORING這個功能增加到我們的監控產品裡，如果能瞭解到這些功能的底層檢視就對我們開發這個功能非常有好處。下面是一些要知道的關鍵檢視：

  • GV$SQL_MONITOR檢視包含了語句執行時的監控資料。當有多個會話在執行相同的語句時，這個檢視會有多個記錄與之對應，所以請確保使用了正確的搜尋過濾條件來獲得你所關注的SQL執行情況。例如，應該注意SID和INST_ID是不是你正在尋找的會話，對於並行執行則是PX_QCSID和PX_QCINST_ID，如果在診斷一條當前正在執行的查詢，則要檢查列status是否顯示為executing狀態。
  • GV$SQL_PLAN_MONITOR 檢視包含了執行計劃每一行的效能指標，這些指標會被實時監控和更新。
  • GV$ACTIVE_SESSION_HISTORY 檢視從ORACLE 11GR1開始包含了如SQL_PLAN_LINE_ID、SQL_PLAN_OPERATION和SQL_PLAN_OPTIONS這些列。通過查詢這些列，加上SQL_ID，就可以找到一個SQL執行計劃中最顯著的行源，而不僅僅找到最顯著的執行計劃。

控制SQL監控

對於並行執行的SQL，SQL監控會馬上對他們啟用，而不管他們執行的時間有多久。對於序列執行的SQL，SQL監控不會馬上啟用，因為SQL監控並不是用來監控通常執行速度很快的OLTP查詢的，這類查詢每秒都會被執行很多遍。然而，如果一個SQL序列查詢消耗多餘5S的CPU和IO等待時間，他將會被認為是一個長查詢，SQL監控也會對此類查詢啟用。這是自動發生的，查詢語句不需要重新執行。 
可以使用提示MONITOR和NO_MONITOR來控制對一個語句的監控與否。如果你對某一個執行時間比較長的SQL不想做監控，可以使用NO_MONITOR這個HINT。需要注意有一個跟NO_MONITOR非常相像的HINT NO_MONITORING，它是與SQL MONITORING功能完全不相關的東西，通過NO_MONITORING這個HINT可以禁止對錶裡先關欄位的謂詞使用情況進行監控，這些監控資料會被放入到sys.col_usage$中。



SQL>SELECT NAME FROM V$SQL_HINT where name like '%MONITOR%';


NAME
---------------------
NO_MONITORING
MONITOR
NO_MONITOR


使用SQL獲取SQL MONITORING的輸出

經過上面一系列的介紹，我們再來看通過SQL包獲取SQL MONITORING的輸出就變得極為簡單，這裡我不會再花費筆墨來對輸出做詳細的解釋，大部分的資訊上面我都已經講到過了。 
這個主要是通過dbms_sqltune.report_sql_monitor來進行的。下面的SQL的含義是，把SQL_ID為4vnz8232nugv9的查詢，最近的SQL MONITORING的資訊以text格式列印出來：

col comm format a300
SELECT dbms_sqltune.report_sql_monitor(
sql_id => '4vnz8232nugv9',
report_level => 'ALL',
type=>'text'
) comm
FROM dual;


SQL Monitoring Report


SQL Text
------------------------------
select /*+ full(a) parallel(a 6)*/ count(*) from ORDER_ITEMS a


Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  1
Session             :  SOE (857:1095)
SQL ID              :  dgxh9275fun36
SQL Execution ID    :  16777218
Execution Started   :  08/24/2015 12:57:24
First Refresh Time  :  08/24/2015 12:57:25
Last Refresh Time   :  08/24/2015 12:57:40
Duration            :  16s
Module/Action       :  SQL*Plus/-
Service             :  SYS$USERS
Program             :  sqlplus@rac1 (TNS V1-V3)
Fetch Calls         :  1


Global Stats
============================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Calls |  Gets  | Reqs | Bytes |
============================================================================================
|      90 |      16 |       73 |        0.00 |        0.29 |     1 |     3M | 185K |  22GB |
============================================================================================


Parallel Execution Details (DOP=6 , Servers Allocated=6)
===================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO     | Concurrency | Buffer | Read  | Read  |      Wait Events      |
|                |       |         | Time(s) | Time(s) | Waits(s)  |  Waits(s)   |  Gets  | Reqs  | Bytes |      (sample #)       |
===================================================================================================================================
| PX Coordinator | QC    |         |    0.30 |    0.01 |           |        0.29 |    139 |       |     . | os thread startup (1) |
| p000           | Set 1 |       1 |      15 |    2.69 |       12  |             |   498K | 30764 |   4GB | direct path read (10) |
| p001           | Set 1 |       2 |      15 |    2.72 |       12  |             |   498K | 30772 |   4GB | direct path read (10) |
| p002           | Set 1 |       3 |      15 |    2.71 |       12  |             |   498K | 30771 |   4GB | direct path read (11) |
| p003           | Set 1 |       4 |      15 |    2.78 |       12  |             |   498K | 30763 |   4GB | direct path read (11) |
| p004           | Set 1 |       5 |      15 |    2.68 |       12  |             |   498K | 30769 |   4GB | direct path read (11) |
| p005           | Set 1 |       6 |      15 |    2.71 |       12  |             |   498K | 30762 |   4GB | direct path read (12) |
===================================================================================================================================


SQL Plan Monitoring Details (Plan Hash Value=1446499772)
====================================================================================================================================
| Id |        Operation         |   Name     | Rows   | Cost |  Time    |Execs |   Rows   | Read |Activity |    Activity Detail    |
|    |                          |            |(Estim) |      |Active(s) |      | (Actual) | Reqs |  (%)    |      (# samples)      |
====================================================================================================================================
|  0 | SELECT STATEMENT         |            |        |      |        1 |    1 |        1 |      |         |                       |
|  1 |   SORT AGGREGATE         |            |      1 |      |        1 |    1 |        1 |      |         |                       |
|  2 |    PX COORDINATOR        |            |        |      |       16 |    7 |        6 |      |    1.18 | os thread startup (1) |
|  3 |     PX SEND QC (RANDOM)  |:TQ10000    |      1 |      |        2 |    6 |        6 |      |         |                       |
|  4 |      SORT AGGREGATE      |            |      1 |      |       14 |    6 |        6 |      |         |                       |
|  5 |       PX BLOCK ITERATOR  |            |   343M | 116K |       14 |    6 |     357M |      |         |                       |
|  6 |        TABLE ACCESS FULL |ORDER_ITEMS |   343M | 116K |       15 |   78 |     357M | 185K |   98.82 | Cpu (19)              |
|    |                          |            |        |      |          |      |          |      |         | direct path read (65) |
====================================================================================================================================

dbms_sqltune.report_sql_monitor包的type除了我例子給出的text外，還可以有HTML,ACTIVE（11GR2）,XML。如果你想檢視某個SQL歷史的執行統計資訊而不是最近一次的，可以通過指定SQL_EXEC_ID 引數來進行。詳細可以參考:

https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1#active_html_reports_offline


參考資訊
  • ORACLE-BASE 著名ORACLE部落格網站
  • TANEL PODER部落格，ORACLE業界著名人物
  • ORACLE官方文件

作者資訊

魏興華,沃趣科技高階資料庫技術專家，8年ORACLE資料庫使用經驗，Oracle ACE-A ,DBGEEK使用者組聯合創始人之一，ACOUG、SHOUG核心成員。ORACLE INTERNAL達人，原阿里高階資料庫工程師，曾在ORACLE技術嘉年華、ORCL-CON、YY分享平臺等公開場合多次做過資料庫技術專題分享並獲得好評。 
個人郵箱：xinghua.wei@woqutech.com
DB GEEK QQ群：516293316 
公司主頁：www.woqutech.com

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1876846/，如需轉載，請註明出處，否則將追究法律責任。

相關文章