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 objects.
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 field is NULL. |
| TYPE | NUMBER(38) | NO | For SQL plan caching, this field indicates the plan type:
|
| 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 for the plan. |
| DB_ID | NUMBER(38) | NO | The ID of the database. |
| STATEMENT | CLOB | 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 object types. |
| QUERY_SQL | CLOB | 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 | 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 number. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time of the last execution. |
| 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 has become a slow query. |
| HIT_COUNT | NUMBER(38) | NO | The number of times the plan has been 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 write operations. |
| BUFFERS_GETS | NUMBER(38) | NO | The total number of logical reads. |
| APPLICATION_WAIT_TIME | NUMBER(38) | NO | The total time spent on application events during the execution of the request, in microseconds. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | The total time spent on concurrency events during the execution of the request, in microseconds. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total time spent on user I/O events during the execution of the request, in microseconds. |
| ROWS_PROCESSED | NUMBER(38) | NO | The number of rows processed by the query or modified in the table. |
| ELAPSED_TIME | NUMBER(38) | NO | The total time consumed from receiving the request to completing the execution, 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 times a query is identified as a large query. |
| DELAYED_LARGE_QUERYS | NUMBER(38) | NO | The number of times a query is identified as a large query and is queued in the large query queue. |
| DELAYED_PX_QUERYS | NUMBER(38) | NO | The number of times a parallel query is 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 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.2.x:
|
| TEMP_TABLES | CLOB | NO | SQL plan. If there are no temporary tables, this column is empty. |
| IS_USE_JIT | NUMBER(38) | NO | Indicates whether the SQL plan enables JIT execution. |
| OBJECT_TYPE | CLOB | NO | The type of the cached object. Valid values:
|
| PL_SCHEMA_ID | NUMBER(38) | NO | The schema ID of the PL object. This value is valid for non-anonymous PL objects. For anonymous PL objects and SQL plans, this column is the same as ps_stmt_id. |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Indicates whether the plan uses Batched Multi Stmt optimization. |
| 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 | Indicates the number of consecutive positive or negative feedbacks. |
| FIRST_GET_PLAN_TIME | NUMBER(38) | NO | The hard parsing time of the query. |
| FIRST_EXE_USEC | NUMBER(38) | NO | The execution time of the query. |
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: xx.xx.xx.xx
SVR_PORT: 28825
PLAN_ID: 386
SQL_ID: NULL
TYPE: 7
IS_BIND_SENSITIVE: 0
IS_BIND_AWARE: 0
DB_ID: 201001
STATEMENT: STANDARD
QUERY_SQL: NULL
SPECIAL_PARAMS: NULL
PARAM_INFOS: NULL
SYS_VARS: 45,45,2151677954,+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,45,0,
PLAN_HASH: 0
FIRST_LOAD_TIME: 19-MAR-25 02.17.15.500723 PM
SCHEMA_VERSION: 1742363530459848
LAST_ACTIVE_TIME: 18-APR-25 04.22.15.374071 PM
AVG_EXE_USEC: 0
SLOWEST_EXE_TIME: NULL
SLOWEST_EXE_USEC: 0
SLOW_COUNT: 0
HIT_COUNT: 1733
PLAN_SIZE: 15872
EXECUTIONS: 0
DISK_READS: 0
DIRECT_WRITES: 0
BUFFERS_GETS: 0
APPLICATION_WATI_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
ROWS_PROCESSED: 0
ELAPSED_TIME: 0
CPU_TIME: 0
LARGE_QUERYS: 0
DELAYED_LARGE_QUERYS: 0
DELAYED_PX_QUERYS: 0
OUTLINE_VERSION: 0
OUTLINE_ID: 0
OUTLINE_DATA: NULL
HINTS_INFO: NULL
HINTS_ALL_WORKED: 0
ACS_SEL_INFO: NULL
TABLE_SCAN: 0
EVOLUTION: 0
EVO_EXECUTIONS: 0
EVO_CPU_TIME: 0
TIMEOUT_COUNT: 0
PS_STMT_ID: -1
SESSID: 18446744073709551615
TEMP_TABLES: NULL
IS_USE_JIT: 0
OBJECT_TYPE: PACKAGE
PL_SCHEMA_ID: 310001
IS_BATCHED_MULTI_STMT: 0