Sql_Monitor的触发条件、使用方法和注意事项

Sql_Monitor

开启实时SQL监控的条件

1 parallel语句

2 消耗超过5秒的CPU或者I/O时间

3 使用hint  monitor

还需要如下三个条件

• A Diagnostics and Tuning Pack License
• STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL'
• CONTROL_MANAGEMENT_PACK_ACCESS parameter set to  'DIAGNOSTIC+TUNING'.

监控的数据放入两个视图V$SQL_MONITOR and V$SQL_PLAN_MONITOR，一个放SQL信息，一个放执行计划信息

收集的统计信息每秒刷新一次数据。

抓取SQL的执行信息不会立即清除，该SQL执行完毕后至少保留1分钟

DBMS_SQLTUNE.REPORT_SQL_MONITOR

The REPORT_SQL_MONITOR function is used to return a SQL monitoring report for a specific SQL statement. The SQL statement can be identified using a variety of parameters.

The function accepts some optional parameters, the  most common ones are:

• SQL_ID - The SQL_ID of the query of interest. When NULL (the default) the last monitored statement is targeted.
• REPORT_LEVEL - The amount of information displayed in the report. 
• The basic allowed values are 'NONE', 'BASIC', 'TYPICAL' or 'ALL', 
• The default is 'TYPICAL' which is sufficent in most cases.
• TYPE - The format used to display the report ('TEXT', 'HTML', 'XML' or 'ACTIVE'). 
• The 'ACTIVE' parameter is new in Oracle 11g Release 2 and displays the output using HTML and Flash. An Internet connection is needed to use the 'ACTIVE' parameter.
• SESSION_ID - Targets a subset of queries based on the specified SID. 
• Use SYS_CONTEXT('USERENV','SID') for the current session. 
• The default is NULL.

使用步骤：

1 获得sql_id.

SET LINESIZE 300

COLUMN sql_text FORMAT A100

SELECT sql_id, status, sql_text FROM v$sql_monitor;

2 通过调用DBMS_SQLTUNE.report_sql_monitor生成html格式的报告

SET LONG 1000000 

SET FEEDBACK OFF

spool monitor_sql.html 

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'fnj7fdyravrrt',type=> 'HTML')

AS report FROM dual;

spool of

报告生成才当前目录下

3 生成text版本信息

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '0tqfh0cggfg0v', type => 'TEXT')

AS report FROM dual;

其他注意事项

1 受到内存和执行计划行数的限制 

_sqlmon_max_planlines     默认300

_sqlmon_max_plan            默认20*CPU数

SELECT   ksppinm, ksppstvl, ksppdesc  

  FROM   x$ksppi x, x$ksppcv y  

 WHERE   x.indx = y.indx AND  ksppinm ='_sqlmon_max_planlines';    <<<<300

SELECT   ksppinm, ksppstvl, ksppdesc  

  FROM   x$ksppi x, x$ksppcv y  

 WHERE   x.indx = y.indx AND  ksppinm ='_sqlmon_max_plan';    <<<<40

2 如果SQL执行时间少于5秒不被记录，此时可以通过使用hint实现 /*+ monitor */