The real-time SQL monitoring is an Oracle database 11g new feature that enables you to monitor the performance of SQL statements while they are executing. Real-time SQL monitoring is automatically started when a SQL command runs in parallel or when it has consumed at least 5 seconds of CPU or I/O time in a single execution.
PREREQUISITES
statistics_level = TYPICAL or ALL
control_management_pack_access = DIAGNOSTIC+TUNING
HINTS RELATED TO REAL-TIME SQL MONITORING
MONITOR: To force real-time SQL monitoring
NO_MONITOR: To prevent the query from being monitored
MONITORING STATISTICS
You can monitor the statistics for SQL command execution using the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views. After monitoring is initiated, an entry is added to the V$SQL_MONITOR dynamic performance view. These statistics are refreshed in near real-time as the command executes, generally once every second. When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real-time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.
IMPORTANT COLUMNS OF V$SQL_MONITOR AND V$SQL_PLAN_MONITOR
KEY NUMBER: Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR.
STATUS : SQL execution status. Values are below :
- EXECUTING – SQL statement is still executing
- DONE (ERROR) – Execution terminated with an error
- DONE (FIRST N ROWS) – Execution terminated by the application before all rows were fetched.
- DONE (ALL ROWS) – Execution terminated and all rows were fetched.
- DONE – Execution terminated (parallel execution).
EXAMPLES
1. To find information about currently long-running queries:
set lines 200 pages 200 col sql_text for a50 col username for a12 col sid for 9999 col key for 99999999999999 select key, sid, username, sql_id, sql_plan_hash_value plan_hash, elapsed_time, cpu_time, buffer_gets, disk_reads, substr(sql_text,1,50) sql_text from v$sql_monitor where status = 'EXECUTING';
2. To find the execution plan for a key value.
set lines 200 pages 200
col operation for a32
col plan_options for a20
col plan_object_name for a24
col id for 999
select plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost
from v$sql_monitor q, v$sql_plan_monitor p
where q.key = p.key
and q.key = [ key value >]
order by id;
3. To find the execution plans of currently long-running queries:
set lines 200 pages 200
col operation for a32
col plan_options for a20
col plan_object_name for a24
col id for 999
break on sql_id on plan_hash
select sql_id, sql_plan_hash_value plan_hash, plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost
from v$sql_plan_monitor
where status = 'EXECUTING'
order by key, id;