Oracle SQL調優系列之SQL Monitor Report

smileNicky發表於2020-06-19
OracleSQL

@

目錄

1、SQL Monitor簡介

sql monitor是oracle官方提供的自動監控符合特定條件的SQL，用於收集執行時的細節資訊的監控工具，常用於sql調優和系統效能監控

2、捕捉sql的前提

sql monitor 捕捉sql的前提：

  • 並行執行的sql語句
  • 單次執行消耗的CPU或IO超過5秒
  • statistics_level級別必須是TYPICAL 或者ALL
  • 使用/* +MONITOR*/ HINT的SQL語句

3、SQL Monitor 引數設定

  • STATISTICS_LEVEL必須設定為：'TYPICAL'（預設）或者 'ALL'
  • CONTROL_MANAGEMENT_PACK_ACCESS設定為：'DIAGNOSTIC+TUNING'

檢視statistics_level引數

show parameter statistics_level;

在這裡插入圖片描述
建議還是改變Session就可以

alter session set statistics_level=ALL;

檢視引數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，然後資料會存在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改下，然後執行如下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

獲取到對應報告，可以看到執行計劃、Buffer Gets 等等資訊
在這裡插入圖片描述

4.4、ACTIVE格式

ACTIVE格式需要下載相應的flash元件、指令碼，詳細見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、檢視整個系統的效能

  • 檢視整個系統的效能
   SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;

相關SQL指令碼下載：sql download

相關文章