Note
The name of this view 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 in the current tenant.
Notice
The view caches SQL plan objects as well as 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 | NUMBER(38) | NO | Tenant ID. |
| SVR_IP | VARCHAR2(46) | NO | IP address of the server. |
| SVR_PORT | NUMBER(38) | NO | Port number of the server. |
| PLAN_ID | NUMBER(38) | NO | ID of the cached object. |
| SQL_ID | VARCHAR2(32) | NO | ID of the corresponding SQL statement. If the cached object is a PL object, this field is NULL. |
| TYPE | NUMBER(38) | NO | For cached SQL plans, this field indicates the type of the plan:
|
| IS_BIND_SENSITIVE | NUMBER(38) | NO | Indicates whether the plan needs to be enabled for ACS. |
| IS_BIND_AWARE | NUMBER(38) | NO | Indicates whether the plan is enabled for ACS. |
| DB_ID | NUMBER(38) | NO | ID of the database. |
| STATEMENT | CLOB | NO | For SQL plans, this field is the parameterized SQL statement; for anonymous blocks, this field is the parameterized anonymous block statement; for other objects, this field is invalid. |
| QUERY_SQL | CLOB | NO | For SQL plans, this field is the original SQL statement when the plan is first loaded; for anonymous blocks, this field is the parameterized anonymous block statement. |
| SPECIAL_PARAMS | VARCHAR2(4096) | NO | Indicated the values of parameters that cannot be parameterized. |
| PARAM_INFOS | CLOB | NO | Parameterization information. |
| SYS_VARS | VARCHAR2(4096) | NO | Values of system variables that affect the cached object. |
| PLAN_HASH | NUMBER(38) | NO | Hash value of the SQL plan. |
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | Time when the plan is first loaded. |
| SCHEMA_VERSION | NUMBER(38) | NO | Schema version number. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | Time when the plan is last executed. |
| AVG_EXE_USEC | NUMBER(38) | NO | Average execution time. |
| SLOWEST_EXE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | Timestamp of the slowest execution. |
| SLOWEST_EXE_USEC | NUMBER(38) | NO | Duration of the slowest execution. |
| SLOW_COUNT | NUMBER(38) | NO | Number of times the SQL plan has been slow queries. |
| HIT_COUNT | NUMBER(38) | NO | Number of hits. |
| PLAN_SIZE | NUMBER(38) | NO | Size of the cached object in memory. |
| EXECUTIONS | NUMBER(38) | NO | Number of executions. |
| DISK_READS | NUMBER(38) | NO | Number of physical reads. |
| DIRECT_WRITES | NUMBER(38) | NO | Number of writes to disk. |
| BUFFERS_GETS | NUMBER(38) | NO | Number of logical reads. |
| APPLICATION_WAIT_TIME | NUMBER(38) | NO | Total time of all application events during the execution of the request. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | Total time of all concurrency events during the execution of the request. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | Total time of all user_io events during the execution of the request. |
| ROWS_PROCESSED | NUMBER(38) | NO | Number of rows returned by the query or changed in the table during the execution of the query. |
| ELAPSED_TIME | NUMBER(38) | NO | Time from receiving the request to the end of execution. |
| CPU_TIME | NUMBER(38) | NO | CPU time consumed during execution. |
| LARGE_QUERYS | NUMBER(38) | NO | Number of large queries. |
| DELAYED_LARGE_QUERYS | NUMBER(38) | NO | Number of large queries that are put into the large query queue. |
| DELAYED_PX_QUERYS | NUMBER(38) | NO | Number of parallel queries that are retried in the queue. |
| OUTLINE_VERSION | NUMBER(38) | NO | Outline version number. |
| OUTLINE_ID | NUMBER(38) | NO | ID of the outline. If the plan is not generated by binding an outline, the value of this field is -1. |
| OUTLINE_DATA | CLOB | NO | Outline information corresponding to the plan. |
| HINTS_INFO | CLOB | NO | 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 | Selection rate space of 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 | Number of evolutions. |
| EVO_CPU_TIME | NUMBER(38) | NO | Total CPU time consumed during evolution. |
| TIMEOUT_COUNT | NUMBER(38) | NO | Number of timeouts. |
| PS_STMT_ID | NUMBER(38) | NO | Prepare ID of 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 this value is the unique identifier returned by the PS protocol for the statement. |
| SESSID | NUMBER(38) | NO | ID of the session where the cached object is located. For V4.3.x:
|
| TEMP_TABLES | CLOB | NO | Names of temporary tables in the SQL plan. If there are no temporary tables, this field is empty. |
| IS_USE_JIT | NUMBER(38) | NO | Indicates whether expression compilation and execution are enabled for the SQL plan. |
| OBJECT_TYPE | CLOB | NO | Type of the cached object: |
| PL_SCHEMA_ID | NUMBER(38) | NO | For non-anonymous blocks, this field is the schema ID of the cached object; for anonymous blocks, this field is the same as the ps_stmt_id field; for SQL plans, this field is meaningless. |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Indicates whether the plan is optimized for Batched Multi Stmt. |
Sample query
Query the status of each cached object in the plan cache for the current tenant on all OBServer nodes.
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