SQL Monitoring

Real-Time SQL Monitoring in 11g [ID 1229904.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Purpose

The real-time SQL monitoring is a 11g new feature that enables you to monitor the performance of SQL statements while they are executing.

Scope and Application

DBA, Application users and developers

Real-Time SQL Monitoring in 11g

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

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