【SQL】OracleSQLmonitor

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

轉(zhuǎn)載聲明：本文為DBA+社群原創(chuàng)文章，轉(zhuǎn)載必須連同本訂閱號二維碼全文轉(zhuǎn)載，并注明作者名字及來源：DBA+社群（dbaplus）。

目錄

術(shù)語說明

概述

什么SQL會被SQL MONITORING監(jiān)控到

找到Real Time SQL Monitoring入口

詳解Real Time SQL Monitoring

1術(shù)語說明

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

Table Queue，消息緩沖區(qū)，在并行操作中使用，用于PX進(jìn)程之間的通信，或者PX進(jìn)程與QC進(jìn)程之間的通信，是內(nèi)存中的一些page，每個消息緩沖區(qū)的大小由參數(shù)parallel_execution_message_size控制，11GR2版本默認(rèn)為16K，之前的各個大版本這個值都不一樣，詳細(xì)請參考ORACLE官方文檔。

墻面時間、持續(xù)時間指的是物理時間、鐘表時間。

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

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

M代表百萬

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

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

2概述

Oracle每個版本總有一些新特性驚艷到我們，SQL MONITORING對我來說就是這樣一個新特性，雖然它還未廣為人知，它在11GR1版本被提供，而且后續(xù)的幾個版本（11GR2，12CR1）這個功能也被不斷的加強(qiáng)，說明ORACLE對它非常的重視，它能夠把查詢涉及到的所有關(guān)鍵性能統(tǒng)計信息集中在一個頁面上，特別是對于并行查詢的語句會自動啟用這個特性。這個功能在國外的ORACLE 用戶組被多次的分享，但是目前國內(nèi)對它的介紹還非常少，本文主要介紹Oracle Real Time SQL Monitoring的核心特性，意圖使DBA能夠有一種新的手段（更先進(jìn)的手段）來診斷SQL性能，進(jìn)而提升優(yōu)化效率。

SQL優(yōu)化是一個DBA必備的技能，然而即使一個有豐富SQL優(yōu)化經(jīng)驗的老DBA估計碰到幾十行甚至上百行的執(zhí)行計劃也要皺皺眉頭，他如何能快速知道：

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

如何知道整個SQL執(zhí)行過程中消耗的哪一類資源最多，IO？CPU?，這讓我們對SQL的性能有一個整體的認(rèn)識，你可能觀察性能指標(biāo)后會說，奧，這是一個IO比較重的SQL，如果需要大幅提升SQL性能，也許要考慮提升數(shù)據(jù)庫系統(tǒng)IO的能力。

對于一個正在執(zhí)行的SQL語句，如何知道它當(dāng)前執(zhí)行到哪一步了？甚至知道執(zhí)行完這一步還需要多久？

如何知道執(zhí)行這個SQL語句都經(jīng)歷了哪些等待事件，甚至知道這些等待里哪一類等待最為嚴(yán)重？

要想知道這些問題的答案，在11G之前都是非常不容易的，要通過各種V$視圖的關(guān)聯(lián)去獲取，而且展示的結(jié)果不夠一目了然。 11G以后這些信息全部可以在SQL MONITORING中找到答案，SQL MONITORING提供的功能還不僅僅是上面提到的這些，通過SQL MONITORING還可以輕松獲取語句的綁定變量、監(jiān)控索引的整個創(chuàng)建過程及創(chuàng)建完索引剩余的工作量。 文本會著重講解SQL MONITORING的核心功能，其他的相關(guān)信息就請讀者們?nèi)ケM情挖掘吧。

3什么SQL會被SQL MONITORING監(jiān)控到

對于絕大多數(shù)OLTP系統(tǒng)來說，SQL相對比較簡單，每次的運(yùn)行時間都非?？?，絕大部分SQL的響應(yīng)時間都應(yīng)該在10MS以下，優(yōu)化的復(fù)雜度也比較低，SQL MONITORING功能的出現(xiàn)并不是為了幫助DBA發(fā)現(xiàn)、診斷OLTP SQL的性能問題，而是為了加快DBA優(yōu)化數(shù)據(jù)倉庫類SQL的效率，這些SQL是偏OLAP系統(tǒng)的，特點(diǎn)是并發(fā)量低、運(yùn)行時間久、SQL復(fù)雜度高。滿足以下條件的任意SQL都會被SQL MONITORING監(jiān)控到：

