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 | NUMBER(38) | NO | The tenant ID. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the server. |
| SVR_PORT | NUMBER(38) | NO | The port number of the server. |
| PLAN_ID | NUMBER(38) | NO | The ID of the cached object. |
| SQL_ID | VARCHAR2(32) | NO | The SQL ID corresponding to the cached object. If the cached object is a PL object, this column is NULL. |
| TYPE | NUMBER(38) | NO | For SQL plan caching, this column indicates the type of the plan:
|
| IS_BIND_SENSITIVE | NUMBER(38) | NO | Indicates whether the plan requires the ACS to be opened. |
| IS_BIND_AWARE | NUMBER(38) | NO | Indicates whether the ACS is opened. |
| DB_ID | NUMBER(38) | NO | The ID of the database. |
| STATEMENT | CLOB | NO | For SQL plan caching, this column stores the parameterized SQL statement. For anonymous block caching, this column stores the parameterized anonymous block statement. This column is invalid for other types of cached objects. |
| QUERY_SQL | CLOB | NO | For SQL plan caching, this column stores the original SQL statement queried when the plan was first loaded. For anonymous block caching, this column stores the parameterized anonymous block statement. |
| SPECIAL_PARAMS | VARCHAR2(4096) | NO | The values of the non-parameterized parameters. |
| PARAM_INFOS | CLOB | NO | The parameterization information. |
| SYS_VARS | VARCHAR2(4096) | NO | The values of the system variables that affect the cached object. |
| PLAN_HASH | NUMBER(38) | NO | The hash value of the SQL plan. |
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the plan was first loaded. |
| SCHEMA_VERSION | NUMBER(38) | NO | The schema version number. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the plan was last executed. |
| AVG_EXE_USEC | NUMBER(38) | NO | The average execution time, in microseconds. |
| SLOWEST_EXE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | NUMBER(38) | NO | The execution time of the slowest execution. |
| SLOW_COUNT | NUMBER(38) | NO | The number of times the SQL plan became a slow query. |
| HIT_COUNT | NUMBER(38) | NO | The number of times the plan was hit. |
| PLAN_SIZE | NUMBER(38) | NO | The memory size occupied by the cached object. |
| EXECUTIONS | NUMBER(38) | NO | The number of executions. |
| DISK_READS | NUMBER(38) | NO | The total number of physical reads for all executions. |
| DIRECT_WRITES | NUMBER(38) | NO | The total number of writes executed. |
| BUFFERS_GETS | NUMBER(38) | NO | The total number of logical reads executed. |
| APPLICATION_WAIT_TIME | NUMBER(38) | NO | The total time of all application events during the execution of the request, in microseconds. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | The total time of all concurrency events during the execution of the request, in microseconds. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total time of all user_io events during the execution of the request, in microseconds. |
| ROWS_PROCESSED | NUMBER(38) | NO | The total number of rows processed by the query or the number of rows changed in the table. |
| ELAPSED_TIME | NUMBER(38) | NO | The total time consumed from when the request was received to when the execution ended, in microseconds. |
| CPU_TIME | NUMBER(38) | NO | The total CPU time consumed by the execution, in microseconds. |
| LARGE_QUERYS | NUMBER(38) | NO | The number of queries judged to be large queries. |
| DELAYED_LARGE_QUERYS | NUMBER(38) | NO | The number of large queries that are added to the large query queue. |
| DELAYED_PX_QUERYS | NUMBER(38) | NO | The number of parallel queries that are returned to the queue for retry. |
| OUTLINE_VERSION | NUMBER(38) | NO | The outline version number. |
| OUTLINE_ID | NUMBER(38) | NO | The ID of the outline. -1 indicates that the plan is not generated by using an outline. |
| OUTLINE_DATA | CLOB | NO | The outline information corresponding to the plan. |
| HINTS_INFO | CLOB | NO | The hint information of the SQL plan. |
| HINTS_ALL_WORKED | NUMBER(38) | NO | Indicates whether all hints in the SQL plan are effective. |
| ACS_SEL_INFO | CLOB | NO | The selectivity space corresponding to the current ACS plan. |
| TABLE_SCAN | NUMBER(38) | NO | Indicates whether the query is a primary key scan. |
| EVOLUTION | NUMBER(38) | NO | Indicates whether the execution plan is evolving. |
| EVO_EXECUTIONS | NUMBER(38) | NO | The number of evolutions. |
| EVO_CPU_TIME | NUMBER(38) | NO | The total CPU time consumed during the evolution, in microseconds. |
| TIMEOUT_COUNT | NUMBER(38) | NO | The number of timeouts. |
| PS_STMT_ID | NUMBER(38) | NO | The Prepare ID corresponding to the request:
|
| SESSID | NUMBER(38) | NO | The session ID of the cache object. For V4.3.x:
|
| TEMP_TABLES | CLOB | NO | The name of the temporary table in the SQL plan. If no temporary table is present, this field is empty. |
| IS_USE_JIT | NUMBER(38) | NO | Indicates whether the SQL plan has enabled expression compilation execution. |
| OBJECT_TYPE | CLOB | NO | The type of the cached object:
|
| PL_SCHEMA_ID | NUMBER(38) | NO | For non-anonymous PL objects, 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 irrelevant. |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Indicates whether the plan is optimized for Batched Multi Stmt. |
| RULE_NAME | VARCHAR2(256) | NO | The name of the rule. |
| PLAN_STATUS | VARCHAR2(8) | NO | Indicates whether the current plan is ACTIVE or INACTIVE. |
| ADAPTIVE_FEEDBACK_TIMES | NUMBER(38) | NO | The number of consecutive positive or negative feedbacks. |
| FIRST_GET_PLAN_TIME | NUMBER(38) | NO | The time when the query was hard-parsed. |
| FIRST_EXE_USEC | NUMBER(38) | NO | The time when the query was first executed. |
| FORMAT_SQL_ID | VARCHAR2(32) | NO | The format SQL ID of the current plan. |
| CACHE_NODE_ID | NUMBER(38) | NO | The ID of the cache node to which the object belongs.
NoteThis field is available starting with V4.4.2 BP2. |
| PCV_ID | NUMBER(38) | 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 | NUMBER(38) | 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 | VARCHAR2(16384) | NO | The reason for adding the current plan.
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 OBServer nodes in the current tenant.
obclient [SYS]> SELECT * FROM SYS.GV$OB_PLAN_CACHE_PLAN_STAT WHERE ROWNUM =1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
SVR_IP: xxx.xxx.xxx.xxx
SVR_PORT: 2882
PLAN_ID: 9
SQL_ID: D9EB4937E65F94AA38F6B0FE72B06D13
TYPE: 1
IS_BIND_SENSITIVE: NULL
IS_BIND_AWARE: NULL
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: NULL
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,1,0,100,0.005
PLAN_HASH: 12254811659671915614
FIRST_LOAD_TIME: 08-JUN-26 06.03.36.438775 PM
SCHEMA_VERSION: 1
LAST_ACTIVE_TIME: 17-JUN-26 05.08.35.606928 PM
AVG_EXE_USEC: 337
SLOWEST_EXE_TIME: 12-JUN-26 04.26.49.234625 AM
SLOWEST_EXE_USEC: 10197
SLOW_COUNT: 0
HIT_COUNT: 257889
PLAN_SIZE: 29232
EXECUTIONS: 257890
DISK_READS: 198
DIRECT_WRITES: 0
BUFFERS_GETS: 766
APPLICATION_WATI_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 257889
ELAPSED_TIME: 87147186
CPU_TIME: 87115834
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.2.2') END_OUTLINE_DATA*/
HINTS_INFO: /*+ */
HINTS_ALL_WORKED: 1
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: NULL
IS_USE_JIT: 0
OBJECT_TYPE: SQL_PLAN
PL_SCHEMA_ID: 0
IS_BATCHED_MULTI_STMT: 0
RULE_NAME: NULL
PLAN_STATUS: ACTIVE
ADAPTIVE_FEEDBACK_TIMES: 0
FIRST_GET_PLAN_TIME: 1213
FIRST_EXE_USEC: 87
FORMAT_SQL_ID: CC42DE6C11EC4E8C9077BA12970047CE
CACHE_NODE_ID: 1
PCV_ID: 1
PLAN_SET_ID: 1
CREATE_REASON: Cache key not exists, cache_node_id: -1.
1 row in set