EBS_DBA_最佳化:掌握SQL Monitor這些特性，SQL最佳化將如有神助！ (轉)

標籤：

SQL分析的苦與痛

 

對於線上的SQL語句，很多DBA都總會有一些疑問，看著執行計畫cost還不錯，但是實際執行的時候效果卻有天壤之別，這是為什麼呢？ 

 

對於一個龐大的SQL語句，看著得到的執行計畫卻不知道瓶頸在哪裡，SQL語句太複雜，執行計畫看起來更複雜，要讀明白它掌握要領也不是一件容易的事情。

偶爾會有一些朋友問我，怎麼去讀一個執行計畫，這個無論說得怎麼細，似乎都不得要領，畢竟純文字描述和圖形的效果還是有很大的差別。     

如果你在11g的版本中，SQL Monitor就是一個大大的福利，上面的問題可能都會在此化解，這個特性你值得掌握，如果你還沒有好好掌握它，就實在太可惜了。   

至於SQL Monitor更多的細節就不一一描述了，11g推出的這個特性其實和MySQL裡的慢日誌有些類似，MySQL裡面的閾值要更低一些，SQL Monitor是5秒。一旦達到這個標準，就會進收集到v$sql_monitor這個視圖中,可以得到詳細的會話資訊和執行計畫。

 

指定的SQL使用SQL Monitor監控

 

如果某條語句想使用SQL Monitor來監控，單獨來定製，也不是一件難事。可以使用Hint monitor來完成。比如這樣的形式：

select /*+ monitor */ count(*) from emp where  xxxxx

 

如果確認不需要放入監控範圍，也可以使用no_monitor來定製，比如這樣的形式

select /*+ no_monitor */ count(*) from emp where  xxxxx

 

查看SQL Monitor報告的方式：

 

查看產生的監控資訊，可以使用如下的方式：

select dbms_sqltune.report_sql_monitor from dual;

 

除了調用DBMS_SQLTUNE包得到報告外，此外可以採用以下2種方式來得到SQL Monitor內容：

 (1)EM的：Performance —〉右下角的SQL Monitoring —〉Monitored SQL Executions  (2) SQL Developer的：Tools Monitor SQL

 

格式豐富的SQL Monitor報告

 

當然這些都是SQL Monitor常規的一些知識點，還不足以讓我興趣大開。我感興趣的是它強大的UI展現能力。聽起來這個似乎和這個特性好像關聯不大，你如果看到效果就知道了，還是那句話，一個很複雜，抽象的事物如果用圖形表示，要遠比文字豐富形象得多。

SQL Monitor報告格式，大體有以下幾種格式。TEXT，HTML，ACTIVE，XML四種，不過我們著重來說一下前三種，雖然看起來格式繁多，我們打一個比方就容易理解了。有些手機會按照配置來冠以各種名號。

 

SQL Monitor的報告也可以這麼區分，TEXT格式是標準版，HTML是高配版，ACTIVE是尊享版，我們不來虛的，來實際看看效果。

 

文字格式設定的效果如下：

 

 

HTML格式的效果如下：

 

SQL資訊，會話資訊，執行計畫一目瞭然，非常貼心。

 

  那麼ACTIVE格式是什麼意思呢，就是最炫最全面的效果，開啟的時候竟然還有一個小的flash效果。

 

ACTIVE格式的報告效果如下：

 

 

有的朋友可能看到會說，這和HTML的效果有啥差別啊，有的，我再給一張圖你就明白了。

 

執行計畫原來可以這麼讀。全表掃描，索引掃描，表串連資訊都一目瞭然，越是複雜的執行計畫這種方式越省事。
SQL文本和綁定變數的資訊，點擊SQL_ID就會彈出一個小視窗來。

 

得到TEXT，HTML報告的指令碼

 

好吧，看起來這麼炫，想得到這個報告難不難呢，其實很簡單，就一個SQL語句就能搞定，絕對沒有標題黨的意思。

如果想寫成shell指令碼，就是嵌入一個SQL語句，本質就是調用dbms_sqltune.report_sql_monitor即可。指令碼內容如下：

tmp_sql_id=$1 sqlplus -s  / as sysdba<<EOF

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000 SELECT dbms_sqltune.report_sql_monitor( sql_id => ‘${tmp_sql_id}‘, report_level => ‘ALL‘, type=>‘TEXT‘) FROM dual; EOF

 

唯一的差別就是在type的地方。TEXT，HTML的就設定為TEXT，HTML即可。

 

得到ACTIVE格式報告的方法

 

如果是ACTIVE格式的，這個咱得稍說一下背景。

這個功能在Enterprise Manager中查看是很自然的一件事情，如果沒有安裝EM,我們不能因為這個專門去部署一個EM來不是。要達到同樣的效果，有兩種方式，一種是通過網路下載所需的指令碼，即線上查看，另外一種是離線查看，需要提前把幾個指令碼下載到本地即可。所以要得到一個報告並顯示出來，這些工作都可以搞定，辦法總比困難多。

 

如果要得到一個線上的報告，可以使用如下的方式得到，指令碼內容如下：