如果串行執(zhí)行的SQL，消耗的CPU時間或IO時間超過5秒，那么這些SQL 將會被監(jiān)控到，通過修改隱含參數(shù)_sqlmon_threshold可以控制這一行為，默認(rèn)為5秒，如果設(shè)置為0將關(guān)閉SQL MONITORING功能 。注意我這里提到的是SQL消耗的CPU時間或IO時間，而不是SQL的執(zhí)行時間，之所以需要限制CPU時間或IO時間是為了防止數(shù)據(jù)庫某一時刻如果有大量lock/latch的話，那么將有大量的SQL滿足5秒執(zhí)行時間的條件，而SQL監(jiān)控本身比較消耗資源，需要拷貝運(yùn)行時的性能統(tǒng)計信息到SGA，每一個受監(jiān)控的SQL都有一個單獨(dú)的內(nèi)存結(jié)構(gòu)，在11G可能會導(dǎo)致大量的latch競爭，CPU飆高，12C對這個問題做了優(yōu)化不存在該問題了。如果你發(fā)現(xiàn)你的SQL運(yùn)行時間明顯超過了5秒但是卻沒被SQL MONITORING監(jiān)控到，那么你該仔細(xì)檢查是否是由于SQL本身消耗的CPU或IO并沒有超過5秒（由于鎖、網(wǎng)絡(luò)？）。

并行執(zhí)行的SQL將全部被監(jiān)控到，不需要等待CPU或IO時間超過5秒。對于這一點(diǎn)也比較好理解，一般并行查詢的SQL都是報表類或比較重的任務(wù)類的SQL，因此會自動打開SQL MONITORING的功能。

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

除了以上條件外，你還需要檢查一些系統(tǒng)參數(shù)是否設(shè)置正確：

statistics_level需要為TYPICAL（默認(rèn)）或者ALL.

control_management_pack_access需要為DIAGNOSTIC+TUNING（默認(rèn)）

SQL MONITORING并不會監(jiān)控非常大的執(zhí)行計劃，默認(rèn)不會監(jiān)控超過300行的執(zhí)行計劃，可以通過隱含參數(shù)_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，文章的后面也會有單獨(dú)一節(jié)簡單介紹如何通過SQL包DBMS_SQLTUNE來獲取Real Time SQL Monitoring的輸出。

首先登陸Oracle EMCC，進(jìn)入到【數(shù)據(jù)庫】頁面，選擇你需要監(jiān)控的數(shù)據(jù)庫，這里以clouddb1為例：

進(jìn)入到clouddb1數(shù)據(jù)庫后，選擇【性能】下的SQL監(jiān)視功能。

然后就進(jìn)入到了SQL MONTORING的主界面，這里包含了最近被監(jiān)控到的SQL，查看狀態(tài)列，可以知道SQL當(dāng)前的執(zhí)行狀態(tài)。

狀態(tài)列一般包含以下4種狀態(tài)：運(yùn)行、完成、排隊、錯誤，將鼠標(biāo)放在狀態(tài)列的符號上面，會看到符號所代表的狀態(tài)。這幾個狀態(tài)除了【排隊】狀態(tài)都比較好理解，排隊這個狀態(tài)只會在大于11GR2版本出現(xiàn)。ORACLE 11GR2在并行設(shè)置方面起了很大變化：自動并行度調(diào)整、并行語句隊列、內(nèi)存并行執(zhí)行，啟用此三項新特性需要設(shè)置參數(shù)parallel_degree_policy為auto，默認(rèn)值為MANUAL。設(shè)置為auto后，三項新特性全部被打開，這里我們主要說下并行語句隊列，啟用該特性后，通過設(shè)置parallel_servers_target來指定總的可用的并行子進(jìn)程的目標(biāo)數(shù)，運(yùn)行語句時，如果發(fā)現(xiàn)可用的并行進(jìn)程數(shù)已經(jīng)小于待運(yùn)行語句的目標(biāo)數(shù)，那么語句將會放入隊列中等待直到擁有足夠可用的并行進(jìn)程。一旦被加入隊列，你就會在SQL MONITORING的監(jiān)控頁面上看到，語句的當(dāng)前運(yùn)行狀態(tài)為【排隊】。

SQL監(jiān)視頁面顯示的SQL列表不是針對特定的SQL文本的，而是針對特定SQL語句的一次特定的執(zhí)行，因此如果兩個會話運(yùn)行相同的語句，你會在此列表中看到兩個單獨(dú)的條目，因此它與v$sql的表現(xiàn)行為不一樣，如果2個會話在運(yùn)行同一個SQL，在 v$sql里只能對應(yīng)到匯總了SQL執(zhí)行統(tǒng)計信息的一個記錄。針對SQL MONITOR的這個特點(diǎn)，允許你針對一個特定的用戶（他正在抱怨性能問題）檢查這個語句到底出了什么問題，而不是首先觀察匯總了所有用戶的語句級的性能指標(biāo)，像v$sql里的，再試著從中發(fā)現(xiàn)你所關(guān)注的用戶問題。

