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 |
Oracle dynamic performance views
| Last updated: July 07, 2009 |
No comments:
Post a Comment