Get monitoring statistics for a SQL Statement
get
/database/performance/sql_statements/{sql_id}/monitor/
Oracle Tuning Pack licence is required for this service. Returns all GV$SQL_MONITOR records for a specified SQL_ID. GV$SQL_MONITOR will contain statistics only for SQL statements whose execution have been (or are being) monitored by Oracle. A client requires SQL Administrator role to invoke this service.
Request
Path Parameters
-
sql_id: string
SQL identifier.
Query Parameters
-
limit(optional): integer(int32)
The maximum number of records to return.
-
q(optional): string
Filtering is the process of limiting a collection resource by using a per-request dynamic filter definition across multiple page resources, where each page contains a subset of items found in the complete collection. Filtering enables efficient traversal of large collections.
Response
Supported Media Types
- application/json
200 Response
Description of all monitored SQL statements in the database.
Root Schema : PerformanceSQLStatementMonitors
Type:
Show Source
object-
count(optional):
integer
Total number of records in the current response.
-
hasMore(optional):
boolean
Indicates if there are more records to be retrieved.
-
items(optional):
array items
-
limit(optional):
integer
The actual page size limit on number of records applied by the server.
-
links(optional):
array links
-
offset(optional):
integer
The actual index from which the item resources are returned.
Nested Schema : items
Type:
Show Source
array-
Array of:
object PerformanceSQLStatementMonitorsItem
This object provides statistics for a single execution of an SQL statement. The list of attributes may vary depending on database version.
Nested Schema : PerformanceSQLStatementMonitorsItem
Type:
objectThis object provides statistics for a single execution of an SQL statement. The list of attributes may vary depending on database version.
Show Source
-
action(optional):
string
Name of the executing action when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure.
-
application_wait_time(optional):
integer
Application wait time (in microseconds); updated as the statement executes.
-
binds_xml(optional):
string
Information about bind variables used with the SQL, such as name, position, value, data type, and so on (stored in XML format).
-
buffer_gets(optional):
integer
Number of buffer get operations; updated as the statement executes.
-
client_identifier(optional):
string
Client identifier from the user session.
-
client_info(optional):
string
Client information for the user session.
-
cluster_wait_time(optional):
integer
Cluster wait time (in microseconds); updated as the statement executes.
-
con_id(optional):
integer
The ID of the container to which the data pertains.
-
con_name(optional):
string
Container name of the object. The value of this column is NULL in non-CDBs.
-
concurrency_wait_time(optional):
integer
Concurrency wait time (in microseconds); updated as the statement executes.
-
cpu_time(optional):
integer
CPU time (in microseconds); updated as the statement executes.
-
current_user(optional):
integer
Unique number identifying the current user.
-
current_username(optional):
integer
Username for the current user.
-
dbop_exec_id(optional):
integer
Database operation execution identifier for the current execution. If the type is SQL, the DBOP_EXEC_ID will be NULL.
-
dbop_name(optional):
string
Database operation name. If the type is SQL, the DBOP_NAME will be NULL.
-
direct_writes(optional):
integer
Number of direct writes; updated as the statement executes.
-
disk_reads(optional):
integer
Number of disk reads; updated as the statement executes.
-
ecid(optional):
string
Execution context identifier (sent by Application Server).
-
elapsed_time(optional):
integer
Elapsed time (in microseconds); updated as the statement executes.
-
error_facility(optional):
string
Error facility in case a SQL fails to execute successfully (for example, ORA in case of ORA-00932).
-
error_message(optional):
string
Detailed error message displayed corresponding to the error number and error facility when a SQL fails to execute successfully.
-
error_number(optional):
string
Error number encountered in case a SQL fails to execute successfully (for example, 932 in case of ORA-00932).
-
exact_matching_signature(optional):
integer
Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
-
fetches(optional):
integer
Number of fetches associated with the SQL statement; updated as the statement executes.
-
first_refresh_time(optional):
string
Time when monitoring of the SQL statement started, generally a few seconds after execution start time.
-
force_matching_signature(optional):
integer
Same as EXACT_MATCHING_SIGNATURE but literals in the SQL text are replaced by binds.
-
in_dbop_exec_id(optional):
integer
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.
-
in_dbop_name(optional):
string
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.
-
inst_id(optional):
integer
Instance number from which the associated GV$SQL_MONITOR view information was obtained.
-
io_cell_offload_eligible_bytes(optional):
integer
Number of I/O bytes which can be filtered by the Exadata storage system.
-
io_cell_offload_returned_bytes(optional):
integer
Number of filtered bytes returned by Exadata cells (that is, the number of bytes returned after processing has been offloaded on the Exadata cells).
-
io_cell_uncompressed_bytes(optional):
integer
Number of uncompressed bytes (that is, size after decompression) that are offloaded to the Exadata cells.
-
io_interconnect_bytes(optional):
integer
Number of I/O bytes exchanged between Oracle Database and the storage system.
-
is_adaptive_plan(optional):
string
Indicates whether the statistics are from an adaptive plan (Y) or not (N).
-
is_final_plan(optional):
string
Indicates whether the statistics are from the final plan (Y) or not (N).
-
is_full_sqltext(optional):
string
Indicates whether the SQL_TEXT column has the entire SQL text (Y) or not (N).
-
java_exec_time(optional):
integer
Java execution time (in microseconds); updated as the statement executes.
-
key(optional):
integer
Artificial join key to efficiently join GV$SQL_MONITOR with its corresponding plan level monitoring statistics stored in GV$SQL_PLAN_MONITOR.
-
last_refresh_time(optional):
string
Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
-
links(optional):
array links
-
module(optional):
string
Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure.
-
other_xml(optional):
string
Additional information about SQL execution stored in XML format.
-
physical_read_bytes(optional):
integer
Number of bytes read from disks by the monitored SQL.
-
physical_read_requests(optional):
integer
Number of physical read I/O requests issued by the monitored SQL.
-
physical_write_bytes(optional):
integer
Number of bytes written to disks by the monitored SQL.
-
physical_write_requests(optional):
integer
Number of physical write I/O requests issued by the monitored SQL.
-
plsql_entry_object_id(optional):
integer
Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack.
-
plsql_entry_subprogram_id(optional):
integer
Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack.
-
plsql_exec_time(optional):
integer
PL/SQL execution time (in microseconds); updated as the statement executes.
-
plsql_object_id(optional):
integer
Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL.
-
plsql_subprogram_id(optional):
integer
Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL.
-
process_name(optional):
string
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).
-
program(optional):
string
Name of the operating system program that issued the monitored SQL.
-
px_is_cross_instance(optional):
string
Indicates whether the SQL statement ran parallel across multiple instances (Y) or not (N).
-
px_maxdop(optional):
integer
Maximum degree of parallelism for any plan operation executed on behalf of the monitored SQL.
-
px_maxdop_instances(optional):
integer
Number of database instances touched at the maximum degree of parallelism.
-
px_qcinst_id(optional):
integer
Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL.
-
px_qcsid(optional):
integer
Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL.
-
px_server#(optional):
integer
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(optional):
integer
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(optional):
integer
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_servers_allocated(optional):
integer
Actual number of parallel execution servers allocated to execute the query
-
px_servers_requested(optional):
integer
Total number of parallel execution servers requested to execute the monitored SQL.
-
queuing_time(optional):
integer
Duration of time (in microseconds) spent by SQL in the statement queue.
-
refresh_count(optional):
integer
Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes).
-
report_id(optional):
integer
Unique ID of the XML report stored in Automatic Workload Repository (AWR) for this monitored entity.
-
rm_consumer_group(optional):
string
The current consumer group for this SQL operation.
-
rm_last_action(optional):
string
The most recent action that was taken on this SQL operation by Resource Manager.
-
rm_last_action_reason(optional):
string
The reason for the most recent action that was taken on this SQL operation by Resource Manager.
-
rm_last_action_time(optional):
string
The time of the most recent action that was taken on this SQL operation by Resource Manager.
-
service_name(optional):
string
Service name of the user session.
-
session_serial#(optional):
integer
Session serial number executing the statement being monitored.
-
sid(optional):
integer
Session identifier executing (or having executed) the SQL statement being monitored.
-
sql_child_address(optional):
string
Address of the child cursor (can be used with SQL_ID to join with GV$SQL).
-
sql_exec_id(optional):
integer
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_exec_start(optional):
string
Time when the execution started.
-
sql_full_plan_hash_value(optional):
integer
Numeric representation of the complete SQL plan for this cursor. Comparing one SQL_FULL_PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line). Note that the SQL_FULL_PLAN_HASH_VALUE cannot be compared across databases releases. It is not backward compatible.
-
sql_id(optional):
string
SQL identifier of the statement being monitored.
-
sql_plan_hash_value(optional):
integer
SQL plan hash value.
-
sql_text(optional):
string
Up to the first 2000 characters of the text of the SQL being monitored.
-
status(optional):
string
SQL execution status.
-
user#(optional):
integer
User ID of the database user who issued the SQL being monitored.
-
user_io_wait_time(optional):
integer
User I/O Wait Time (in microseconds); updated as the statement executes.
-
username(optional):
string
User name of the database user who issued the SQL being monitored.