5詳解Real Time SQL Monitoring

找到Real Time SQL Monitoring的入口后，可以點(diǎn)擊SQL_ID進(jìn)入到Real Time SQL Monitoring的展示頁面，是不是很炫酷？

也許你對頁面上的很多指標(biāo)還感覺很陌生，別著急，我接下來會對頁面上的核心部分進(jìn)行介紹。

5.1 一般信息

一般信息部分顯示了SQL執(zhí)行的一些基本的細(xì)節(jié)，例如SQL的文本，SQL語句采用的并行度，SQL的執(zhí)行開始時間，本次統(tǒng)計值最后一次的刷新時間，執(zhí)行SQL的用戶、取數(shù)操作等。注意【SQL文本】后帶有…的標(biāo)記（紅色框標(biāo)記），點(diǎn)擊它，你會獲得完整的SQL語句文本，從版本11.2.0.2開始，你還會看到綁定變量的值。

例如：

上面的代碼我對一個帶有綁定變量的SQL以并行度6執(zhí)行了查詢，然后通過EMCC的SQL MONITORING頁面點(diǎn)擊【SQL文本】后面部分的…，這時會出現(xiàn)一個新窗口：

點(diǎn)擊show SQL Binds：

這時就可以看到綁定變量的列表，是不是非常的方便？

當(dāng)然就像上面提到的，SQL MONITORING主要用于監(jiān)控數(shù)據(jù)倉庫類型的SQL，對于長時間運(yùn)行查詢，是否應(yīng)該使用綁定變量是另外一個單獨(dú)的話題，在數(shù)據(jù)倉庫中，一般的最佳實踐為：不該為長時間運(yùn)行的查詢使用綁定變量，因為相對于整個SQL的運(yùn)行時間，SQL解析只占到可以忽略不計的時間，而且數(shù)據(jù)倉庫的系統(tǒng)本身的SQL執(zhí)行頻率也非常低，因此我們可以考慮對所有查詢都進(jìn)行硬解析，即使這樣會消耗掉一些響應(yīng)時間（可以忽略不計），但我們盡可能保證每一組參數(shù)組合都能生成一個最優(yōu)的執(zhí)行計劃，因為就綁定變量和文本常量來說，文本常量是最適合產(chǎn)生最優(yōu)執(zhí)行計劃的。

言歸正傳，就像上面已經(jīng)演示的，得到被監(jiān)控查詢的綁定變量值在Oracle 11.2.0.2或更高版本上變得容易，以前你可能需要查詢v$sql_bind_capture，dba_hist_sqlbind來獲取綁定變量的值（歷史綁定值），甚至通過oradebug errorstack這類命令來獲得當(dāng)前運(yùn)行SQL的正在使用的綁定變量的值，現(xiàn)在你終于可以拋棄這些方法了。

【一般信息】模塊還包含了取數(shù)操作（fetch calls）的次數(shù)，簡單來說它代表了數(shù)據(jù)庫和應(yīng)用端網(wǎng)絡(luò)交互的次數(shù)，如果是count,sum這類操作，一般這個值為1，只需要一次網(wǎng)絡(luò)交互，但是對于select *這樣的查詢，返回的數(shù)據(jù)量越大，這個值也會越大。

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

取數(shù)操作我們在后面的章節(jié)還會繼續(xù)講到，這里先點(diǎn)到為止。

5.2 時間和等待統(tǒng)計信息

這一部分內(nèi)容非常少，但是信息量非常大，顯示的是持續(xù)時間、數(shù)據(jù)庫時間和等待活動百分比。把鼠標(biāo)移動到數(shù)據(jù)庫時間的條狀圖上，會看到消耗在各類等待事件和CPU上的時間，這讓你知道你的SQL時間都花哪了，是IO操作多，還是CPU操作多，這非常的棒。等待活動百分比條狀圖則顯示了詳細(xì)的等待事件分布，例如上圖中藍(lán)色部分代表整個語句執(zhí)行過程中，45%的等待發(fā)生在direct path read temp這個等待事件上，注意這里等待活動百分比條狀圖的100（100%）指的是數(shù)據(jù)庫里的等待事件時間，而不是整個數(shù)據(jù)庫時間，即并不包括CPU時間，這部分的信息可以告訴DBA在SQL執(zhí)行過程中，遭遇了哪些等待事件，以及這些等待事件占取的比例，當(dāng)然，你應(yīng)該關(guān)注這些等待事件里最為惹眼的。

