Get parallel execution information for specific statement execution
get
/database/performance/sql_statements/{sql_id}/monitor/{sql_exec_id},{sql_exec_start}/parallelism
Oracle Tuning Pack licence is required for this service. Returns information on a monitored statement that is executed in parallel. Using GV$SQL_MONITOR view, the information includes the Parallel Coordinator and the instance(s) where it was executed. A client requires SQL Administrator role to invoke this service.
Request
Supported Media Types
- application/json
Path Parameters
-
sql_exec_id: number(double)
Execution identifier.
-
sql_exec_start: string(date-time)
Time when the execution started.
-
sql_id: string
SQL identifier.
Response
Supported Media Types
- application/json
200 Response
Returns information on a monitored statement. The structure of the data may vary depending on the database used.
Root Schema : PerformanceSQLStatementMonitorParallelism
Type:
object
Title:
PerformanceSQLStatementMonitorParallelism
Describes the parallel execution of an SQL Statement. Using GV$SQL_MONITOR view, the information includes the Parallel Coordinator and the instance(s) where it was executed.
Show Source
-
count(optional):
integer(int32)
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(int32)
The actual page size limit on number of records applied by the server.
-
links(optional):
array links
-
offset(optional):
integer(int32)
The actual index from which the item resources are returned.
Nested Schema : Item3
Type:
object
Title:
Show Source
Item3
-
application_wait_time(optional):
number(double)
Application wait time (in microseconds); updated as the statement executes.
-
buffer_gets(optional):
number(double)
Number of buffer get operations; updated as the statement executes.
-
buffer_gets_max(optional):
number(double)
The maximum number of buffer get operations by a monitored SQL execution for the same SQL Statement.
-
buffer_gets_prop(optional):
number(double)
The proportional number of buffer get operations compared to other execution records for the same SQL Statement.
-
cluster_wait_time(optional):
number(double)
Cluster wait time (in microseconds); updated as the statement executes.
-
concurrency_wait_time(optional):
number(double)
Concurrency wait time (in microseconds); updated as the statement executes.
-
cpu_time(optional):
integer(int32)
CPU time (in microseconds) used by this cursor for parsing, executing, and fetching.
-
db_time(optional):
number(double)
The greater of either ELAPSED_TIME or a total of the following CPU_TIME + QUEUING_TIME + APPLICATION_WAIT_TIME + CONCURRENCY_WAIT_TIME + CLUSTER_WAIT_TIME + USER_IO_WAIT_TIME + PLSQL_EXEC_TIME + JAVA_EXEC_TIME.
-
db_time_max(optional):
number(double)
The maximum time spent for an execution of the SQL Statement.
-
db_time_prop(optional):
number(double)
The proportional time spent for this GV$SQL_MONITOR record compared to other execution records for the same SQL Statement.
-
dop_downgrade(optional):
string
Percentage of actual number of parallel execution servers allocated to execute the query compared to the total number of parallel execution servers requested to execute the query.
-
inst_id(optional):
number(double)
The Instance Number from the GV$SQL_MONITOR record.
-
io_bytes(optional):
number(double)
Total of number of bytes read from disks and bytes written to disks by the monitored SQL.
-
io_bytes_max(optional):
number(double)
The maximum number of bytes read from disks and bytes written to disks by a monitored SQL execution for the same SQL Statement.
-
io_bytes_prop(optional):
number(double)
The proportional number of bytes read from disks and bytes written to disks by the monitored SQL compared to other execution records for the same SQL Statement.
-
io_requests(optional):
number(double)
Total of number of physical read and write I/O requests issued by the monitored SQL.
-
io_requests_max(optional):
number(double)
The maximum number of physical read and write I/O requests issued by a monitored SQL execution for the same SQL Statement.
-
io_requests_prop(optional):
number(double)
The proportional number of physical read and write I/O requests issued by the monitored SQL compared to other execution records for the same SQL Statement.
-
java_exec_time(optional):
number(double)
Java execution time (in microseconds); updated as the statement executes.
-
name(optional):
string
Name of either the Instance, Parallel Coordinator, Parallel Group, Parallel Set or Parallel Server that corresponds to the GV$SQL_MONITOR record.
-
node_id(optional):
integer(int32)
Used as a distinguishing value in the Parallelism metadata. The value may be an Instance Number to represent the execution on an instance, 10000 when a Parallel Coordinator or some other value derived from the SERVER_GROUP data.
-
other_wait_time(optional):
number(double)
The greater of either zero or a total of the following CPU_TIME - QUEUING_TIME - APPLICATION_WAIT_TIME - CONCURRENCY_WAIT_TIME - CLUSTER_WAIT_TIME - USER_IO_WAIT_TIME - PLSQL_EXEC_TIME - JAVA_EXEC_TIME.
-
parent_node_id(optional):
number(double)
The value may be an Instance Number to represent the execution of Parallel Coordinator or Server Group on an instance. May be NULL.
-
plsql_exec_time(optional):
number(double)
PL/SQL execution time (in microseconds); updated as the statement executes.
-
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).
-
queuing_time(optional):
integer(int32)
Duration of time (in microseconds) spent by SQL in the statement queue.
-
server_set(optional):
integer(int32)
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.
-
servers_allocated(optional):
number(double)
Actual number of parallel execution servers allocated to execute the query.
-
servers_requested(optional):
number(double)
Total number of parallel execution servers requested to execute the monitored SQL.
-
user_io_wait_time(optional):
number(double)
User I/O Wait Time (in microseconds); updated as the statement executes.