Tuesday, July 7, 2009

V$SQL_PLAN_MONITOR

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. As with V$SQL_MONITOR, statistics exposed in V$SQL_PLAN_MONITOR are generally updated every second when the statement executes. These statistics are recycled on the same basis as V$SQL_MONITOR.

To eliminate the overhead of SQL plan monitoring, statistics collected for each operation of the plan do not record timing information such as elapsed time, CPU time, or I/O time. Instead, this timing information can be estimated quite accurately by joining V$SQL_PLAN_MONITOR with V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and SQL_PLAN_LINE_ID (simply named PLAN_LINE_ID in V$SQL_PLAN_MONITOR). The result of that join is a sample of the activity performed by each operation in the plan, from which an estimate of CPU time and wait time can be derived. This can be achieved by breaking statement level monitoring time statistics found in V$SQL_MONITOR in proportion to the number of samples found in V$ACTIVE_SESSION_HISTORY for the corresponding activity type.

Column

Datatype

Description

KEY

NUMBER

Foreign key to efficiently join V$SQL_PLAN_MONITOR with V$SQL_MONITOR (see V$SQL_MONITOR)

STATUS

VARCHAR2(19)

SQL execution status:

  • 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)

FIRST_REFRESH_TIME

DATE

Time when monitoring of the SQL statement started

LAST_REFRESH_TIME

DATE

Time when statistics were last updated for the SQL statement

FIRST_CHANGE_TIME

DATE

First time a row was produced by this operation

LAST_CHANGE_TIME

DATE

Last time a row was produced by this operation

REFRESH_COUNT

NUMBER

Number of times statistics have been refreshed

SID

NUMBER

Session identifier executing (or having executed) the SQL statement being monitored

PROCESS_NAME

VARCHAR2(5)

Process name identifier

SQL_ID

VARCHAR2(13)

SQL identifier

SQL_EXEC_START

DATE

Time when the execution started

SQL_EXEC_ID

NUMBER

Execution identifier

SQL_PLAN_HASH_VALUE

NUMBER

SQL plan hash value

SQL_CHILD_ADDRESS

RAW(4 | 8)

Address of the child cursor

PLAN_LINE_ID

NUMBER

Plan line number for the entry

PLAN_OPERATION

VARCHAR2(30)

Plan operation name (from V$SQL_PLAN)

PLAN_OPTIONS

VARCHAR2(30)

Plan option name (from V$SQL_PLAN)

STARTS

NUMBER

Number of times this operation was executed. For example, an operation is executed multiple times when it is on the right side of a nested-loop join (once for each row of the left input of that nested-loop join).

OUTPUT_ROWS

NUMBER

Number of rows produced by this operation since the execution started. This number is cumulated for all executions of this operation. Divide by the value of the STARTS column to compute the average number of rows per execution of the operation.

WORKAREA_MEM

NUMBER

Amount of memory (in bytes) used by the operation when the query is executing; NULL if the execution is done. This applies only to operations using a work area, such as sort, hash-join, group-by, and so on.

WORKAREA_MAX_MEM

NUMBER

Maximum value (in bytes) for WORKAREA_MEM; NULL if the operation is not using a work area. When the execution is finished, this value will hold the maximum amount of memory consumed by this operation during the execution of the statement.

WORKAREA_TEMPSEG

NUMBER

Amount of temporary space (in bytes) used by the operation when the query is executing; NULL if the operation has not spilled to disk or if the execution is finished

WORKAREA_MAX_TEMPSEG

NUMBER

Maximum value (in bytes) for WORKAREA_TEMPSEG; NULL if this operation never spilled to disk. When the execution is done, this value will hold the maximum amount of temporary space consumed by this operation during the entire execution.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 07, 2009

V$SQL_MONITOR

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

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.

Column

Datatype

Description

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

VARCHAR2(19)

SQL execution status:

  • 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)

FIRST_REFRESH_TIME

DATE

Time when monitoring of the SQL statement started, generally a few seconds after execution start time

LAST_REFRESH_TIME

DATE

Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.

REFRESH_COUNT

NUMBER

Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)

SID

NUMBER

Session identifier executing (or having executed) the SQL statement being monitored

PROCESS_NAME

VARCHAR2(5)

Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)

SQL_ID

VARCHAR2(13)

SQL identifier of the statement being monitored

SQL_EXEC_START

DATE

Time when the execution started

SQL_EXEC_ID

NUMBER

Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.

SQL_PLAN_HASH_VALUE

NUMBER

SQL Plan hash value

SQL_CHILD_ADDRESS

RAW(4 | 8)

Address of the child cursor (can be used with SQL_ID to join with V$SQL)

SESSION_SERIAL#

NUMBER

Session serial number executing the statement being monitored

PX_SERVER#

NUMBER

Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).

PX_SERVER_GROUP

NUMBER

Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.

PX_SERVER_SET

NUMBER

Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server

PX_QCINST_ID

NUMBER

Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL

PX_QCSID

NUMBER

Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL

ELAPSED_TIME

NUMBER

Elapsed time (in microseconds); updated as the statement executes

CPU_TIME

NUMBER

CPU time (in microseconds); updated as the statement executes

FETCHES

NUMBER

Number of fetches associated with the SQL statement; updated as the statement executes

BUFFER_GETS

NUMBER

Number of buffer get operations; updated as the statement executes

DISK_READS

NUMBER

Number of disk reads; updated as the statement executes

DIRECT_WRITES

NUMBER

Number of direct writes; updated as the statement executes

APPLICATION_WAIT_TIME

NUMBER

Application wait time (in microseconds); updated as the statement executes

CONCURRENCY_WAIT_TIME

NUMBER

Concurrency wait time (in microseconds); updated as the statement executes

CLUSTER_WAIT_TIME

NUMBER

Cluster wait time (in microseconds); updated as the statement executes

USER_IO_WAIT_TIME

NUMBER

User I/O Wait Time (in microseconds); updated as the statement executes

PLSQL_EXEC_TIME

NUMBER

PL/SQL execution time (in microseconds); updated as the statement executes

JAVA_EXEC_TIME

NUMBER

Java execution time (in microseconds); updated as the statement executes

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 07, 2009