Note
- This view is introduced in OceanBase Database V4.3.x since OceanBase Database V4.3.1.
- This view is introduced in OceanBase Database V4.2.x since OceanBase Database V4.2.2.
Purpose
The V$OB_PL_CACHE_OBJECT view displays the basic information about cached PL objects on the current OBServer node of the current tenant.
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 PS 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 time 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 time 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:
|
Sample query
Query the basic information about cached PL objects on the current OBServer node in 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: xx.xx.xx.xx
SVR_PORT: 2882
CACHE_OBJECT_ID: 1860
PARAMETERIZE_TEXT: STANDARD
OBJECT_TEXT: NULL
FIRST_LOAD_TIME: 10-MAY-24 02.22.41.089659 PM
LAST_ACTIVE_TIME: 13-MAY-24 02.22.41.079422 PM
AVG_EXE_USEC: 0
SLOWEST_EXE_TIME: NULL
SLOWEST_EXE_USEC: 0
HIT_COUNT: 4
CACHE_OBJ_SIZE: 23808
EXECUTIONS: 0
ELAPSED_TIME: 0
OBJECT_TYPE: PACKAGE
OBJECT_ID: 310001
COMPILE_TIME: 6941
SCHEMA_VERSION: 1715320512869024
PS_STMT_ID: -1
1 row in set (0.013 sec)