10.55 V$SQL_MONITOR
V$SQL_MONITOR
displays SQL statements whose execution have been (or are being) monitored by Oracle.
This view contains global, high-level information about simple and composite database operations.
Oracle Database monitors simple database operations, which are top SQL statements and PL/SQL subprograms, when any of the following conditions is true:
-
The operations run in parallel.
-
The operations have consumed at least 5 seconds of CPU or I/O time in a single execution.
-
Tracking for the operations is forced by the /*+ MONITOR */ hint.
For simple database operations, monitoring statistics are not cumulative over several executions. In this case, one entry in V$SQL_MONITOR
is dedicated to a single execution of a SQL statement. If the database monitors two executions of the same SQL statement, then each execution has a separate entry in V$SQL_MONITOR
.
For simple database operations, V$SQL_MONITOR
has one entry for the parallel execution coordinator process and one entry for each parallel execution server process. Each entry has corresponding entries in V$SQL_PLAN_MONITOR
. Because the processes allocated for the parallel execution of a SQL statement are cooperating for the same execution, these entries share the same execution key (the combination of SQL_ID
, SQL_EXEC_START
, and SQL_EXEC_ID
).
Oracle Database monitors composite database operations when either of the following conditions is true:
-
A database operation was started with
DBMS_SQL_MONITOR.BEGIN_OPERATION
and the operation has consumed at least 5 seconds of CPU or I/O time. -
Tracking for the operation is forced by setting
FORCE_TRACKING
toY
inDBMS_SQL_MONITOR.BEGIN_OPERATION
.
For composite database operations, each row contains an operation whose statistics are accumulated over the SQL statements and PL/SQL subprograms that run in the same session as part of the operation.
The V$SQL_MONITOR
view contains a subset of the statistics available in V$SQL
. However, unlike V$SQL
, monitoring statistics are not cumulative over several executions. Instead, one entry in V$SQL_MONITOR
is dedicated to a single execution of a SQL statement. If the database monitors two executions of the same SQL statement, then each execution has a separate entry in V$SQL_MONITOR
.
The primary key is the combination of the columns SQL_ID
, SQL_EXEC_START
, and SQL_EXEC_ID
.
V$SQL_MONITOR
has one entry for the parallel execution coordinator process, and one entry for each parallel execution server process. Each entry has corresponding entries in V$SQL_PLAN_MONITOR
. Because the processes allocated for the parallel execution of a SQL statement are cooperating for the same execution, these entries share the same execution key (the composite SQL_ID
, SQL_EXEC_START
, and SQL_EXEC_ID
). You can aggregate the execution key to determine the overall statistics for a parallel execution.
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 |
---|---|---|
|
|
Artificial join key to efficiently join |
|
|
Unique ID of the XML report stored in Automatic Workload Repository (AWR) for this monitored entity |
|
|
SQL execution status:
|
|
|
User ID of the database user who issued the SQL being monitored |
|
|
User name of the database user who issued the SQL being monitored |
|
|
Name of the executing module when sampled, as set by the |
|
|
Name of the executing action when sampled, as set by the |
|
|
Service name of the user session |
|
|
Client identifier from the user session |
|
|
Client information for the user session |
|
|
Name of the operating system program that issued the monitored SQL |
|
|
Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack |
|
|
Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack |
|
|
Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL |
|
|
Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL |
|
|
Time when monitoring of the SQL statement started, generally a few seconds after execution start time |
|
|
Time when statistics in |
|
|
Number of times |
|
|
Database operation execution identifier for the current execution. If the type is SQL, the |
|
|
Database operation name. If the type is SQL, the |
|
|
Session identifier executing (or having executed) the SQL statement being monitored |
|
|
Process name identifier executing (or having executed)the statement; |
|
|
SQL identifier of the statement being monitored |
|
|
Up to the first 2000 characters of the text of the SQL being monitored |
|
|
Indicates whether the |
|
|
Time when the execution started |
|
|
Execution identifier. Together, the three columns |
|
|
SQL plan hash value |
|
|
Numeric representation of the complete SQL plan for this cursor. Comparing one |
|
|
Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings. |
|
|
Same as |
|
|
Address of the child cursor (can be used with |
|
|
Session serial number executing the statement being monitored |
|
|
Indicates whether the SQL statement ran parallel across multiple instances ( |
|
|
Maximum degree of parallelism for any plan operation executed on behalf of the monitored SQL |
|
|
Number of database instances touched at the maximum degree of parallelism |
|
|
Total number of parallel execution servers requested to execute the monitored SQL |
|
|
Actual number of parallel execution servers allocated to execute the query |
|
|
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 |
|
|
Logical parallel execution server group number to which |
|
|
Number ( |
|
|
Instance identifier where the parallel execution coordinator runs; NULL if |
|
|
Session identifier for the parallel execution coordinator; NULL if |
|
|
Error number encountered in case a SQL fails to execute successfully (for example, 932 in case of ORA-00932) |
|
|
Error facility in case a SQL fails to execute successfully (for example, ORA in case of ORA-00932) |
|
|
Detailed error message displayed corresponding to the error number and error facility when a SQL fails to execute successfully |
|
|
Information about bind variables used with the SQL, such as name, position, value, data type, and so on (stored in XML format) |
|
|
Additional information about SQL execution stored in XML format |
|
|
Elapsed time (in microseconds); updated as the statement executes |
|
|
Duration of time (in microseconds) spent by SQL in the statement queue |
|
|
CPU time (in microseconds); updated as the statement executes |
|
|
Number of fetches associated with the SQL statement; updated as the statement executes |
|
|
Number of buffer get operations; updated as the statement executes |
|
|
Number of disk reads; updated as the statement executes |
|
|
Number of direct writes; updated as the statement executes |
|
|
Number of I/O bytes exchanged between Oracle Database and the storage system |
|
|
Number of physical read I/O requests issued by the monitored SQL |
|
|
Number of bytes read from disks by the monitored SQL |
|
|
Number of physical write I/O requests issued by the monitored SQL |
|
|
Number of bytes written to disks by the monitored SQL |
|
|
Application wait time (in microseconds); updated as the statement executes |
|
|
Concurrency wait time (in microseconds); updated as the statement executes |
|
|
Cluster wait time (in microseconds); updated as the statement executes |
|
|
User I/O Wait Time (in microseconds); updated as the statement executes |
|
|
PL/SQL execution time (in microseconds); updated as the statement executes |
|
|
Java execution time (in microseconds); updated as the statement executes |
|
|
The most recent action that was taken on this SQL operation by Resource Manager. Its value is one of the following:
For the last value, <CG NAME> is the name of the consumer group that the SQL operation was switched to. If the Resource Plan has since been changed then <CG NAME> is the ID of the consumer group. |
|
|
The reason for the most recent action that was taken on this SQL operation by Resource Manager. Its value is one of the following:
|
|
|
The time of the most recent action that was taken on this SQL operation by Resource Manager |
|
|
The current consumer group for this SQL operation |
|
|
The ID of the container to which the data pertains. Possible values include:
|
|
|
Container name of the object. The value of this column is NULL in non-CDBs. |
|
|
Execution context identifier (sent by Application Server) |
|
|
Indicates whether the statistics are from an adaptive plan (Y) or not (N). |
|
|
Indicates whether the statistics are from the final plan (Y) or not (N). |
|
|
If the SQL that is monitored was executed by a session that was also monitored by a database operation (DBOP), then this column specifies the name of that DBOP |
|
|
If the SQL that is monitored was executed by a session that was also monitored by a database operation (DBOP), then this column specifies the execution ID of that DBO |
|
|
Number of uncompressed bytes (that is, size after decompression) that are offloaded to the Exadata cells See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
Number of I/O bytes which can be filtered by the Exadata storage system See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
Number of filtered bytes returned by Exadata cells (that is, the number of bytes returned after processing has been offloaded on the Exadata cells) See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
Unique number identifying the current user |
|
|
Username for the current user |
See Also:
-
Oracle Database SQL Tuning Guide for more information about monitoring database operations
-
Oracle Database PL/SQL Packages and Types Reference for more information about the
DBMS_APPLICATION_INFO.SET_MODULE
procedure -
Oracle Database PL/SQL Packages and Types Reference for more information about the
DBMS_APPLICATION_INFO.SET_ACTION
procedure