V$SQL_PLAN_MONITOR

9.59 V$SQL_PLAN_MONITOR

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. As with V$SQL_MONITOR, statistics exposed in V$SQL_PLAN_MONITOR are generally updated every second when the statement executes. These statistics are recycled on the same basis as V$SQL_MONITOR.

To eliminate the overhead of SQL plan monitoring, statistics collected for each operation of the plan do not record timing information such as elapsed time, CPU time, or I/O time. Instead, this timing information can be estimated quite accurately by joining V$SQL_PLAN_MONITOR with V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and SQL_PLAN_LINE_ID (simply named PLAN_LINE_ID in V$SQL_PLAN_MONITOR). The result of that join is a sample of the activity performed by each operation in the plan, from which an estimate of CPU time and wait time can be derived. This can be achieved by breaking statement level monitoring time statistics found in V$SQL_MONITOR in proportion to the number of samples found in V$ACTIVE_SESSION_HISTORY for the corresponding activity type.

Column	Datatype	Description
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data
`KEY`	`NUMBER`	Foreign key to efficiently join `V$SQL_PLAN_MONITOR` with `V$SQL_MONITOR` (see `V$SQL_MONITOR`)
`STATUS`	`VARCHAR2(19)`	SQL execution status: `EXECUTING` - SQL statement is still executing `DONE (ERROR)` - Execution terminated with an error `DONE (FIRST N ROWS)` - Execution terminated by the application before all rows were fetched `DONE (ALL ROWS)` - Execution terminated and all rows were fetched `DONE` - Execution terminated (parallel execution)
`FIRST_REFRESH_TIME`	`DATE`	Time when monitoring of the SQL statement started
`LAST_REFRESH_TIME`	`DATE`	Time when statistics were last updated for the SQL statement
`FIRST_CHANGE_TIME`	`DATE`	First time a row was produced by this operation
`LAST_CHANGE_TIME`	`DATE`	Last time a row was produced by this operation
`REFRESH_COUNT`	`NUMBER`	Number of times statistics have been refreshed
`SID`	`NUMBER`	Session identifier executing (or having executed) the SQL statement being monitored
`PROCESS_NAME`	`VARCHAR2(5)`	Process name identifier
`SQL_ID`	`VARCHAR2(13)`	SQL identifier
`SQL_EXEC_START`	`DATE`	Time when the execution started
`SQL_EXEC_ID`	`NUMBER`	Execution identifier
`SQL_PLAN_HASH_VALUE`	`NUMBER`	SQL plan hash value
`SQL_CHILD_ADDRESS`	`RAW(4 \| 8)`	Address of the child cursor
`PLAN_PARENT_ID`	`NUMBER`	ID of the next execution step that operates on the output of the current step
`PLAN_LINE_ID`	`NUMBER`	Plan line number for the entry
`PLAN_OPERATION`	`VARCHAR2(30)`	Plan operation name (from `V$SQL_PLAN`)
`PLAN_OPTIONS`	`VARCHAR2(30)`	Plan option name (from `V$SQL_PLAN`)
`PLAN_OBJECT_OWNER`	`VARCHAR2(128)`	Name of the user who owns the schema containing the table or index
`PLAN_OBJECT_NAME`	`VARCHAR2(128)`	Name of the table or index
`PLAN_OBJECT_TYPE`	`VARCHAR2(80)`	Type of the object
`PLAN_DEPTH`	`NUMBER`	Depth (or level) of the operation in the tree. It is not necessary to issue a `CONNECT BY` statement to get the level information, which is generally used to indent the rows from the `PLAN_TABLE` table. The root operation (statement) is level 0.
`PLAN_POSITION`	`NUMBER`	Order of processing for all operations that have the same `PARENT_ID`
`PLAN_COST`	`NUMBER`	Cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is NULL.
`PLAN_CARDINALITY`	`NUMBER`	Estimate, by the cost-based optimizer, of the number of rows produced by the operation
`PLAN_BYTES`	`NUMBER`	Estimate, by the cost-based optimizer, of the number of bytes produced by the operation
`PLAN_TIME`	`NUMBER`	Elapsed time (in seconds) of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is NULL.
`PLAN_PARTITION_START`	`VARCHAR2(256)`	Start partition of a range of accessed partitions
`PLAN_PARTITION_STOP`	`VARCHAR2(256)`	Stop partition of a range of accessed partitions
`PLAN_CPU_COST`	`NUMBER`	CPU cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is NULL.
`PLAN_IO_COST`	`NUMBER`	I/O cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is NULL.
`PLAN_TEMP_SPACE`	`NUMBER`	Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is NULL.
`STARTS`	`NUMBER`	Number of times this operation was executed. For example, an operation is executed multiple times when it is on the right side of a nested-loop join (once for each row of the left input of that nested-loop join).
`OUTPUT_ROWS`	`NUMBER`	Number of rows produced by this operation since the execution started. This number is cumulated for all executions of this operation. Divide by the value of the STARTS column to compute the average number of rows per execution of the operation. Note that the value in the `STARTS` column is equal to or higher than the value in the `OUTPUT_ROWS` column. The value will usually be equal, but depending on internal optimizations a higher value may be seen.
`IO_INTERCONNECT_BYTES`	`NUMBER`	Number of I/O bytes exchanged between Oracle Database and the storage system. Maintained only after Oracle starts to monitor the execution.
`PHYSICAL_READ_REQUESTS`	`NUMBER`	Number of physical read I/O requests issued by the monitored SQL. Maintained only after Oracle starts to monitor the execution.
`PHYSICAL_READ_BYTES`	`NUMBER`	Number of bytes read from disks by the monitored SQL. Maintained only after Oracle starts to monitor the execution.
`PHYSICAL_WRITE_REQUESTS`	`NUMBER`	Number of physical write I/O requests issued by the monitored SQL. Maintained only after Oracle starts to monitor the execution.
`PHYSICAL_WRITE_BYTES`	`NUMBER`	Number of bytes written to disks by the monitored SQL. Maintained only after Oracle starts to monitor the execution.
`WORKAREA_MEM`	`NUMBER`	Amount of memory (in bytes) used by the operation when the query is executing; NULL if the execution is done. This applies only to operations using a work area, such as sort, hash-join, group-by, and so on.
`WORKAREA_MAX_MEM`	`NUMBER`	Maximum value (in bytes) for `WORKAREA_MEM`; NULL if the operation is not using a work area. When the execution is finished, this value will hold the maximum amount of memory consumed by this operation during the execution of the statement.
`WORKAREA_TEMPSEG`	`NUMBER`	Amount of temporary space (in bytes) used by the operation when the query is executing; NULL if the operation has not spilled to disk or if the execution is finished
`WORKAREA_MAX_TEMPSEG`	`NUMBER`	Maximum value (in bytes) for `WORKAREA_TEMPSEG`; NULL if this operation never spilled to disk. When the execution is done, this value will hold the maximum amount of temporary space consumed by this operation during the entire execution.
`OTHERSTAT_GROUP_ID`	`NUMBER`	Plan line statistic group identifier (see `GROUP_ID` column in `V$SQL_MONITOR_STATNAME`)
`OTHERSTAT_1_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 1 of that plan line
`OTHERSTAT_1_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_1_VALUE`	`NUMBER`	Value of statistic number 1 of that plan line
`OTHERSTAT_2_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 2 of that plan line
`OTHERSTAT_2_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_2_VALUE`	`NUMBER`	Value of statistic number 2 of that plan line
`OTHERSTAT_3_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 3 of that plan line
`OTHERSTAT_3_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_3_VALUE`	`NUMBER`	Value of statistic number 3 of that plan line
`OTHERSTAT_4_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 4 of that plan line
`OTHERSTAT_4_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_4_VALUE`	`NUMBER`	Value of statistic number 4 of that plan line
`OTHERSTAT_5_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 5 of that plan line
`OTHERSTAT_5_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_5_VALUE`	`NUMBER`	Value of statistic number 5 of that plan line
`OTHERSTAT_6_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 6 of that plan line
`OTHERSTAT_6_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_6_VALUE`	`NUMBER`	Value of statistic number 6 of that plan line
`OTHERSTAT_7_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 7 of that plan line
`OTHERSTAT_7_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_7_VALUE`	`NUMBER`	Value of statistic number 7 of that plan line
`OTHERSTAT_8_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 8 of that plan line
`OTHERSTAT_8_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_8_VALUE`	`NUMBER`	Value of statistic number 8 of that plan line
`OTHERSTAT_9_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 9 of that plan line
`OTHERSTAT_9_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_9_VALUE`	`NUMBER`	Value of statistic number 9 of that plan line
`OTHERSTAT_10_ID`	`NUMBER`	Statistic identifier (see `ID` column in `V$SQL_MONITOR_STATNAME`) for statistic number 10 of that plan line
`OTHERSTAT_10_TYPE`	`NUMBER`	Reserved for internal use
`OTHERSTAT_10_VALUE`	`NUMBER`	Value of statistic number 10 of that plan line
`OTHER_XML`	`CLOB`	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: Name of the schema against which the query was parsed Release number of the Oracle Database that produced the explain plan Hash value associated with the execution plan Name (if any) of the outline or the SQL profile used to build the execution plan Indication of whether or not dynamic statistics were used to produce the plan The outline data, a set of optimizer hints that can be used to regenerate the same plan
`PLAN_OPERATION_INACTIVE`	`NUMBER`	Indicates whether this plan operation was part of the final resolved plan