www.allbetgaming.net:Oracle SQL调优系列之SQL Monitor Report

admin/2020-06-20/ 分类:科技/阅读:

@

目录
  • 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 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

,

联博以太坊

www.326681.com采用以太坊区块链高度哈希值作为统计数据,联博以太坊统计数据开源、公平、无任何作弊可能性。联博统计免费提供API接口,支持多语言接入。

TAG:
阅读:
广告 330*360
广告 330*360
Sunbet_进入申博sunbet官网
微信二维码扫一扫
关注微信公众号
新闻自媒体 Copyright © 2002-2019 Sunbet 版权所有
二维码
意见反馈 二维码