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
This view displays the status of each cached object in the plan cache of all OBServer nodes for the current tenant.
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 of the cached object. If the cached object is a PL object, this field 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 requires the ACS feature to be enabled. |
| IS_BIND_AWARE | bigint(20) | NO | Indicates whether the ACS feature 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. For other objects, this field is invalid. |
| 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 duration of the slowest execution. |
| SLOW_COUNT | bigint(20) | NO | The number of times the current SQL plan becomes a slow query. |
| HIT_COUNT | bigint(20) | NO | The number of times the plan is 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 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 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 queued in 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 number. |
| OUTLINE_ID | bigint(20) | NO | The ID of the outline. -1 indicates that the plan is not generated by using a bound 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 consumed during the evolution, in microseconds. |
| TIMEOUT_COUNT | bigint(20) | NO | The number of times the statement timed out. |
| PS_STMT_ID | bigint(20) | NO | The Prepare ID corresponding to the request: -1: indicates that the SQL statement does not use the PS protocol.-1: indicates that the SQL statement uses the PS protocol, 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.2.x:
|
| TEMP_TABLES | longtext | NO | The name of the temporary table in the SQL plan. If no temporary table exists, this field is empty. |
| IS_USE_JIT | tinyint(4) | NO | Indicates whether the SQL plan uses expression compilation. |
| 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 irrelevant. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether the plan is optimized for Batched Multistmt. |
| RULE_NAME | VARCHAR2(256) | NO | The name of the rule. |
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: xx.xx.xx.xx
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,17180067074,1,0,0,1,3,0,10,1001,101,0,1,
PLAN_HASH: 12254811659671915614
FIRST_LOAD_TIME: 2025-04-15 14:49:43.785401
SCHEMA_VERSION: 1
LAST_ACTIVE_TIME: 2025-04-18 16:01:13.101607
AVG_EXE_USEC: 435
SLOWEST_EXE_TIME: 2025-04-16 12:42:34.552533
SLOWEST_EXE_USEC: 4822
SLOW_COUNT: 0
HIT_COUNT: 87643
PLAN_SIZE: 57592
EXECUTIONS: 87643
DISK_READS: 0
DIRECT_WRITES: 0
BUFFER_GETS: 520
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 87643
ELAPSED_TIME: 38197334
CPU_TIME: 8935320
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