你可能對持續(xù)時間和數(shù)據(jù)庫時間的含義感到疑惑，沒關(guān)系，我們接下來就會著重介紹這兩個時間代表的含義和兩者的差異：

5.3 持續(xù)時間

持續(xù)時間（墻面時間）是用戶非常關(guān)注的時間，它顯示一個語句已經(jīng)處于活動狀態(tài)多久，它代表著語句從開始執(zhí)行直到結(jié)束的時間跨度，對于正在運(yùn)行的語句，則是從開始執(zhí)行到當(dāng)前的時間。當(dāng)然，最終用戶可能還要等待更長的時間，因為除了數(shù)據(jù)庫的響應(yīng)時間之外，時間還可能會被花在應(yīng)用系統(tǒng)上，或者是數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的網(wǎng)絡(luò)上。例如SQL的持續(xù)時間為5秒，但是應(yīng)用本身處理這些數(shù)據(jù)需要1秒，那么用戶端感受到的時間就可能是6秒，而不是數(shù)據(jù)庫端看到的持續(xù)時間5秒。

需要再次強(qiáng)調(diào)的是，持續(xù)時間衡量的是從SQL開始執(zhí)行即游標(biāo)打開直到游標(biāo)被關(guān)閉或取消的時間跨度，這意味著如果數(shù)據(jù)庫1分鐘內(nèi)完成一個查詢，但隨后產(chǎn)生的數(shù)百萬結(jié)果每次只能返回幾行，從應(yīng)用的角度看，這個查詢將需要很長的時間（需要數(shù)百萬次的網(wǎng)絡(luò)傳輸）才能完成，但是數(shù)據(jù)庫端只花了一點(diǎn)時間來處理。對于網(wǎng)絡(luò)傳輸?shù)那闆r，SQL MONITORING會有一個指標(biāo)進(jìn)行反應(yīng)，就是上面已經(jīng)提到過的取數(shù)操作（fetch calls），如果網(wǎng)絡(luò)傳輸量比較大的話，這個值會比較大。

例如我通過monitor這個hint強(qiáng)制讓ORACLE監(jiān)控這個SQL，這個SQL會返回大量的結(jié)果集給客戶端，我們通過EMCC來監(jiān)控這個SQL的相關(guān)監(jiān)控信息：

取數(shù)操作值非常大，因為數(shù)據(jù)庫要跟應(yīng)用（我們這里是SQLPLUS客戶端）有大量的網(wǎng)絡(luò)交互。

5.4 數(shù)據(jù)庫時間

數(shù)據(jù)庫時間即DB Time，顯示的是一個查詢在數(shù)據(jù)庫中執(zhí)行花費(fèi)的總時間，就DML操作來說，一般數(shù)據(jù)庫時間基本等于持續(xù)時間，因為DML操作不用返回結(jié)果集，沒有網(wǎng)絡(luò)交互時間，但是如果運(yùn)行的是一個SELECT語句并返回很多行，那么持續(xù)時間一般會大于數(shù)據(jù)庫時間，因為很多的網(wǎng)絡(luò)時間會被統(tǒng)計在持續(xù)時間中，數(shù)據(jù)庫會把數(shù)據(jù)分批發(fā)送給應(yīng)用程序，因此對于一個select語句你可能會看到數(shù)據(jù)庫時間只有30秒，而持續(xù)時間卻有50秒，其中30秒用于服務(wù)你的請求，其余的時間數(shù)據(jù)庫則是空閑的，等待應(yīng)用取下一批數(shù)據(jù)。我們再次看一下上面已經(jīng)使用過的一張圖，對于這個查詢來說，由于要返回大量的結(jié)果集給客戶端，因此持續(xù)時間遠(yuǎn)遠(yuǎn)大于數(shù)據(jù)庫時間。

