Note
This view was renamed from GV$PLAN_CACHE_PLAN_STAT to GV$OB_PLAN_CACHE_PLAN_STAT starting with V4.0.0.
Purpose
The GV$OB_PLAN_CACHE_PLAN_STAT view displays the status of each cached object in the plan cache of the current tenant on 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 only valid for specific object types.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| 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 the cached object is a PL object, this field is NULL. |
| TYPE | bigint(20) | NO | For SQL plans, this field indicates the plan type:
|
| IS_BIND_SENSITIVE | bigint(20) | NO | Indicates whether the plan requires the ACS to be enabled. |
| IS_BIND_AWARE | bigint(20) | NO | Indicates whether the ACS is enabled for the plan. |
| DB_ID | bigint(20) unsigned | NO | The database ID. |
| STATEMENT | longtext | NO | For SQL plans, this field contains the parameterized SQL statement. For anonymous blocks, this field contains the parameterized anonymous block statement. This field is invalid for other objects. |
| QUERY_SQL | longtext | NO | For SQL plans, this 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 | The values of parameters that cannot be parameterized. |
| PARAM_INFOS | longtext | NO | The parameterization information. |
| SYS_VARS | varchar(4096) | NO | The values of system variables that affect the plan. |
| CONFIGS | varchar(4096) | NO | The information about the configuration items 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 first loaded. |
| SCHEMA_VERSION | bigint(20) | NO | The schema version. |
| LAST_ACTIVE_TIME | timestamp(6) | NO | The time when the plan was last executed. |
| AVG_EXE_USEC | bigint(20) | NO | The average execution time, in microseconds. |
| SLOWEST_EXE_TIME | timestamp(6) | NO | The timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | bigint(20) | NO | The execution time of the slowest execution. |
| SLOW_COUNT | bigint(20) | NO | The number of times the current SQL plan has become a slow query. |
| HIT_COUNT | bigint(20) | NO | The number of times the plan has been hit. |
| PLAN_SIZE | bigint(20) | NO | The size of memory occupied by the cached object. |
| EXECUTIONS | bigint(20) | NO | The number of executions. |
| DISK_READS | bigint(20) | NO | The number of physical reads for all executions. |
| DIRECT_WRITES | bigint(20) | NO | The number of physical writes for all executions. |
| BUFFER_GETS | bigint(20) | NO | The number of logical reads for all executions. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | The total time of all Application events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | The total time of all Concurrency events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | The total time of all user_io events, in microseconds. |
| ROWS_PROCESSED | bigint(20) | NO | The number of rows processed by all executions. |
| ELAPSED_TIME | bigint(20) unsigned | NO | The time consumed from when the request was received to when the execution ended, in microseconds. |
| CPU_TIME | bigint(20) unsigned | NO | The total CPU time consumed by all executions, in microseconds. |
| LARGE_QUERYS | bigint(20) | NO | The number of times the query has been judged to be a large query. |
| DELAYED_LARGE_QUERYS | bigint(20) | NO | The number of times the query has been judged to be a large query and has been added to the large query queue. |
| DELAYED_PX_QUERYS | bigint(20) | NO | The number of times a parallel query has been returned to the queue for retry. |
| OUTLINE_VERSION | bigint(20) | NO | The outline version number. |
| OUTLINE_ID | bigint(20) | NO | The ID of the outline. -1 indicates that the plan is not generated by using an outline. |
| OUTLINE_DATA | longtext | NO | The outline information corresponding to the plan. |
| ACS_SEL_INFO | longtext | NO | The selection rate 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 | The number of evolutions. |
| EVO_CPU_TIME | bigint(20) unsigned | NO | The total CPU time for evolutions, in microseconds. |
| TIMEOUT_COUNT | bigint(20) | NO | The number of timeouts. |
| PS_STMT_ID | bigint(20) | NO | The Prepare ID corresponding to the request. The value can be one of the following:
|
| SESSID | bigint(20) unsigned | NO | The session ID of the cache object. For V4.3.x:
|
| TEMP_TABLES | longtext | NO | The names of the temporary tables in the SQL plan. If no temporary tables are used, this field is empty. |
| IS_USE_JIT | tinyint(4) | NO | Indicates whether expression compilation and execution are enabled for the SQL plan. |
| OBJECT_TYPE | longtext | NO | The type of the cache object:
|
| HINTS_INFO | longtext | NO | The hints for the SQL plan. |
| HINTS_ALL_WORKED | tinyint(4) | NO | Indicates whether all hints in the SQL plan are effective. |
| PL_SCHEMA_ID | bigint(20) unsigned | NO | If the PL object is not an anonymous block, this field indicates the schema ID of the cache object. If the PL object is an anonymous block, the value is the same as that of the ps_stmt_id field. This field is not used for SQL plans. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether the plan is optimized for Batched Multistmt. |
| RULE_NAME | varchar(256) | NO | The name of the rule. |
| PLAN_STATUS | varchar(8) | NO | Indicates whether the plan is in the ACTIVE or INACTIVE state. |
| ADAPTIVE_FEEDBACK_TIMES | bigint(20) | NO | The number of consecutive positive or negative feedback. |
| FIRST_GET_PLAN_TIME | bigint(20) | NO | The time when the hard parse was performed for the query. |
| FIRST_EXE_USEC | bigint(20) | NO | The time when the query was first executed. |
| FORMAT_SQL_ID | varchar(32) | NO | The Format SQL ID of the current plan.
NoteThis field is available starting with V4.4.0. |
| CACHE_NODE_ID | bigint(20) | NO | The ID of the cache node where the object is located.
NoteThis field is available starting with V4.6.0. |
| PCV_ID | bigint(20) | NO | The ID of the plan cache value node where the object is located.
NoteThis field is available starting with V4.6.0. |
| PLAN_SET_ID | bigint(20) | NO | The ID of the plan set node where the object is located.
NoteThis field is available starting with V4.6.0. |
| CREATE_REASON | varchar(4096) | NO | The reason why the plan was added.
NoteThis field is available starting with V4.6.0. |
Sample query
Query the status of each cached object in the plan cache of all OBServer nodes for 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