Note
This view was introduced in OceanBase Database V4.2.2.
Purpose
The V$OB_PL_CACHE_OBJECT view displays the basic information about cached PL objects of all tenants.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | NUMBER(38) | NO | The ID of the tenant. Valid values:
|
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node where the cached object resides. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node where the cached object resides. |
| CACHE_OBJECT_ID | NUMBER(38) | NO | The ID of the cached object, which is allocated by libcache. |
| PARAMETERIZE_TEXT | CLOB | NO |
|
| OBJECT_TEXT | CLOB | NO | The text of the query statement. This column is valid only for an anonymous block or a CALL statement in prepared statement mode. |
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the cached object was first loaded, in microseconds. |
| LAST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the cache was last hit, in microseconds. |
| AVG_EXE_USEC | NUMBER(38) | NO | The average execution duration of the cached object, in microseconds. |
| SLOWEST_EXE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The end timestamp of the slowest execution, in microseconds. |
| SLOWEST_EXE_USEC | NUMBER(38) | NO | The duration 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 memory occupied by the cached object. |
| EXECUTIONS | NUMBER(38) | NO | The number of successful executions of the stored object. |
| ELAPSED_TIME | NUMBER(38) | NO | The time when the stored object was last executed, in microseconds. |
| OBJECT_TYPE | CLOB | NO | The type of the cached object, such as procedure, function, or trigger. |
| OBJECT_ID | NUMBER(38) | NO | The ID of the stored object, namely the routine ID or package ID. |
| COMPILE_TIME | NUMBER(38) | NO | The compilation duration of the stored object, in microseconds. |
| SCHEMA_VERSION | NUMBER(38) | NO | The version of the schema. |
| PS_STMT_ID | NUMBER(38) | NO | The ID of the prepared statement corresponding to the request. Valid values:
|
| DB_ID | NUMBER(38) | NO | The database ID used in the compilation of the stored object. |
Sample query
Query the basic information about cached PL objects of the current tenant.
obclient > SELECT * FROM SYS.V$OB_PL_CACHE_OBJECT\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
CACHE_OBJECT_ID: 99
PARAMETERIZE_TEXT: STANDARD
OBJECT_TEXT: NULL
FIRST_LOAD_TIME: 21-OCT-24 02.45.56.733254 PM
LAST_ACTIVE_TIME: 22-OCT-24 04.15.50.947208 PM
AVG_EXE_USEC: 0
SLOWEST_EXE_TIME: NULL
SLOWEST_EXE_USEC: 0
HIT_COUNT: 154
CACHE_OBJ_SIZE: 23808
EXECUTIONS: 0
ELAPSED_TIME: 0
OBJECT_TYPE: PACKAGE
OBJECT_ID: 310001
COMPILE_TIME: 211607
SCHEMA_VERSION: 1729247463268296
PS_STMT_ID: -1
DB_ID: 201006