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:
|
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 |