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 valid only 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 corresponding to the cached object. If the cached object is a PL object, this field is NULL. |
| TYPE | bigint(20) | NO | For SQL plan caching, this field indicates the type of the plan:
|
| IS_BIND_SENSITIVE | bigint(20) | NO | Indicates whether the plan requires the Adaptive Query Optimization (AQO) feature to be enabled. |
| IS_BIND_AWARE | bigint(20) | NO | Indicates whether the AQO feature is enabled for the plan. |
| DB_ID | bigint(20) unsigned | NO | The ID of the database. |
| STATEMENT | longtext | NO | For SQL plan caching, this field contains the parameterized SQL statement. For anonymous block caching, this field contains the parameterized anonymous block statement. This field is invalid for other objects. |
| QUERY_SQL | longtext | NO | For SQL plan caching, this field contains the original SQL statement queried when the plan was first loaded. For anonymous block caching, 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 configurations 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 number. |
| 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 longest execution time. |
| SLOWEST_EXE_USEC | bigint(20) | NO | The duration 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 total number of physical reads for all executions. |
| DIRECT_WRITES | bigint(20) | NO | The total number of physical writes for all executions. |
| BUFFER_GETS | bigint(20) | NO | The total 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 time of all Schedule events. |
| ELAPSED_TIME | bigint(20) unsigned | NO | The time consumed 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 is judged to be a large query. |
| DELAYED_LARGE_QUERYS | bigint(20) | NO | The number of times the query is judged to be a large query and is added to the large query queue. |
| DELAYED_PX_QUERYS | bigint(20) | NO | The number of times a parallel query is returned to the queue for retry. |
| OUTLINE_VERSION | bigint(20) | NO | The outline version. |
| OUTLINE_ID | bigint(20) | NO | The outline ID. -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 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 | The number of evolutions. |
| EVO_CPU_TIME | bigint(20) unsigned | NO | The total CPU time consumed during evolution, in microseconds. |
| TIMEOUT_COUNT | bigint(20) | NO | The number of timeouts. |
| PS_STMT_ID | bigint(20) | NO | The Prepare ID corresponding to the request: -1: indicates that the PS protocol is not used for the SQL statement.-1: indicates that the PS protocol is used for the SQL statement, and the value is the unique identifier returned by the PS protocol for the statement. |
| SESSID | bigint(20) unsigned | NO | The Session ID of the cache 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 expression compilation is enabled for the SQL plan. |
| OBJECT_TYPE | longtext | NO | The type of the cache 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 are effective. |
| PL_SCHEMA_ID | bigint(20) unsigned | NO | For non-anonymous blocks, this field indicates the Schema ID of the cache 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 Multistmt. |
| RULE_NAME | varchar(256) | NO | The name of the rule. |
| PLAN_STATUS | varchar(8) | NO | The status of the plan, which 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 hard parse of the query is completed. |
| FIRST_EXE_USEC | bigint(20) | NO | The time when the query is executed for the first time. |
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: xx.xx.xx.xx
SVR_PORT: 2882
PLAN_ID: 1262
SQL_ID: 17605A1DA6B6A2150E9FBCA5D4C7653A
TYPE: 1
IS_BIND_SENSITIVE: 0
IS_BIND_AWARE: 0
DB_ID: 201001
STATEMENT: SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, column_name
QUERY_SQL: SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = '__all_global_stat' ORDER BY row_id, column_name
SPECIAL_PARAMS:
PARAM_INFOS: {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,17180067074,1,0,0,1,3,0,10,1001,101,0,1,
PLAN_HASH: 1225481165967191xxxx
FIRST_LOAD_TIME: 2025-04-15 14:48:44.753417
SCHEMA_VERSION: 1744699724625088
LAST_ACTIVE_TIME: 2025-04-18 15:43:50.879576
AVG_EXE_USEC: 109
SLOWEST_EXE_TIME: 2025-04-15 14:48:59.343768
SLOWEST_EXE_USEC: 3490
SLOW_COUNT: 0
HIT_COUNT: 2678
PLAN_SIZE: 65528
EXECUTIONS: 2679
DISK_READS: 0
DIRECT_WRITES: 0
BUFFER_GETS: 0
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 2679
ELAPSED_TIME: 293534
CPU_TIME: 96391
LARGE_QUERYS: 0
DELAYED_LARGE_QUERYS: 0
DELAYED_PX_QUERYS: 0
OUTLINE_VERSION: 0
OUTLINE_ID: -1
OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "oceanbase"."__all_core_table"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.5.2') 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