Real-Time SQL Monitoring with /*+MONITOR*/ Hint in Oracle

As of Oracle 11g R1, long-running queries or queries that we run with the / * MONITOR * / hint are automatically monitored. During the monitoring, statistics about SQL execution are collected every second.

The value of the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be ‘DIAGNOSTIC + TUNING’, and the value of STATISTICS_LEVEL parameter should be ALL or TYPICAL for monitoring.

Monitoring will start automatically when the following conditions occur.

• If the query has consumed more than 5 seconds of CPU time
• If the query has consumed more than 5 seconds of I / O time
• If the query was run in parallel
• If the query was run with / * + MONITOR * / hint

If we want to monitor a query, we can run it with / * + MONITOR * /. If we don’t want to monitor, we can run it with / * + NO_MONITOR * / hint.

Information about the monitored queries can be found in the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views.

V$SQL_MONITOR: Displays SQL statements monitored by Oracle. A record is added here each time the monitored query runs.

V$SQL_PLAN_MONITOR: Displays plan-level monitoring statistics for each SQL statement in V$SQL_MONITOR.

There are some hidden parameters related to monitoring:

_sqlmon_max_plan: The maximum number of plan entries that can be monitored. The default value is 20 per CPU.
_sqlmon_max_planlines: Number of plan lines in which a plan cannot be tracked The default value is 300.

It is useful to increase the values ​​of these hidden parameters to display the monitoring results.

You can query as follows by changing parameter values session-based.

With the monitored data, the dbms_sqltune.report_sql_monitor function can be used to generate a report for a query in V$SQL_MONITOR. We can execute this function with sql id information for a specific query as follows.