oracle sql monitor report,Oracle SQL调优系列之SQL Monitor Report

青旅半醒 2023-01-19 07:25 15阅读 0赞

、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;

![3cafd3fcd1e2a21d2efd3429994393ab.png][]

建议还是改变Session就可以alter session set statistics\_level=ALL;

查看参数CONTROL\_MANAGEMENT\_PACK\_ACCESSshow parameter CONTROL\_MANAGEMENT\_PACK\_ACCESS;

![b4e58e96fd44211e87bbe79c23ab29f3.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\_idselect 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

![3f8063fbdb544e2d93f318f379e68708.png][]

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等等信息

![5515ea4bcf6be91eabe6a3fb7a737402.png][]

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

![2fd60accb6b056e1db4428f2c5457a7e.png][]

5、SQL Monitor Report查询

提供sql monitor常用的查询脚本

5.1、查看所有的sql monitor report查看所有的sql monitor reportselect dbms\_sqltune.report\_sql\_monitor from dual;

5.2、查看某个sql的sql monitor report查看某个sql的sql monitor reportSELECT 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;

[3cafd3fcd1e2a21d2efd3429994393ab.png]: /images/20221021/e6030d5bd0824b558dfda62109e91dca.png
[b4e58e96fd44211e87bbe79c23ab29f3.png]: /images/20221021/abe0607a42044a1eabd8be412d479e83.png
[3f8063fbdb544e2d93f318f379e68708.png]: /images/20221021/5607a09007ff421b9581aaafbbfd13a0.png
[5515ea4bcf6be91eabe6a3fb7a737402.png]: /images/20221021/993e467a7d22496aa99a48fdd5a0ddb3.png
[2fd60accb6b056e1db4428f2c5457a7e.png]: /images/20221021/2a73383d0d8c4e0eb32fe41c4f415bfe.png