Note
- This view is available starting with V4.3.1 in V4.3.x.
- This view is available starting with V4.2.2 in V4.2.x.
Purpose
This view displays basic information about PL-related cache objects in the current server of the current tenant.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TENANT_ID | NUMBER(38) | NO | Tenant ID:
|
| SVR_IP | VARCHAR2(46) | NO | The IP address of the node where the cache object resides. |
| SVR_PORT | NUMBER(38) | NO | The port of the node where the cache object resides. |
| CACHE_OBJECT_ID | NUMBER(38) | NO | The ID of the cache object allocated by the lib cache. |
| PARAMETERIZE_TEXT | CLOB | NO |
|
| OBJECT_TEXT | CLOB | NO | The text of the query statement. This column is valid only in anonymous block and PS mode CALL statements. |
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the cache object was first loaded, in microseconds. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time of the last cache hit, in microseconds. |
| 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, in microseconds. |
| SLOWEST_EXE_USEC | NUMBER(38) | NO | The execution time of the slowest execution, in microseconds. |
| HIT_COUNT | NUMBER(38) | NO | The number of cache hits. |
| CACHE_OBJ_SIZE | NUMBER(38) | NO | The size of the cache object in memory. |
| EXECUTIONS | NUMBER(38) | NO | The number of successful executions of the stored procedure. |
| ELAPSED_TIME | NUMBER(38) | NO | The execution time of the stored procedure in the last execution, in microseconds. |
| OBJECT_TYPE | CLOB | NO | The type of the cache object (for example, Procedure, Function, or Trigger). |
| OBJECT_ID | NUMBER(38) | NO | The ID of the stored procedure object (routine_id/package_id). |
| COMPILE_TIME | NUMBER(38) | NO | The compilation time of the stored procedure, in microseconds. |
| SCHEMA_VERSION | NUMBER(38) | NO | The schema version number. |
| PS_STMT_ID | NUMBER(38) | NO | The Prepare ID corresponding to the request:
|
| DB_ID | NUMBER(38) | NO | The ID of the database used to compile the stored procedure.
Note |
| PL_CG_MEM_HOLD | NUMBER(38) | NO | The memory held during the PL CG phase
Note |
| SYS_VARS | varchar(4096) | NO | The system variables that affect plan generation in the current session
Note |
| PARAM_INFOS | CLOB | NO | The parameterized type information for anonymous blocks and call proc info
Note |
| SQL_ID | varchar(32) | NO | The unique ID generated by using the MD5 hash of the combination of the database name and routine name.
Note |
| OUTLINE_VERSION | NUMBER(38) | NO | The version of the throttling outline. The default value is 0.
Note |
| OUTLINE_ID | NUMBER(38) | NO | The ID of the throttling outline. The default value is -1.
Note |
| CONCURRENT_DATA | CLOB | NO | The throttling content, which defaults to /*+max_concurrent(-1)*/. -1 indicates no throttling.
Note |
Sample query
Query the basic information about PL cache objects in the current server of the current tenant.
obclient > SELECT * FROM SYS.V$OB_PL_CACHE_OBJECT WHERE ROWNUM < = 1 \G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
SVR_IP: 172.xx.xx.xx
SVR_PORT: 2882
CACHE_OBJECT_ID: 567
PARAMETERIZE_TEXT: BEGIN DBMS_STATS.ASYNC_GATHER_STATS_JOB_PROC(?); END
OBJECT_TEXT: BEGIN DBMS_STATS.ASYNC_GATHER_STATS_JOB_PROC(?); END
FIRST_LOAD_TIME: 30-JUL-25 04.46.58.530625 PM
LAST_ACTIVE_TIME: 04-AUG-25 04.31.58.447356 PM
AVG_EXE_USEC: 18762
SLOWEST_EXE_TIME: 30-JUL-25 04.46.59.016867 PM
SLOWEST_EXE_USEC: 486909
HIT_COUNT: 479
CACHE_OBJ_SIZE: 15872
EXECUTIONS: 480
ELAPSED_TIME: 9006025
OBJECT_TYPE: ANONYMOUS
OBJECT_ID: 18446744073709551615
COMPILE_TIME: 76418
SCHEMA_VERSION: -1
PL_EVICT_VERSION: 1753864526437224
PS_STMT_ID: -1
DB_ID: 201006
PL_CG_MEM_HOLD: 345660
SYS_VARS: 4,BYTE,17180131328,
PARAM_INFOS: {1,0,0,-85,15,7,18446744073709551615}
SQL_ID: E6454891418256E7AEE67AC9BBB536B4
OUTLINE_VERSION: 0
OUTLINE_ID: -1
CONCURRENT_DATA: /*+max_concurrent(-1)*/
1 row in set (0.012 sec)