A plan cache view contains the status of each plan cache and execution statistics and other information about the plans in the plan cache.
GV$PLAN_CACHE_STAT
The GV$PLAN_CACHE_STAT view records the status of each plan cache, and each plan cache has a record entry in this view.
| Field | Type | Description |
|---|---|---|
| SVR_IP | VARCHAR2(32) | The IP address of the OBServer node. |
| SVR_PORT | NUMBER(38) | The port number. |
| SQL_NUM | NUMBER(38) | The number of SQL statements in the plan cache. |
| MEM_USED | NUMBER(38) | The size of memory used by the plan cache. |
| MEM_HOLD | NUMBER(38) | The size of memory that the plan cache holds. |
| ACCESS_COUNT | NUMBER(38) | The number of accesses to the plan cache. |
| HIT_COUNT | NUMBER(38) | The number of plan cache hits. |
| HIT_RATE | NUMBER(38) | The hit rate of the plan cache. |
| PLAN_NUM | NUMBER(38) | The number of plans. |
| MEM_LIMIT | NUMBER(38) | The maximum memory allowed for the plan cache. |
| HASH_BUCKET | NUMBER(38) | The number of buckets in the hash map of the plan cache. |
| STMTKEY_NUM | NUMBER(38) | The number of stmt_key items in the plan cache. |
GV$PLAN_CACHE_PLAN_STAT
GV$PLAN_CACHE_PLAN_STAT records the specific information of all execution plans in a plan cache and the general execution statistics of each plan. Each execution plan has a record entry in the view.
| Field | Type | Description |
|---|---|---|
| TENANT_ID | NUMBER(38) | The ID of the tenant. |
| SVR_IP | VARCHAR2(32) | The IP address of the OBServer node. |
| SVR_PORT | NUMBER(38) | The port number of the server. |
| PLAN_ID | NUMBER(38) | The ID of the execution plan. |
| SQL_ID | VARCHAR2(32) | The ID of the SQL query. |
| TYPE | NUMBER(38) | For SQL plan caching, this field indicates the type of the plan. Valid values:
|
| DB_ID | NUMBER(38) | The ID of the database. |
| IS_BIND_SENSITIVE | NUMBER(38) | Indicates whether Adaptive Cursor Sharing (ACS) needs to be enabled for the plan. |
| IS_BIND_AWARE | NUMBER(38) | Indicates that ACS is enabled for the plan. |
| STATEMENT | VARCHAR2(65536) | The parameterized SQL statement. |
| QUERY_SQL | VARCHAR2(65536) | The original SQL statement used to query data when the plan is loaded for the first time. |
| SPECIAL_PARAMS | VARCHAR2(4096) | The values of parameters that cannot be parameterized. |
| PARAM_INFOS | VARCHAR2(65536) | The parameter information. |
| SYS_VARS | VARCHAR2(4096) | The values of system variables that affect the cached object. |
| PLAN_HASH | NUMBER(38) | The Hash value of the SQL execution plan. |
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | The time when the plan was loaded for the first time. |
| SCHEMA_VERSION | NUMBER(38) | The version of the schema. |
| MERGED_VERSION | NUMBER(38) | The version of the merged plan corresponding to the current cached plan. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | The time of the last execution. |
| AVG_EXE_USEC | NUMBER(38) | The average execution duration. |
| SLOWEST_EXE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | The timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | NUMBER(38) | The time consumed by the slowest execution. |
| SLOW_COUNT | NUMBER(38) | The number of times that the current SQL plan was identified as a slow query. |
| HIT_COUNT | NUMBER(38) | The number of hits. |
| PLAN_SIZE | NUMBER(38) | The size of memory occupied by cached objects. |
| EXECUTIONS | NUMBER(38) | The number of executions. |
| DISK_READS | NUMBER(38) | The total number of physical reads of all executions. |
| DIRECT_WRITES | NUMBER(38) | The total number of disk writes of all executions. |
| BUFFERS_GETS | NUMBER(38) | The total number of logical reads of all executions. |
| APPLICATION_WAIT_TIME | NUMBER(38) | The total amount of time spent on waiting for events of the application class in all executions. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | The total amount of time spent on waiting for events of the concurrency class in all executions. |
| USER_IO_WAIT_TIME | NUMBER(38) | The total amount of time spent on waiting for events of the USER_IO class in all executions. |
| ROWS_PROCESSED | NUMBER(38) | The total number of rows in the results selected for all executions or the number of rows modified by executing the ALTER TABLE statement. |
| ELAPSED_TIME | NUMBER(38) | 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) | The total amount of CPU time used by all executions. |
| LARGE_QUERYS | NUMBER(38) | The number of times the cached object was considered a large query. |
| DELAYED_LARGE_QUERYS | NUMBER(38) | The number of times the cached object was considered a large query and dropped to the large query queue. |
| DELAYED_PX_QUERYS | NUMBER(38) | The number of times a subquery in a parallel query was dropped back into the queue for retry. |
| OUTLINE_VERSION | NUMBER(38) | The version of the outline. |
| OUTLINE_ID | NUMBER(38) | The ID of the outline. The value -1 indicates a plan that is not generated by binding the statement to an outline. |
| OUTLINE_DATA | VARCHAR2(65536) | The information about the outline corresponding to the plan. |
| HINTS_INFO | VARCHAR2(65536) | The hints of the SQL plan. |
| HINTS_ALL_WORKED | NUMBER(38) | Indicates whether all the hints in the SQL plan have taken effect. |
| ACS_SEL_INFO | VARCHAR2(65536) | The range of selectivity for the current ACS plan. |
| TABLE_SCAN | NUMBER(38) | Indicates whether the query is a primary key scan. |
| EVOLUTION | NUMBER(38) | Indicates whether the execution plan is evolving. |
| EVO_EXECUTIONS | NUMBER(38) | The number of evolutions. |
| EVO_CPU_TIME | NUMBER(38) | The total amount of CPU time for executions during the evolution. |
| TIMEOUT_COUNT | NUMBER(38) | The number of timeouts. |
| PS_STMT_ID | NUMBER(38) | The ID of the PREPARE statement. |
| SESSID | NUMBER(38) | The ID of the session containing the cached object. |
| TEMP_TABLES | VARCHAR2(65536) | 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) | Indicates whether just-in-time (JIT) compilation is enabled for the SQL plan. |
| OBJECT_TYPE | VARCHAR2(65536) | The type of the cached object. Valid values:
|
| PL_SCHEMA_ID | NUMBER(38) | Indicates the schema ID of the cached object for PL objects of non-anonymous blocks, is identical to PS_STMT_ID for anonymous blocks, and is meaningless for SQL plans. |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | Indicates whether the plan has been optimized for batch execution of multiple statements. |
GV$PLAN_CACHE_PLAN_EXPLAIN
GV$PLAN_CACHE_PLAN_EXPLAIN records the execution plans of an SQL statement in the plan cache.
Notice
- To query the
GV$PLAN_CACHE_PLAN_EXPLAINtable, you must specify the necessary conditions such asIP,Port,TENANT_ID, andPLAN_ID.- To query the
V$PLAN_CACHE_PLAN_EXPLAINtable, you must specify the necessary conditions such asTENANT_IDandPLAN_ID.
| Field | Type | Description |
|---|---|---|
| TENANT_ID | NUMBER(38) | The ID of the tenant. |
| SVR_IP | VARCHAR2(32) | The IP address of the server. |
| SVR_PORT | NUMBER(38) | The port number of the server. |
| PLAN_ID | NUMBER(38) | The ID of the execution plan. |
| PLAN_DEPTH | NUMBER(38) | The display depth of the operator. |
| PLAN_LINE_ID | VARCHAR2(32) | The ID of the operator. |
| OPERATOR | VARCHAR2(128) | The name of the operator. |
| NAME | VARCHAR2(256) | The name of the table. |
| ROWS | NUMBER(38) | The estimated number of rows in the result. |
| COST | NUMBER(38) | The estimated cost. |
| PROPERTY | VARCHAR2(4096) | The information about the corresponding operator. |