Note
The view name is changed from GV$PLAN_CACHE_PLAN_STAT to GV$OB_PLAN_CACHE_PLAN_STAT since OceanBase Database V4.0.0.
Purpose
The GV$OB_PLAN_CACHE_PLAN_STAT view displays the status of each object cached by the current tenant in the plan cache on all OBServer nodes.
Notice
This view caches both SQL plan objects and Procedural Language (PL) objects (such as anonymous blocks, PL packages, and PL functions). Some columns take effect only for specific objects.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| SVR_IP | varchar(46) | NO | The IP address of the server. |
| SVR_PORT | bigint(20) | NO | The port number of the server. |
| PLAN_ID | bigint(20) | NO | The ID of the cached object. |
| SQL_ID | varchar(32) | NO | The SQL ID corresponding to the cached object. If it is a PL object, this column is NULL. |
| TYPE | bigint(20) | NO | For SQL plan caching, this column indicates the type of the plan. Valid values:
|
| IS_BIND_SENSITIVE | bigint(20) | NO | Indicates whether Adaptive Cursor Sharing (ACS) needs to be enabled for the plan. |
| IS_BIND_AWARE | bigint(20) | NO | Indicates that ACS is enabled for the plan. |
| DB_ID | bigint(20) unsigned | NO | The ID of the database. |
| STATEMENT | longtext | NO | If the cached object is an SQL plan, the column value is a parameterized SQL statement. If the cached object is an anonymous block, the column value is a parameterized anonymous block statement. This column is invalid for other types of objects. |
| QUERY_SQL | longtext | NO | If the cached object is an SQL plan, the column value is the original SQL statement queried when the plan was loaded for the first time. If the cached object is an anonymous block, the column value is a parameterized anonymous block statement. |
| SPECIAL_PARAMS | varchar(4096) | NO | The values of parameters that cannot be parameterized. |
| PARAM_INFOS | longtext | NO | The parameter information. |
| SYS_VARS | varchar(4096) | NO | The value of the system variable that affects the plan. |
| CONFIGS | varchar(4096) | NO | The parameters that affect the execution plan. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the SQL plan. |
| FIRST_LOAD_TIME | timestamp(6) | NO | The time when the plan was loaded for the first time. |
| SCHEMA_VERSION | bigint(20) | NO | The version of the schema. |
| MERGED_VERSION | bigint(20) | NO | The merged version number corresponding to the current cached plan. |
| LAST_ACTIVE_TIME | timestamp(6) | NO | The time of the last execution. |
| AVG_EXE_USEC | bigint(20) | NO | The average execution duration. |
| SLOWEST_EXE_TIME | timestamp(6) | NO | The timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | bigint(20) | NO | The time consumed by the slowest execution. |
| SLOW_COUNT | bigint(20) | NO | The number of times that the current SQL plan was identified as a slow query. |
| HIT_COUNT | bigint(20) | NO | The number of hits. |
| PLAN_SIZE | bigint(20) | NO | The size of memory occupied by cached objects. |
| EXECUTIONS | bigint(20) | NO | The number of executions. |
| DISK_READS | bigint(20) | NO | The total number of physical reads of all executions. |
| DIRECT_WRITES | bigint(20) | NO | The total number of physical writes of all executions. |
| BUFFER_GETS | bigint(20) | NO | The total number of logical reads of all executions. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the Application class. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the Concurrency class. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the user_io class. |
| ROWS_PROCESSED | bigint(20) | NO | The total amount of time spent on events of the schedule class. |
| ELAPSED_TIME | bigint(20) unsigned | NO | The amount of time elapsed from when the request was received to when the execution of the request ended. |
| CPU_TIME | bigint(20) unsigned | NO | The total amount of CPU time used by all executions. |
| LARGE_QUERYS | bigint(20) | NO | The number of times the cached object was considered a large query. |
| DELAYED_LARGE_QUERYS | bigint(20) | NO | The number of times the cached object was considered a large query and dropped to the large query queue. |
| DELAYED_PX_QUERYS | bigint(20) | NO | The number of times a subquery in a parallel query was dropped back into the queue for retry. |
| OUTLINE_VERSION | bigint(20) | NO | The version of the outline. |
| OUTLINE_ID | bigint(20) | NO | The ID of the outline. The value "-1" indicates that the plan is not generated based on a bound outline. |
| OUTLINE_DATA | longtext | NO | The information about the outline corresponding to the plan. |
| ACS_SEL_INFO | longtext | NO | The range of selectivity for the current ACS plan. |
| TABLE_SCAN | tinyint(4) | NO | Indicates whether the query is a primary key scan. |
| EVOLUTION | tinyint(4) | NO | Indicates whether the execution plan is evolving. |
| EVO_EXECUTIONS | bigint(20) | NO | The number of evolutions. |
| EVO_CPU_TIME | bigint(20) unsigned | NO | The total amount of CPU time for executions during the evolution. |
| TIMEOUT_COUNT | bigint(20) | NO | The number of timeouts. |
| PS_STMT_ID | bigint(20) | NO | The ID of the prepared statement corresponding to the request. -1: indicates that the prepared statement (PS) protocol is not used in the SQL statement.-1: indicates that the PS protocol is used in the SQL statement. It also indicates the unique identifier returned by the PS protocol for the statement. |
| SESSID | bigint(20) unsigned | NO | The ID of the session containing the cached object. |
| TEMP_TABLES | longtext | NO | The name of the temporary table in the SQL plan. This column is left empty if no temporary table exists. |
| IS_USE_JIT | tinyint(4) | NO | Indicates whether just-in-time (JIT) compilation is enabled for the SQL plan. |
| OBJECT_TYPE | longtext | NO | The type of the cached object. Valid values: |
| HINTS_INFO | longtext | NO | The hints of the SQL plan. |
| HINTS_ALL_WORKED | tinyint(4) | NO | Indicates whether all the hints in the SQL plan have taken effect. |
| PL_SCHEMA_ID | bigint(20) unsigned | NO | This column is the schema ID of the cached object for PL objects other than anonymous blocks. It is identical to PS_STMT_ID for anonymous blocks and meaningless for SQL plans. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether the plan has been optimized for batch execution of multiple statements. |
| RULE_NAME | VARCHAR2(256) | NO | The name of the rule. |