Note
The view name was changed from GV$PLAN_CACHE_PLAN_STAT to GV$OB_PLAN_CACHE_PLAN_STAT in V4.0.0.
Purpose
The GV$OB_PLAN_CACHE_PLAN_STAT view displays the status of each cached object in the plan cache of all OBServer nodes for all tenants.
Notice
The view caches both SQL plan objects and PL objects (such as anonymous blocks, PL packages, and PL functions). Some fields are valid only for specific objects.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID. |
| SVR_IP | varchar(46) | NO | IP address of the server. |
| SVR_PORT | bigint(20) | NO | Port number of the server. |
| PLAN_ID | bigint(20) | NO | ID of the cached object. |
| SQL_ID | varchar(32) | NO | ID of the corresponding SQL statement. If the object is a PL object, this field is NULL. |
| TYPE | bigint(20) | NO | For cached SQL plans, this field indicates the type of the plan:
|
| IS_BIND_SENSITIVE | bigint(20) | NO | Indicates whether the plan needs to be enabled for ACS. |
| IS_BIND_AWARE | bigint(20) | NO | Indicates whether the plan is enabled for ACS. |
| DB_ID | bigint(20) unsigned | NO | ID of the database. |
| STATEMENT | longtext | NO | For SQL plans, this field indicates the parameterized SQL statement; for anonymous block objects, this field indicates the parameterized anonymous block statement; for other objects, this field is invalid. |
| QUERY_SQL | longtext | NO | For SQL plans, this field indicates the original SQL statement when the plan is loaded for the first time; for anonymous block objects, this field indicates the parameterized anonymous block statement. |
| SPECIAL_PARAMS | varchar(4096) | NO | Values of the parameters that cannot be parameterized. |
| PARAM_INFOS | longtext | NO | Parameterization information. |
| SYS_VARS | varchar(4096) | NO | Values of the system variables that affect the plan. |
| CONFIGS | varchar(4096) | NO | Configuration items that affect the execution plan. |
| PLAN_HASH | bigint(20) unsigned | NO | Hash value of the SQL plan. |
| FIRST_LOAD_TIME | timestamp(6) | NO | Time when the plan was loaded for the first time. |
| SCHEMA_VERSION | bigint(20) | NO | Schema version number. |
| LAST_ACTIVE_TIME | timestamp(6) | NO | Time when the plan was last executed. |
| AVG_EXE_USEC | bigint(20) | NO | Average execution time. |
| SLOWEST_EXE_TIME | timestamp(6) | NO | Timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | bigint(20) | NO | Duration of the slowest execution. |
| SLOW_COUNT | bigint(20) | NO | Number of times the SQL plan has been identified as a slow query. |
| HIT_COUNT | bigint(20) | NO | Number of hits. |
| PLAN_SIZE | bigint(20) | NO | Size of the memory occupied by the cached object. |
| EXECUTIONS | bigint(20) | NO | Number of executions. |
| DISK_READS | bigint(20) | NO | Number of physical reads. |
| DIRECT_WRITES | bigint(20) | NO | Number of physical writes. |
| BUFFER_GETS | bigint(20) | NO | Number of logical reads. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | Total time of application events. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | Total time of concurrency events. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | Total time of user_io events. |
| ROWS_PROCESSED | bigint(20) | NO | Total time of schedule events. |
| ELAPSED_TIME | bigint(20) unsigned | NO | Time from receiving the request to the end of execution. |
| CPU_TIME | bigint(20) unsigned | NO | Total CPU time consumed by the execution. |
| LARGE_QUERYS | bigint(20) | NO | Number of large queries. |
| DELAYED_LARGE_QUERYS | bigint(20) | NO | Number of large queries that are queued. |
| DELAYED_PX_QUERYS | bigint(20) | NO | Number of parallel queries that are retried in the queue. |
| OUTLINE_VERSION | bigint(20) | NO | Outline version number. |
| OUTLINE_ID | bigint(20) | NO | ID of the outline. If the plan is not generated by binding an outline, the value of this field is -1. |
| OUTLINE_DATA | longtext | NO | Outline information corresponding to the plan. |
| ACS_SEL_INFO | longtext | NO | 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 | Number of evolutions. |
| EVO_CPU_TIME | bigint(20) unsigned | NO | Total CPU time consumed by the evolutions. |
| TIMEOUT_COUNT | bigint(20) | NO | Number of timeouts. |
| PS_STMT_ID | bigint(20) | NO | Prepare ID corresponding to the request: -1: the SQL statement does not use the PS protocol.-1: the SQL statement uses the PS protocol, and this value is the unique identifier returned by the PS protocol for the statement. |
| SESSID | bigint(20) unsigned | NO | The session ID where the cached object resides. For V4.3.x:
|
| TEMP_TABLES | longtext | NO | Names of temporary tables in the SQL plan. If no temporary table is included, 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 | Type of the cached object: |
| HINTS_INFO | longtext | NO | 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 PL objects, this field indicates the schema ID of the cached object; for anonymous PL objects, this field is the same as the ps_stmt_id field; for SQL plans, this field is meaningless. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether the plan is an optimized plan for Batched Multistmt. |
| RULE_NAME | VARCHAR2(256) | NO | Rule name |
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