Note
This view is available starting with V4.0.0. The view name was changed from V$PLAN_CACHE_PLAN_STAT to V$OB_PLAN_CACHE_PLAN_STAT.
Purpose
The V$OB_PLAN_CACHE_PLAN_STAT view displays the status of each cached object in the plan cache of the current OBServer node 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 | NUMBER(38) | NO | The ID of the tenant. | ||||||||||
| 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 of the cached object. If the cached object is a PL object, this field is NULL. | ||||||||||
| TYPE | NUMBER(38) | NO | The type of the cached object. For SQL plans, the value indicates the type of the plan:
|
||||||||||
| IS_BIND_SENSITIVE | NUMBER(38) | NO | Indicates whether the plan needs to be opened with the ACS. | ||||||||||
| IS_BIND_AWARE | NUMBER(38) | NO | Indicates whether the plan has been opened with the ACS. | ||||||||||
| DB_ID | NUMBER(38) | NO | The ID of the database. | ||||||||||
| STATEMENT | CLOB | NO | For SQL plans, this field contains the parameterized SQL statement. For anonymous blocks, this field contains the parameterized anonymous block statement. This field is invalid for other objects. | ||||||||||
| QUERY_SQL | CLOB | 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 | VARCHAR2(4096) | NO | The values of parameters that cannot be parameterized. | ||||||||||
| PARAM_INFOS | CLOB | NO | The parameterization information. | ||||||||||
| SYS_VARS | VARCHAR2(4096) | NO | The values of 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. | ||||||||||
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The last execution time. | ||||||||||
| 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 number of physical reads for all executions. | ||||||||||
| DIRECT_WRITES | NUMBER(38) | NO | The total number of writes to disk. | ||||||||||
| BUFFERS_GETS | NUMBER(38) | NO | The total number of logical reads. | ||||||||||
| APPLICATION_WAIT_TIME | NUMBER(38) | NO | The total time spent on all Application events, in microseconds. | ||||||||||
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | The total time spent on all Concurrency events, in microseconds. | ||||||||||
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total time spent on all user_io events, in microseconds. | ||||||||||
| ROWS_PROCESSED | NUMBER(38) | NO | The number of rows processed by the query. | ||||||||||
| ELAPSED_TIME | NUMBER(38) | NO | The total time 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 large queries. | ||||||||||
| DELAYED_LARGE_QUERYS | NUMBER(38) | NO | The number of large queries that are delayed and 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 selection rate 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 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: -1: Indicates that the SQL statement does not use the PS protocol.-1: Indicates that the SQL statement uses the PS protocol. This value is the unique identifier returned by the PS protocol for the statement. |
||||||||||
| SESSID | NUMBER(38) | NO | The session ID of the cache object. For V4.3.x:
|
||||||||||
| TEMP_TABLES | CLOB | NO | The names of the temporary tables in the SQL plans. If no temporary tables are found, this column is empty. | ||||||||||
| IS_USE_JIT | NUMBER(38) | NO | Whether the SQL plan enables expression compilation execution | ||||||||||
| OBJECT_TYPE | CLOB | NO | The object type: |
||||||||||
| PL_SCHEMA_ID | NUMBER(38) | NO | The schema ID of a cached object for non-anonymous PL objects. For anonymous blocks, the value is the same as ps_stmt_id. The value is not meaningful for SQL plans. | ||||||||||
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Indicates whether the execution plan is optimized for batched multi-statement queries. | ||||||||||
| Column | Type | Nullable? | Description | -------------------- | ------------------- | ---------------- | ------------------ | RULE_NAME | VARCHAR2(256) | NO | The name of the rule. | ||
| PLAN_STATUS | VARCHAR2(8) | NO | Indicates whether the 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 | Indicates the time when a query is hard-parsed. | ||||||||||
| FIRST_EXE_USEC | NUMBER(38) | NO | This column stores the time of the query's first execution. |
Sample query
The status of each cached object in the plan cache of the current tenant on the current OBServer node.
obclient [SYS]> SELECT * FROM SYS.V$OB_PLAN_CACHE_PLAN_STAT WHERE ROWNUM =1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
SVR_IP: xxx.xx.xxx.xxx
SVR_PORT: 2882
PLAN_ID: 8
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: 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,
PLAN_HASH: 12254811659671915614
FIRST_LOAD_TIME: 23-JUL-25 02.41.18.001108 PM
SCHEMA_VERSION: 1
LAST_ACTIVE_TIME: 29-JUL-25 04.48.45.360517 PM
AVG_EXE_USEC: 450
SLOWEST_EXE_TIME: 25-JUL-25 02.00.11.264722 AM
SLOWEST_EXE_USEC: 11331
SLOW_COUNT: 0
HIT_COUNT: 174976
PLAN_SIZE: 57640
EXECUTIONS: 174976
DISK_READS: 269
DIRECT_WRITES: 0
BUFFERS_GETS: 1301
APPLICATION_WATI_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 174976
ELAPSED_TIME: 78763828
CPU_TIME: 18991733
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.3.5.3') 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: NULL
FIRST_EXE_USEC: NULL