Overview
V$PLAN_CACHE_PLAN_STAT displays the status of each object cached by the current tenant in the plan cache on the current server.
Notice
This table caches not only SQL plan objects, but also PL objects, such as anonymous blocks, PL packages, and PL functions. Some fields are valid only for specific objects.
Field description
| Field name | Type | Nullable | Description |
|---|---|---|---|
| TENANT_ID | NUMBER(38) | NO | The ID of the tenant. |
| SVR_IP | VARCHAR2(32) | NO | The IP address of the OBServer. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer. |
| PLAN_ID | NUMBER(38) | NO | The ID of the cached object. |
| SQL_ID | VARCHAR2(32) | NO | The SQL ID corresponding to the cached object. If it is a PL object, this field is NULL. |
| TYPE | NUMBER(38) | NO | For SQL plan caching, this field indicates the type of the plan. Valid values: |
| IS_BIND_SENSITIVE | NUMBER(38) | NO | Indicates whether Adaptive Cursor Sharing (ACS) needs to be enabled for the plan. |
| IS_BIND_AWARE | NUMBER(38) | NO | Indicates that ACS is enabled for the plan. |
| DB_ID | NUMBER(38) | NO | The ID of the database. |
| STATEMENT | VARCHAR2(65536) | NO | This field is a parameterized SQL statement for an SQL plan, a parameterized anonymous block statement for an anonymous block object, and invalid for other objects. |
| QUERY_SQL | VARCHAR2(65536) | NO | For an SQL plan, this field is the original SQL statement queried when the plan was loaded for the first time. For an anonymous block, this field is a parameterized anonymous block statement. |
| SPECIAL_PARAMS | VARCHAR2(4096) | NO | The values of parameters that cannot be parameterized. |
| PARAM_INFOS | VARCHAR2(65536) | NO | The parameter information. |
| SYS_VARS | VARCHAR2(4096) | NO | The values of system variables that affect the cached object. |
| PLAN_HASH | NUMBER(38) | NO | The hash value of the SQL plan. |
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the plan was loaded for the first time. |
| SCHEMA_VERSION | NUMBER(38) | NO | The version of the schema. |
| MERGED_VERSION | NUMBER(38) | NO | The compaction version corresponding to the current cached plan. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time of the last execution. |
| AVG_EXE_USEC | NUMBER(38) | NO | The average execution time. |
| SLOWEST_EXE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | NUMBER(38) | NO | The time consumed by the slowest execution. |
| SLOW_COUNT | NUMBER(38) | NO | The number of times that the current SQL plan was identified as a slow query. |
| HIT_COUNT | NUMBER(38) | NO | The number of hits. |
| PLAN_SIZE | NUMBER(38) | NO | The size of memory occupied by cached objects. |
| EXECUTIONS | NUMBER(38) | NO | The number of times the plan has been executed. |
| DISK_READS | NUMBER(38) | NO | The total number of physical reads of all executions. |
| DIRECT_WRITES | NUMBER(38) | NO | The total number of disk writes of all executions. |
| BUFFERS_GETS | NUMBER(38) | NO | The total number of logical reads of all executions. |
| APPLICATION_WAIT_TIME | NUMBER(38) | NO | The total amount of time spent on waiting for events of the application class in all executions. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | The total amount of time spent on waiting for events of the concurrency class in all executions. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total amount of time spent on waiting for events of the user_io class in all executions. |
| ROWS_PROCESSED | NUMBER(38) | NO | The total number of rows in the results selected in all executions or the number of rows modified by executing the ALTER TABLE statement. |
| ELAPSED_TIME | NUMBER(38) | NO | The total time consumed by all executions, where the time consumed by each execution is defined as the time elapsed from when the execution request was received to when the execution was completed. |
| CPU_TIME | NUMBER(38) | NO | The total amount of CPU time used by all executions. |
| LARGE_QUERYS | NUMBER(38) | NO | The number of times the cached object was considered a large query. |
| DELAYED_LARGE_QUERYS | NUMBER(38) | NO | The number of times the cached object was considered a large query and dropped to the large query queue. |
| DELAYED_PX_QUERYS | NUMBER(38) | NO | For a parallel query, this field indicates the number of times a subquery was dropped back into the queue for retry. |
| OUTLINE_VERSION | NUMBER(38) | NO | The version of the outline. |
| OUTLINE_ID | NUMBER(38) | NO | The ID of the outline. The value "-1" indicates that the plan is not generated based on a bound outline. |
| OUTLINE_DATA | VARCHAR2(65536) | NO | The information about the outline corresponding to the plan. |
| HINTS_INFO | VARCHAR2(65536) | NO | The hints of the SQL plan. |
| HINTS_ALL_WORKED | NUMBER(38) | NO | Indicates whether all the hints in the SQL plan have taken effect. |
| ACS_SEL_INFO | VARCHAR2(65536) | NO | The range of selectivity for the current ACS plan. |
| TABLE_SCAN | NUMBER(38) | NO | Indicates whether the query is a primary key scan. |
| EVOLUTION | NUMBER(38) | NO | Indicates whether the execution plan is evolving. |
| EVO_EXECUTIONS | NUMBER(38) | NO | The number of evolutions. |
| EVO_CPU_TIME | NUMBER(38) | NO | The total amount of CPU time for executions during the evolution. |
| TIMEOUT_COUNT | NUMBER(38) | NO | The number of timeouts. |
| PS_STMT_ID | NUMBER(38) | NO | The ID of the PREPARE statement. |
| SESSID | NUMBER(38) | NO | The ID of the session containing the cached object. |
| TEMP_TABLES | VARCHAR2(65536) | NO | The name of the temporary table in the SQL plan. This field is left empty if no temporary table exists. |
| IS_USE_JIT | NUMBER(38) | NO | Indicates whether just-in-time (JIT) compilation is enabled for the SQL plan. |
| OBJECT_TYPE | VARCHAR2(65536) | NO | The type of the cached object. Valid values: |
| PL_SCHEMA_ID | NUMBER(38) | NO | This field is the schema ID of the cached object for PL objects of non-anonymous blocks, the ID of the PREPARE statement for anonymous blocks, and meaningless for SQL plans. |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Indicates whether the plan has been optimized for batch execution of multiple statements. |