Oracle SQL调优系列之SQL Monitor Report

骑猪看日落 2021-08-30 22:26 406阅读 0赞

### Oracle SQL调优系列之SQL Monitor Report ###

*   *   *  1、SQL Monitor简介
         *  2、捕捉sql的前提
         *  3、SQL Monitor 参数设置
         *  4、SQL Monitor Report
         *   *  4.1、SQL\_ID获取
             *  4.2、Text文本格式
             *  4.3、Html格式
             *  4.4、ACTIVE格式
             *  4.5 SQL Monitoring list
         *  5、SQL Monitor Report查询
         *   *  5.1、查看所有的sql monitor report
             *  5.2、查看某个sql的sql monitor report
             *  5.3、查看某个sql的整体性能
             *  5.4、查看整个系统的性能

### 1、SQL Monitor简介 ###

SQL调优系列博客链接：[SQL调优专栏][SQL]

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;

![在这里插入图片描述][20200619135120113.png]  
建议还是改变Session就可以

alter session set statistics_level=ALL;

查看参数CONTROL\_MANAGEMENT\_PACK\_ACCESS

show parameter CONTROL_MANAGEMENT_PACK_ACCESS;

![在这里插入图片描述][20200619135107352.png]

### 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  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]

#### 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 等等信息  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70 1]

#### 4.4、ACTIVE格式 ####

ACTIVE格式需要下载相应的flash组件、脚本，详细见[SQL Monitor Report 使用详解][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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70 2]

### 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][]

[SQL]: https://blog.csdn.net/u014427391/category_8679315.html
[20200619135120113.png]: /images/20210813/33bd456b44e14c9a816ecaab6a5be60a.png
[20200619135107352.png]: /images/20210813/b873576927a6408f994b3217e7fd7d45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]: /images/20210813/7422fb098ebb411d90ab0304c543c90c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70 1]: /images/20210813/39b150c48612472f9cfacb872e37907a.png
[SQL Monitor Report]: http://blog.itpub.net/27067062/viewspace-2129635/#_SQL_Monitor_report%E8%8E%B7%E5%8F%96%E6%96%B9%E6%B3%95
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70 2]: /images/20210813/e16c1d8323734f2b9ac4c1f4bebc3ddd.png
[sql download]: https://github.com/u014427391/oracle_optimize_sql/tree/master/sql%20monitor