Overview
The GV$OB_PLAN_CACHE_PLAN_STAT view displays the status of each cached object in the plan cache for the current tenant across all OBServer nodes.
Notice
This view 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.
Columns
Field Name |
Type |
Whether can be NULL |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID |
| SVR_IP | varchar(46) | NO | Server IP Address |
| SVR_PORT | bigint(20) | NO | Server Port Number |
| PLAN_ID | bigint(20) | NO | ID of the cached object |
| SQL_ID | varchar(32) | NO | SQL ID corresponding to the cached object. This field is NULL for PL objects. |
| TYPE | bigint(20) | NO | For SQL plan caching, this field indicates the plan type:
|
| IS_BIND_SENSITIVE | bigint(20) | NO | Whether to enable ACS for this plan |
| IS_BIND_AWARE | bigint(20) | NO | ACS has been enabled for this plan. |
| DB_ID | bigint(20) unsigned | NO | Database ID |
| STATEMENT | longtext | NO | For SQL plans, this field contains the parameterized SQL statement. For anonymous block objects, this field contains the parameterized anonymous block statement. This field is invalid for other objects. |
| QUERY_SQL | longtext | NO | For SQL plans, the field contains the original SQL statement queried when the plan was first loaded. For anonymous blocks, this field contains the parameterized anonymous block statement. |
| SPECIAL_PARAMS | varchar(4096) | NO | Represents a parameter value that cannot be parameterized. |
| PARAM_INFOS | longtext | NO | Represents parameterized information. |
| SYS_VARS | varchar(4096) | NO | Values of system variables that affect the plan |
| CONFIGS | varchar(4096) | NO | Represents the parameter information that affects the execution plan. |
| PLAN_HASH | bigint(20) unsigned | NO | Hash value of the SQL plan |
| FIRST_LOAD_TIME | timestamp(6) | NO | First Load Time |
| SCHEMA_VERSION | bigint(20) | NO | Schema version number |
| LAST_ACTIVE_TIME | timestamp(6) | NO | Last Execution Time |
| AVG_EXE_USEC | bigint(20) | NO | Average execution time, in microseconds |
| SLOWEST_EXE_TIME | timestamp(6) | NO | Slowest Execution Timestamp |
| SLOWEST_EXE_USEC | bigint(20) | NO | Time Spent on the Slowest Execution |
| SLOW_COUNT | bigint(20) | NO | Number of Times the Current SQL Plan Became a Slow Query |
| HIT_COUNT | bigint(20) | NO | Hit Count |
| PLAN_SIZE | bigint(20) | NO | Memory Size Occupied by Cache Objects |
| EXECUTIONS | bigint(20) | NO | Number of Executions |
| DISK_READS | bigint(20) | NO | Total Number of Physical Reads |
| DIRECT_WRITES | bigint(20) | NO | Total Number of Physical Writes |
| BUFFER_GETS | bigint(20) | NO | Total Number of Logical Reads |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | Total time of all application-type events, in microseconds |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | Total time of all concurrency events, in microseconds |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | Total Time for All user_io Events, in Microseconds |
| ROWS_PROCESSED | bigint(20) | NO | Number of rows selected or changed |
| ELAPSED_TIME | bigint(20) unsigned | NO | The time consumed from request reception to execution completion, in microseconds. |
| CPU_TIME | bigint(20) unsigned | NO | Total CPU time consumed for all executions, in microseconds |
| LARGE_QUERYS | bigint(20) | NO | Number of Times Identified as a Large Query |
| DELAYED_LARGE_QUERYS | bigint(20) | NO | Number of Times Judged as a Large Query and Sent to the Large Query Queue |
| DELAYED_PX_QUERYS | bigint(20) | NO | Number of Times a Parallel Query is Dropped Back into the Queue for Retry |
| OUTLINE_VERSION | bigint(20) | NO | Outline Version |
| OUTLINE_ID | bigint(20) | NO | The ID of the outline. If the value is -1, the plan was not generated by binding an outline. |
| OUTLINE_DATA | longtext | NO | Outline information of the plan |
| ACS_SEL_INFO | longtext | NO | Selectivity Space Corresponding to 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 | Evolution Count |
| EVO_CPU_TIME | bigint(20) unsigned | NO | Total CPU Time in Microseconds During Evolution |
| TIMEOUT_COUNT | bigint(20) | NO | Number of Timeout Attempts |
| PS_STMT_ID | bigint(20) | NO | Prepare ID of the request:
|
| SESSID | bigint(20) unsigned | NO | The session ID of the object cached in the cache.
|
| TEMP_TABLES | longtext | NO | The name of the temporary table contained in the SQL plan. If no temporary tables are used, this field is empty. |
| IS_USE_JIT | tinyint(4) | NO | Whether expression compilation and execution are enabled for the SQL plan |
| OBJECT_TYPE | longtext | NO | Type of the cached object. Valid values:
|
| HINTS_INFO | longtext | NO | Hint information of SQL plan |
| HINTS_ALL_WORKED | tinyint(4) | NO | Whether all hints in the SQL plan have taken effect |
| PL_SCHEMA_ID | bigint(20) unsigned | NO | For PL objects other than anonymous blocks, this field is the schema ID of the cached object. For anonymous blocks, this field is the same as ps_stmt_id. For SQL plans, this field is meaningless. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether the plan is optimized for batched multistatement execution. |
| RULE_NAME | varchar(256) | NO | Represents the rule name. |
| PLAN_STATUS | varchar(8) | NO | Indicates the status of the current plan is ACTIVE Or INACTIVE. |
| ADAPTIVE_FEEDBACK_TIMES | bigint(20) | NO | Represents the number of consecutive positive or negative feedbacks. |
| FIRST_GET_PLAN_TIME | bigint(20) | NO | The time taken for the query to be parsed. |
| FIRST_EXE_USEC | bigint(20) | NO | The time when the query was executed for the first time. |
| FORMAT_SQL_ID | varchar(32) | NO | Indicates the format SQL ID of the current plan. |
| CACHE_NODE_ID | bigint(20) | NO | The ID of the cache node to which the object belongs. |
| PCV_ID | bigint(20) | NO | Indicates the ID of the plan cache value node to which the object belongs. |
| PLAN_SET_ID | bigint(20) | NO | The ID of the plan set node to which the object belongs. |
| CREATE_REASON | varchar(4096) | NO | Indicates the reason for joining the current plan. |
Sample query
Query the status of each cached object stored in the plan cache on all OBServer nodes of the current tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1002
SVR_IP: 172.xx.xxx.xxx
SVR_PORT: 2882
PLAN_ID: 12
SQL_ID: D9EB4937E65F94AA38F6B0FE72B06D13
TYPE: 1
IS_BIND_SENSITIVE: 0
IS_BIND_AWARE: 0
DB_ID: 201001
STATEMENT: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = ? AND COLUMN_NAME = ?
QUERY_SQL: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = '__all_global_stat' AND COLUMN_NAME = 'snapshot_gc_scn'
SPECIAL_PARAMS:
PARAM_INFOS: {1,0,0,-1,22},{1,0,0,-1,22}
SYS_VARS: 45,45,4194304,+08:00,2,4,1,0,0,3,1,0,1,10485760,1,0,YYYY-MM-DD HH24:MI:SS,YYYY-MM-DD HH24:MI:SS.FF,YYYY-MM-DD HH24:MI:SS.FF TZR TZD,BINARY,BINARY,AL32UTF8,AL16UTF16,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1,1,1,0,0,0,1000,BLOOM_FILTER,RANGE,IN,1,17180000512,17180000512,1,0,0,45,0,0,2,
CONFIGS: 4,1,1,0,1,1,1,0,30,17180131328,1,0,0,1,3,0,10,1001,101,0,1,1,
PLAN_HASH: 12254811659671915614
FIRST_LOAD_TIME: 2025-07-08 14:01:09.046056
SCHEMA_VERSION: 1
LAST_ACTIVE_TIME: 2025-07-17 10:40:31.570558
AVG_EXE_USEC: 556
SLOWEST_EXE_TIME: 2025-07-16 02:00:07.244719
SLOWEST_EXE_USEC: 78792
SLOW_COUNT: 0
HIT_COUNT: 254569
PLAN_SIZE: 57600
EXECUTIONS: 254569
DISK_READS: 498
DIRECT_WRITES: 0
BUFFER_GETS: 2246
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 254569
ELAPSED_TIME: 141649747
CPU_TIME: 30458037
LARGE_QUERYS: 0
DELAYED_LARGE_QUERYS: 0
DELAYED_PX_QUERYS: 0
OUTLINE_VERSION: 0
OUTLINE_ID: -1
OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase"."__all_core_table"@"SEL$1" "primary") OPTIMIZER_FEATURES_ENABLE('4.4.0.0') END_OUTLINE_DATA*/
ACS_SEL_INFO:
TABLE_SCAN: 0
EVOLUTION: 0
EVO_EXECUTIONS: 0
EVO_CPU_TIME: 0
TIMEOUT_COUNT: 0
PS_STMT_ID: -1
SESSID: 0
TEMP_TABLES:
IS_USE_JIT: 0
OBJECT_TYPE: SQL_PLAN
HINTS_INFO: /*+ */
HINTS_ALL_WORKED: 1
PL_SCHEMA_ID: 0
IS_BATCHED_MULTI_STMT: 0
RULE_NAME:
PLAN_STATUS: ACTIVE
ADAPTIVE_FEEDBACK_TIMES: 0
FIRST_GET_PLAN_TIME: NULL
FIRST_EXE_USEC: NULL
FORMAT_SQL_ID: CC42DE6C11EC4E8C9077BA12970047CE
CACHE_NODE_ID: 116
PCV_ID: 1
PLAN_SET_ID: 1
CREATE_REASON: Cache key not exists, cache_node_id: -1.
1 row in set