Oracle SQL調(diào)優(yōu)系列之SQL Monitor Report
1、SQL Monitor簡介
sql monitor是oracle官方提供的自動監(jiān)控符合特定條件的SQL，用于收集執(zhí)行時的細(xì)節(jié)信息的監(jiān)控工具，常用于sql調(diào)優(yōu)和系統(tǒng)性能監(jiān)控
2、捕捉sql的前提
sql monitor 捕捉sql的前提：
并行執(zhí)行的sql語句
單次執(zhí)行消耗的CPU或IO超過5秒
statistics_level級別必須是TYPICAL 或者ALL
使用/* +MONITOR*/ HINT的SQL語句
3、SQL Monitor 參數(shù)設(shè)置
STATISTICS_LEVEL必須設(shè)置為：'TYPICAL'（缺?。┗蛘?'ALL'
CONTROL_MANAGEMENT_PACK_ACCESS設(shè)置為：'DIAGNOSTIC+TUNING'
查看statistics_level參數(shù)
show parameter statistics_level;
建議還是改變Session就可以
alter session set statistics_level=ALL;
查看參數(shù)CONTROL_MANAGEMENT_PACK_ACCESS
show parameter CONTROL_MANAGEMENT_PACK_ACCESS;
4、SQL Monitor Report
本博客采用DBMS_SQLTUNE包DBMS_SQLTUNE.report_sql_monitor的方式獲取，報告格式有：'TEXT'，'HTML'，'XML' ，'ACTIVE'，其中'ACTIVE'只在11g R2以后才支持
4.1、SQL_ID獲取
sql monitor使用，必須在sql中使用/* +MONITOR*/?Hint，然后數(shù)據(jù)會存在v$sql_monitor表里
隨意找條sql，注意要加/*+ moniotr*/
select /*+ moniotr*/ a.user_code, a.full_name, a.user_pwd, c.unit_code, c.unit_name
from base_user a
left join (select ur.user_code, ur.unit_code
from t_user_role ur
where ur.user_role < 10) b
on a.user_code = b.user_code
left join t_unit_info c
on b.unit_code = c.unit_code
where c.unit_code in
(select uinfo.unit_code
from t_unit_info uinfo
start with uinfo.unit_code = '15803'
connect by prior uinfo.unit_code = uinfo.para_unit_code);
提供sql查詢，獲取sql_id
select sql_id,sql_text from v$sql_monitor where sql_text like '%t_unit_info%
4.2、Text文本格式
將上面查詢到的sql_id改下，然后執(zhí)行如下SQL：
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
spool report_sql_monitor_text.txt
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => 'g9rtj389t0g66',
TYPE => 'TEXT',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;
spool off
獲取到text格式的sql monitor
4.3、Html格式
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
spool report_sql_monitor_html.html
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => 'g9rtj389t0g66',
TYPE => 'HTML',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;
spool off
獲取到對應(yīng)報告，可以看到執(zhí)行計劃、Buffer Gets 等等信息
4.4、ACTIVE格式
ACTIVE格式需要下載相應(yīng)的flash組件、腳本，詳細(xì)見SQL Monitor Report 使用詳解
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
spool report_sql_monitor_active.html
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => '2rjh5d5k2yujz',
TYPE => 'ACTIVE',
REPORT_LEVEL => 'ALL',
BASE_PATH => 'http://ip/script') AS REPORT
FROM dual;
spool off
4.5 SQL Monitoring list
如果要獲取所有sql monitor，就可以使用如下SQL：
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL report_sql_monitor_list.html
SELECT dbms_sqltune.report_sql_monitor_list(
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
5、SQL Monitor Report查詢
提供sql monitor常用的查詢腳本
5.1、查看所有的sql monitor report
查看所有的sql monitor report
select dbms_sqltune.report_sql_monitor from dual;
5.2、查看某個sql的sql monitor report
查看某個sql的sql monitor report
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '2rjh5d5k2yujz', type => 'TEXT') from dual;
5.3、查看某個sql的整體性能
查看某個sql的整體性能
SELECT DBMS_SQLTUNE.report_sql_monitor_list(sql_id=>'2rjh5d5k2yujz',type =>'TEXT',report_level => 'ALL') AS report FROM dual;
5.4、查看整個系統(tǒng)的性能
查看整個系統(tǒng)的性能
SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;
騰訊、阿里、滴滴后臺面試題匯總總結(jié) — （含答案）
面試：史上最全多線程面試題 ！
最新阿里內(nèi)推Java后端面試題
JVM難學(xué)？那是因為你沒認(rèn)真看完這篇文章
關(guān)注作者微信公眾號 —《JAVA爛豬皮》
了解更多java后端架構(gòu)知識以及最新面試寶典
看完本文記得給作者點贊+在看哦~~~大家的支持，是作者源源不斷出文的動力
作者：SmileNicky
出處：https://www.cnblogs.com/mzq123/p/13163298.html