10.71 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) |
|
|
Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following:
|
|
|
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 |
|
|
Unique ID of the XML report of a PDB monitored entity that is stored in the CDB’s Automatic Workload Repository (AWR) tables |
|
|
Container ID of the PDB |
|
|
Identifier of the parsing schema |
|
|
Name of the parsing schema |
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