tmp_sql_id=$1 sqlplus -s  / as sysdba<<EOF

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000 SELECT dbms_sqltune.report_sql_monitor( sql_id => ‘${tmp_sql_id}‘, report_level => ‘ALL‘, type=>‘ACTIVE‘) FROM dual; EOF

 

你沒有看錯，這個和TEXT，HTML的方式幾乎一樣。可以在瀏覽器中開啟的時候有一些差別，那就是這種方式會嘗試從Oracle的網站下載幾個指令碼，這種方式如果沒有網路是無法開啟報告的。

 

下載的指令碼是哪些呢，這就牽扯出離線查看的內容。

 

我們可以在本地設定一個目錄結構，設定一個類似的網站http://www.jeanron100在本地建立一個jeanron100的目錄：

mkdir -p jeanron100/sqlmon

 

然後下載相應的指令碼：

wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/scripts/flashver.js wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/scripts/document.js wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

如果在windows上下載，直接輸入上面的URL即可。

 

產生SQL語句的ACTIVE報告使用如下的方式即可：

SELECT dbms_sqltune.report_sql_monitor( sql_id => ‘${tmp_sql_id}‘, report_level => ‘ALL‘, type=>‘ACTIVE‘,base_path =>‘http://www.jeanron100/sqlmon‘)  FROM dual;

 

把拷貝到的結果以HTML格式儲存，在本地的目錄下開啟，離線報告建議用IE開啟會流暢一些，有的瀏覽器可能有其他的安全限制。如果感興趣也可以讀一下產生的內容，裡面大量使用了XML解析的方式。

 

SQL Monitor List的使用

 

如果在這個基礎上還想做些什麼的話，其實還有不少，比如我們查看v$sql_monitor的結果。

SQL> select count(*)from v$sql_monitor;

  COUNT(*)

----------

       160

 

竟然有這麼多的SQL語句，我們有沒有方法來得到一個概覽資訊呢。

 

 

使用的語句如下：

SELECT dbms_sqltune.report_sql_monitor_list( type => ‘HTML‘,  report_level => ‘ALL‘) AS report  FROM dual;

 

這個方法你用還是不用都在那兒。當然可以直接從v$sql_monitor裡抽出資料來也可以，整體而言效果還是蠻不錯的。

 

強大的SQL Detail Report

 

還有沒有好玩的功能了呢，有的。SQL Detail Report，這個報告比SQL Monitor的ACTIVE報告還要更豐富一些。同樣一個語句在不同時間的執行情況都一目瞭然，這對於分析效能問題尤其有協助。

 

 

還可以看到曆史執行過程中處理的行數，執行次數，DB time的情況，指標非常多，非常全。

 

 

得到這麼一個報告麻煩嗎，還是一個SQL語句即可，不過調用的是另外一個方法了，注意此處的報告格式還是ACTIVE。

set pages 0

set linesize 200

col comm format a300

set long 99999999

SELECT dbms_sqltune.report_sql_detail(

sql_id => ‘xxxx‘,

report_level => ‘ALL‘,

type=>‘ACTIVE‘

) comm

FROM dual;

 

如果查看一個指定時間範圍內的報告資訊，可以採用下面的方式即可。

SELECT dbms_sqltune.report_sql_detail(

sql_id => ‘xxxx‘,

report_level => ‘ALL‘,

type=>‘ACTIVE‘,

start_time=>to_date(‘xxxx‘,‘yyyymmddhh24miss‘),

duration=>‘xxxx‘

)

FROM dual;

 

 

掃雷環節

 

最後來掃幾個雷。

 

對於TEXT，HTML格式的報告，這個對環境的依賴很低，對網路沒有依賴，是非常穩定的實現，推薦使用。

 

對於SQL Monitor List報告，其實完全可以使用v$sql_monitor來得到，有些環境運行可能有下面的報錯資訊。

 

 

對於SQL Monitor的ACTIVE格式報告，線上方式還是推薦使用，離線查看可以先行下載指令碼到本地。

 

如果報告顯示的錯誤如下：

 

 

對於線上查看來說，可能是產生的報告格式的問題，看看是不是預先設定了這些選項。

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000

 

對於離線查看可能就是下載的指令碼路徑問題了，調整一下即可。

 

對於SQL Monitor的更多定製

 

v$sql_monitor類似v$session的機制，某個SQL語句造成的問題已經發生了一段時間，想查看之前的執行情況，v$sql_monitor基本上就無從得知了，因為資料已經被刷出去了。

 

我們可以在後台啟用一個JOB不定時的去尋找，把這部分資料給緩衝起來，比如是採用一般檔案的形式來收集，然後通過時間戳記來進行區別管理。

 

 

相信到此大家對於SQL Monitor有了一個簡單的認識和理解，希望有所協助。

 

作者介紹  楊建榮

• DBAplus社群聯合發起人。現就職於搜狐暢遊，Oracle ACE-A、YEP成員，超7年資料庫開發和營運經驗，擅長電信資料業務、資料庫遷移和效能調優。持Oracle 10G OCP，OCM，MySQL OCP認證，《Oracle DBA工作筆記》作者。

EBS_DBA_最佳化:掌握SQL Monitor這些特性，SQL最佳化將如有神助！ (轉)