A plan cache view contains the status of each plan cache and execution statistics and other information about the plans in the plan cache.
(G)V$OB_PLAN_CACHE_STAT
The (G)V$OB_PLAN_CACHE_STAT view records the overall status of plan caches of the current tenant on the current or all OBServer nodes. The following table describes the fields in this view.
| Field | Type (MySQL mode) | Type (Oracle mode) | Description |
|---|---|---|---|
| TENANT_ID | BIGNIT(20) | Not supported | The ID of the tenant. |
| SVR_IP | VARCHAR2(32) | VARCHAR2(32) | The IP address of the server. |
| SVR_PORT | BIGNIT(38) | NUMBER(38) | The port number of the server. |
| SQL_NUM | BIGNIT(38) | NUMBER(38) | The number of SQL statements in the plan cache. |
| MEM_USED | BIGNIT(38) | NUMBER(38) | The size of memory used by the plan cache. |
| MEM_HOLD | BIGNIT(38) | NUMBER(38) | The size of memory that the plan cache holds. |
| ACCESS_COUNT | BIGNIT(38) | NUMBER(38) | The number of accesses to the plan cache. |
| HIT_COUNT | BIGNIT(38) | NUMBER(38) | The number of plan cache hits. |
| HIT_RATE | BIGNIT(38) | NUMBER(38) | The hit rate of the plan cache. |
| PLAN_NUM | BIGNIT(38) | NUMBER(38) | The number of plans. |
| MEM_LIMIT | BIGNIT(38) | NUMBER(38) | The maximum memory allowed for the plan cache. |
| HASH_BUCKET | BIGNIT(38) | NUMBER(38) | The number of buckets in the hash map of the plan cache. |
| STMTKEY_NUM | BIGNIT(38) | NUMBER(38) | The number of stmt_key items in the plan cache. |
(G)V$OB_PLAN_CACHE_PLAN_STAT
The (G)V$OB_PLAN_CACHE_PLAN_STAT view records the status of each object cached in plan caches of the current tenant on the current or all OBServer nodes. The following table describes the fields in this view.
| Field | Type (MySQL mode) | Type (Oracle mode) | Description |
|---|---|---|---|
| TENANT_ID | BIGNIT(20) | NUMBER(38) | The ID of the tenant. |
| SVR_IP | VARCHAR2(46) | VARCHAR2(46) | The IP address of the server. |
| SVR_PORT | BIGNIT(20) | NUMBER(38) | The port number of the server. |
| PLAN_ID | BIGNIT(20) | NUMBER(38) | The ID of the execution plan. |
| SQL_ID | VARCHAR2(32) | VARCHAR2(32) | The ID of the SQL statement. |
| TYPE | BIGNIT(20) | NUMBER(38) | For SQL plan caching, this field indicates the type of the plan. Valid values:
|
| DB_ID | BIGNIT(20) UNSIGNED | NUMBER(38) | The ID of the database. |
| IS_BIND_SENSITIVE | BIGNIT(20) | NUMBER(38) | Indicates whether Adaptive Cursor Sharing (ACS) needs to be enabled for the plan. |
| IS_BIND_AWARE | BIGNIT(20) | NUMBER(38) | Indicates that ACS is enabled for the plan. |
| STATEMENT | LONGTEXT | CLOB | The parameterized SQL statement. |
| QUERY_SQL | LONGTEXT | CLOB | The original SQL statement used to query data when the plan is loaded for the first time. |
| SPECIAL_PARAMS | VARCHAR2(4096) | VARCHAR2(4096) | The values of parameters that cannot be parameterized. |
| PARAM_INFOS | LONGTEXT | CLOB | The parameter information. |
| SYS_VARS | VARCHAR2(4096) | VARCHAR2(4096) | The values of system variables that affect the cached object. |
| CONFIGS | VARCHAR(4096) | Not supported | The parameters that affect the execution plan. |
| PLAN_HASH | BIGNIT(20) UNSIGNED | NUMBER(38) | The hash value of the SQL execution plan. |
| FIRST_LOAD_TIME | TIMESTAMP(6) | TIMESTAMP(6) WITH LOCAL TIME ZONE | The time when the plan was loaded for the first time. |
| SCHEMA_VERSION | BIGNIT(20) | NUMBER(38) | The version of the schema. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) | TIMESTAMP(6) WITH LOCAL TIME ZONE | The time of the last execution. |
| AVG_EXE_USEC | BIGNIT(20) | NUMBER(38) | The average execution duration. |
| SLOWEST_EXE_TIME | TIMESTAMP(6) | TIMESTAMP(6) WITH LOCAL TIME ZONE | The timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | BIGNIT(20) | NUMBER(38) | The time consumed by the slowest execution. |
| SLOW_COUNT | BIGNIT(20) | NUMBER(38) | The number of times that the current SQL plan was identified as a slow query. |
| HIT_COUNT | BIGNIT(20) | NUMBER(38) | The number of hits. |
| PLAN_SIZE | BIGNIT(20) | NUMBER(38) | The size of memory occupied by cached objects. |
| EXECUTIONS | BIGNIT(20) | NUMBER(38) | The number of executions. |
| DISK_READS | BIGNIT(20) | NUMBER(38) | The total number of physical reads of all executions. |
| DIRECT_WRITES | BIGNIT(20) | NUMBER(38) | The total number of disk writes of all executions. |
| BUFFERS_GETS | BIGNIT(20) | NUMBER(38) | The total number of logical reads of all executions. |
| APPLICATION_WAIT_TIME | BIGNIT(20) UNSIGNED | NUMBER(38) | The total wait time of Application wait events during the request execution process. |
| CONCURRENCY_WAIT_TIME | BIGNIT(20) UNSIGNED | NUMBER(38) | The total wait time of Concurrency wait events during the request execution process. |
| USER_IO_WAIT_TIME | BIGNIT(20) UNSIGNED | NUMBER(38) | The total wait time of User I/O wait events during the request execution process. |
| ROWS_PROCESSED | BIGNIT(20) | 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 | BIGNIT(20) UNSIGNED | NUMBER(38) | The total time consumed by all executions, that is, the time elapsed from when the execution requests were received to when the execution of all the requests was completed. |
| CPU_TIME | BIGNIT(20) UNSIGNED | NUMBER(38) | The total amount of CPU time used by all executions. |
| LARGE_QUERYS | BIGNIT(20) | NUMBER(38) | The number of times the cached object was considered a large query. |
| DELAYED_LARGE_QUERYS | BIGNIT(20) | NUMBER(38) | The number of times the cached object was considered a large query and dropped to the large query queue. |
| DELAYED_PX_QUERYS | BIGNIT(20) | NUMBER(38) | The number of times a subquery in a parallel query was dropped back into the queue for retry. |
| OUTLINE_VERSION | BIGNIT(20) | NUMBER(38) | The version of the outline. |
| OUTLINE_ID | BIGNIT(20) | 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 | LONGTEXT | CLOB | The information about the outline corresponding to the plan. |
| ACS_SEL_INFO | LONGTEXT | CLOB | The range of selectivity for the current ACS plan. |
| TABLE_SCAN | TINYINT(4) | NUMBER(38) | Indicates whether the query is a primary key scan. |
| EVOLUTION | TINYINT(4) | NUMBER(38) | Indicates whether the execution plan is evolving. |
| EVO_EXECUTIONS | BIGNIT(20) UNSIGNED | NUMBER(38) | The number of evolutions. |
| EVO_CPU_TIME | BIGNIT(20) | NUMBER(38) | The total amount of CPU time for executions during the evolution. |
| TIMEOUT_COUNT | BIGNIT(20) | NUMBER(38) | The number of timeouts. |
| PS_STMT_ID | BIGNIT(20) | NUMBER(38) | The ID of the prepared statement. |
| SESSID | BIGNIT(20) UNSIGNED | NUMBER(38) | The ID of the session containing the cached object. |
| TEMP_TABLES | LONGTEXT | CLOB | The name of the temporary table in the SQL plan. This field is left empty if no temporary table exists. |
| IS_USE_JIT | TINYINT(4) | NUMBER(38) | Indicates whether just-in-time (JIT) compilation is enabled for the SQL plan. |
| OBJECT_TYPE | LONGTEXT | CLOB | The type of the cached object. Valid values:
|
| HINTS_INFO | LONGTEXT | CLOB | The hints of the SQL plan. |
| HINTS_ALL_WORKED | TINYINT(4) | NUMBER(38) | Indicates whether all the hints in the SQL plan have taken effect. |
| PL_SCHEMA_ID | BIGNIT(20) UNSIGNED | NUMBER(38) | For PL objects that are not anonymous blocks, this field indicates the schema ID of the cached object; for anonymous blocks, this field is equivalent to PS_STMT_ID; for SQL plans, this field is meaningless. |
| IS_BATCHED_MULTI_STMT | TINYINT(4) | NUMBER(38) | Indicates whether the plan has been optimized for batch execution of multiple statements. |
| RULE_NAME | VARCHAR(256) | Not supported | Indicates whether a user-defined rewrite rule is hit. |
(G)V$OB_PLAN_CACHE_PLAN_EXPLAIN
The (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN view records the physical execution plans cached in plan caches on the current or all OBServer nodes. The following table describes the fields in this view.
| Field | Type (MySQL mode) | Type (Oracle mode) | Description |
|---|---|---|---|
| TENANT_ID | BIGNIT(20) | NUMBER(38) | The ID of the tenant. |
| SVR_IP | VARCHAR2(46) | VARCHAR2(46) | The IP address of the server. |
| SVR_PORT | BIGNIT(20) | NUMBER(38) | The port number of the server. |
| PLAN_ID | BIGNIT(20) | NUMBER(38) | The ID of the execution plan. |
| PLAN_DEPTH | BIGNIT(20) | NUMBER(38) | The display depth of the operator. |
| PLAN_LINE_ID | BIGNIT(20) | NUMBER(38) | The ID of the operator. |
| OPERATOR | VARCHAR2(128) | VARCHAR2(128) | The name of the operator. |
| NAME | VARCHAR2(256) | VARCHAR2(256) | The name of the table. |
| ROWS | BIGNIT(20) | NUMBER(38) | The estimated number of rows in the result. |
| COST | BIGNIT(20) | NUMBER(38) | The estimated cost. |
| PROPERTY | VARCHAR2(4096) | VARCHAR2(4096) | The information about the corresponding operator. |
