专栏首页Nicky's blogOracle SQL调优系列之SQL Monitor Report

Oracle SQL调优系列之SQL Monitor Report

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle sql共享池$sqlarea分析SQL资源使用情况

    遇到需要排查一个系统使用sql的情况,可以通过查询Oracle的$sql、$ssssion、$sqlarea进行统计排查

    SmileNicky
  • 静态代理和动态代理简介

    在某些情况下,一个客户不想或者不能直接引用一个对象,此时可以通过一个称之为“代理”的第三者来实现间接引用。代理对象可以在客户端和目标对象之间起到中介的作用,并且...

    SmileNicky
  • 设计模式之代理模式(结构型)

    代理模式:代理模式就是引入一个代理对象,通过代理对象实现对原对象的引用。代理模式是一种对象结构型。

    SmileNicky
  • sqlite - java 初学

    进来准备使用一种embedded database,即嵌入式数据库,方便随项目本地存储。目前学习打算是sqlite和H2。 document:http://ww...

    Ryan-Miao
  • Mybatis中 $ 和 # 千万别乱用!

    这是一次代码优化过程中发现的问题,在功能优化后发现部分数据查不到出来了,问题就在于一条sql上的#和$。

    攻城狮的那点事
  • MyBatis中的$和#,用不好,准备走人!

    https://m.toutiaocdn.com/i6685496024770806280

    Java技术栈
  • Mybatis中 $ 和 # 千万不要乱用!

    这是一次代码优化过程中发现的问题,在功能优化后发现部分数据查不到出来了,问题就在于一条sql上的#和$。

    程序猿DD
  • 不要折磨我了,Mybatis 中$和#千万不要乱用!

    这是一次代码优化过程中发现的问题,在功能优化后发现部分数据查不到出来了,问题就在于一条sql上的#和$。

    用户4143945
  • 工作发狂:Mybatis 中$和#千万不要乱用!

    这是一次代码优化过程中发现的问题,在功能优化后发现部分数据查不到出来了,问题就在于一条sql上的#和$。

    良月柒
  • MyBatis中的$和#,用不好,准备走人!

    https://m.toutiaocdn.com/i6685496024770806280

    Java技术江湖

扫码关注云+社区

领取腾讯云代金券