對于串行查詢來說，持續(xù)時間都是大于等于數(shù)據(jù)庫時間的，但是對于并行查詢來說，情況有所改變：當(dāng)運(yùn)行一個并行查詢時，會有多個服務(wù)器進(jìn)程（甚至可能是多個服務(wù)器上的服務(wù)器進(jìn)程）參與進(jìn)來，每個服務(wù)器進(jìn)程都可以使用單獨(dú)的CPU資源，也就是1分鐘的墻面時間(wall time），但是數(shù)據(jù)庫時間可能有1*N分鐘（N為CPU邏輯核數(shù)），并行查詢最終的數(shù)據(jù)庫時間可能會比持續(xù)時間（wall time）長的多。

這個語句的持續(xù)時間只有19秒，而數(shù)據(jù)庫時間則有1.8分鐘，從【一般信息】的Execution Plan部分可以看到這個語句是以并行度6來運(yùn)行，這意味著數(shù)據(jù)庫里有多個進(jìn)程同時服務(wù)于這個查詢，所有這些會話的數(shù)據(jù)庫時間加上協(xié)調(diào)進(jìn)程的時間都被匯總到了數(shù)據(jù)庫時間中。數(shù)據(jù)庫時間代表著大致有多少時間必須在數(shù)據(jù)庫中完成，但是因為該語句是并行的，因此你其實不必等待那么長的時間。

5.5 IO統(tǒng)計信息

顯示了語句執(zhí)行時的一些關(guān)鍵的IO統(tǒng)計信息。

緩沖區(qū)獲取數(shù)，即buffer gets，顯示了數(shù)據(jù)庫層的邏輯IO總數(shù)。IO請求包含了總的IO請求次數(shù)，例如db file sequential read,db file scattered read,direct path read等等的次數(shù)，IO字節(jié)代表了讀取/寫入的IO的總字節(jié)數(shù)。通過條狀圖的不同有顏色部分，能獲取到更詳細(xì)的統(tǒng)計值，例如上圖中顯示10GB的IO字節(jié)，其中有42%是讀取請求產(chǎn)生的，等等。 需要注意的是，這里的IO請求次數(shù)和IO字節(jié)數(shù)統(tǒng)計可能超出你的預(yù)期，例如針對一個1G表的全表掃描按照某個字段排序，不但要統(tǒng)計表本身的IO，而且還要統(tǒng)計排序產(chǎn)生的讀、寫臨時段的IO。

5.6 詳細(xì)信息

詳細(xì)信息頁本身包含了一些子tab頁面，例如上圖中包含了【計劃統(tǒng)計信息】、【計劃】、【并行】、【活動】、【度量】子tab頁。詳細(xì)信息頁主要包含了執(zhí)行計劃在在行源（rowsource）細(xì)節(jié)上的一些統(tǒng)計信息。

首先我們先看下頁面的最左側(cè)一列，如果某個行源操作是被并行執(zhí)行的，會用多個小人的圖標(biāo)標(biāo)識，相反如果是被串行執(zhí)行的會用一個小人的圖標(biāo)標(biāo)識，如果你觀察仔細(xì)的話，會發(fā)現(xiàn)多個小人的圖標(biāo)有時也會有顏色的區(qū)分（下圖），這是因為并行執(zhí)行中，生產(chǎn)者和消費(fèi)者角色的不同導(dǎo)致的。例如圖中的紅色是生產(chǎn)者負(fù)責(zé)掃描表，而圖中藍(lán)色的進(jìn)程是消費(fèi)者負(fù)責(zé)把接收過來的數(shù)據(jù)做排序。

再者，我們會看到多人小圖標(biāo)的后面有向右箭頭的指示，這個代表了目前的查詢正在進(jìn)行，執(zhí)行計劃正在執(zhí)行到這個（些）行源（右箭頭所在的行源），也就是通過SQL MONITORING可以知道目前執(zhí)行計劃執(zhí)行到了哪一個行源，nice job！如果SQL執(zhí)行結(jié)束，或者這個（些）行源執(zhí)行結(jié)束，這些向右的小箭頭也會消失。

然后我們關(guān)注下【估計行數(shù)】和【實際行數(shù)】，如果兩者差異巨大，可能是由于你的數(shù)據(jù)庫系統(tǒng)統(tǒng)計信息缺失或者陳舊，或者是優(yōu)化器自身的算法導(dǎo)致。這里我并不會介紹CBO優(yōu)化的相關(guān)信息，讀者可以自行參閱相關(guān)文章?！緦嶋H行數(shù)】列真實的反應(yīng)了掃描進(jìn)程需要產(chǎn)生的工作量。

雖然Oracle CBO技術(shù)發(fā)展了這么久，相關(guān)書籍、文章也非常多，但是筆者認(rèn)為，閱讀、了解CBO最好的書籍仍然是jonathon lewis的基于成本的ORACLE優(yōu)化法則，不過中文版貌似已經(jīng)買不到了。