Oracle 11g: Real-Time SQL Monitoring

V$SQL_MONITOR
V$SQL_PLAN_MONITOR
DBMS_SQLTUNE.REPORT_SQL_MONITOR

11g中引入了新的动态性能视图V$SQL_MONITOR和V$SQL_PLAN_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动，同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行，V$SQL_MONITOR视图中的统计信息将被实时刷新，频率为每秒1次。SQL语句执行完成后，监视信息将不会被立即删除，Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制，默认为60s)，最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。

It is enabled by default for long running statements if parameter CONTROL_MANAGEMENT_PACK_ACCESS if it is set to “DIAGNOSTIC+TUNING” and STATISTICS_LEVEL=ALL|TYPICAL

It can be enabled at statement level as well with /*+ MONITOR */ hint or disabled with /*+ NO_MONITOR */ hint.

There are some defaults defined which can be altered by setting hidden parameters:
_sqlmon_max_plan - Maximum number of plans entries that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines - Number of plan lines beyond which a plan cannot be monitored (default 300)






Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR

Oracle 11g Real Time SQL Monitoring