Note
This view is available starting with V4.0.0. The view name was changed from GV$PLAN_CACHE_PLAN_STAT to GV$OB_PLAN_CACHE_PLAN_STAT.
Purpose
The GV$OB_PLAN_CACHE_PLAN_STAT view displays the status of each cached object in the plan cache of all tenants 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 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 of the cached object. If the cached object is a PL object, this column is NULL. |
| TYPE | bigint(20) | NO | The type of the cached object. For SQL plans, the value indicates the type of the plan:
|
| IS_BIND_SENSITIVE | bigint(20) | NO | Indicates whether the plan needs to be bound to the Adaptive Query Compilation (ACS) feature. |
| IS_BIND_AWARE | bigint(20) | NO | Indicates whether the plan is bound to the Adaptive Query Compilation (ACS) feature. |
| DB_ID | bigint(20) unsigned | NO | The ID of the database. |
| STATEMENT | longtext | NO | For SQL plans, this column contains the parameterized SQL statement. For anonymous blocks, this column contains the parameterized anonymous block statement. This column is invalid for other objects. |
| QUERY_SQL | longtext | NO | For SQL plans, this column contains the original SQL statement queried when the plan was first loaded. For anonymous blocks, this column 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 last execution time. |
| 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 time taken for the slowest execution. |
| SLOW_COUNT | bigint(20) | NO | The number of times the current SQL plan has been identified as a slow query. |
| HIT_COUNT | bigint(20) | NO | The number of times the plan has been hit. |
| PLAN_SIZE | bigint(20) | NO | The memory size occupied by the cached object. |
| EXECUTIONS | bigint(20) | NO | The number of executions. |
| DISK_READS | bigint(20) | NO | The number of physical reads across all executions. |
| DIRECT_WRITES | bigint(20) | NO | The number of physical writes across all executions. |
| BUFFER_GETS | bigint(20) | NO | The number of logical reads across all executions. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | The total time for all Application events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | The total time for all Concurrency events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | The total time for all user_io events, in microseconds. |
| ROWS_PROCESSED | bigint(20) | NO | The number of rows selected or modified by all executions. |
| ELAPSED_TIME | bigint(20) unsigned | NO | The time taken from receiving the request to the end of execution, 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 identified as a large query. |
| DELAYED_LARGE_QUERYS | bigint(20) | NO | The number of times the query has been identified as a large query and added to the large query queue. |
| DELAYED_PX_QUERYS | bigint(20) | NO | The number of times parallel queries have been returned to the queue for retry. |
| OUTLINE_VERSION | bigint(20) | NO | The outline version. |
| OUTLINE_ID | bigint(20) | NO | The ID of the outline. -1 indicates that the plan was 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 execution CPU time in microseconds during evolution. |
| TIMEOUT_COUNT | bigint(20) | NO | The number of timeouts. |
| PS_STMT_ID | bigint(20) | NO | The Prepare ID corresponding to the request:
|
| SESSID | bigint(20) unsigned | NO | The Session ID of the cached object. For V4.3.x:
|
| TEMP_TABLES | longtext | NO | The names of temporary tables in the SQL plan. If no temporary tables exist, this field is empty. |
| IS_USE_JIT | tinyint(4) | NO | Indicates whether the SQL plan has enabled expression compilation execution. |
| OBJECT_TYPE | longtext | NO | The type of the cached object:
|
| HINTS_INFO | longtext | NO | The hint information of the SQL plan. |
| HINTS_ALL_WORKED | tinyint(4) | NO | Indicates 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 | The name of the rule. |
| PLAN_STATUS | varchar(8) | NO | Indicates whether the plan is ACTIVE or INACTIVE. |
| ADAPTIVE_FEEDBACK_TIMES | bigint(20) | NO | The number of consecutive positive or negative feedbacks. |
| FIRST_GET_PLAN_TIME | bigint(20) | NO | The time when the query was hard parsed. |
| FIRST_EXE_USEC | bigint(20) | NO | The time when the query was executed for the first time. |
| FORMAT_SQL_ID | varchar(32) | NO | 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.
NoteThis field is available starting with V4.4.2 BP2. |
| PCV_ID | bigint(20) | NO | The ID of the plan cache value node to which the object belongs.
NoteThis field is available starting with V4.4.2 BP2. |
| PLAN_SET_ID | bigint(20) | NO | The ID of the plan set node to which the object belongs.
NoteThis field is available starting with V4.4.2 BP2. |
| CREATE_reason | varchar(4096) | NO | The reason why the current plan was added.
NoteThis field is available starting with V4.4.2 BP2. |
Sample query
Query the status of each cached object in the plan cache of all tenants on all OBServer nodes.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1
SVR_IP: xxx.xxx.xxx.xxx
SVR_PORT: 2882
PLAN_ID: 559
SQL_ID: 40E4F66DE936018FB2ED2B62044F854D
TYPE: 1
IS_BIND_SENSITIVE: NULL
IS_BIND_AWARE: NULL
DB_ID: 201001
STATEMENT: SELECT partition_id, object_type, row_cnt as row_count, avg_row_len as avg_row_size, macro_blk_cnt as macro_block_num, micro_blk_cnt as micro_block_num, stattype_locked as stattype_locked,stale_stats as stale_stats,last_analyzed,sstable_row_cnt as sstable_row_cnt,memtable_row_cnt as memtable_row_cnt,spare1 as sample_size FROM __all_table_stat WHERE TENANT_ID = ? AND TABLE_ID=? AND partition_id in (?)
QUERY_SQL: SELECT partition_id, object_type, row_cnt as row_count, avg_row_len as avg_row_size, macro_blk_cnt as macro_block_num, micro_blk_cnt as micro_block_num, stattype_locked as stattype_locked,stale_stats as stale_stats,last_analyzed,sstable_row_cnt as sstable_row_cnt,memtable_row_cnt as memtable_row_cnt,spare1 as sample_size FROM __all_table_stat WHERE TENANT_ID = 0 AND TABLE_ID=385 AND partition_id in (385)
SPECIAL_PARAMS:
PARAM_INFOS: {1,0,0,0,5},{1,0,0,0,5},{1,0,0,0,5}
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,1,0,100,0.005
CONFIGS: 4,1,1,0,1,1,1,0,30,17180131842,1,1,0,1,3,0,1,0,1,0,0,1,1,0,1,15,1,1,1,1,1,0,1,0
PLAN_HASH: 3010089388497790920
FIRST_LOAD_TIME: 2026-06-08 18:02:58.847737
SCHEMA_VERSION: 1780912978818088
LAST_ACTIVE_TIME: 2026-06-17 17:03:31.893689
AVG_EXE_USEC: 100
SLOWEST_EXE_TIME: 2026-06-14 22:53:47.744784
SLOWEST_EXE_USEC: 1994
SLOW_COUNT: 0
HIT_COUNT: 2692
PLAN_SIZE: 45424
EXECUTIONS: 2693
DISK_READS: 35
DIRECT_WRITES: 0
BUFFER_GETS: 3947
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 2607
ELAPSED_TIME: 270107
CPU_TIME: 262362
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_table_stat"@"SEL$1" "primary") OPTIMIZER_FEATURES_ENABLE('4.4.2.2') END_OUTLINE_DATA*/
ACS_SEL_INFO: NULL
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: 1156
FIRST_EXE_USEC: 40
FORMAT_SQL_ID: 0C610691E079FBC3C9EF556C8031789B
CACHE_NODE_ID: 289
PCV_ID: 1
PLAN_SET_ID: 1
CREATE_REASON: Cache key not exists, cache_node_id: -1.
1 row in set