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 the current tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant. Valid values:
|
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node where the cached object resides. |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node where the cached object resides. |
| CACHE_OBJECT_ID | bigint(20) | NO | The ID of the cached object, which is allocated by libcache. |
| PARAMETERIZE_TEXT | longtext | NO |
|
| OBJECT_TEXT | longtext | 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) | NO | The time when the cached object was first loaded, in microseconds. |
| LAST_ACTIVE_TIME | timestamp(6) | NO | The time when the cache was last hit, in microseconds. |
| AVG_EXE_USEC | bigint(20) | NO | The average execution duration of the cached object, in microseconds. |
| SLOWEST_EXE_TIME | timestamp(6) | NO | The end timestamp of the slowest execution, in microseconds. |
| SLOWEST_EXE_USEC | bigint(20) | NO | The duration of the slowest execution, in microseconds. |
| HIT_COUNT | bigint(20) | NO | The number of cache hits. |
| CACHE_OBJ_SIZE | bigint(20) | NO | The size of memory occupied by the cached object. |
| EXECUTIONS | bigint(20) | NO | The number of successful executions of the stored object. |
| ELAPSED_TIME | bigint(20) unsigned | NO | The time when the stored object was last executed, in microseconds. |
| OBJECT_TYPE | longtext | NO | The type of the cached object, such as procedure, function, or trigger. |
| OBJECT_ID | bigint(20) unsigned | NO | The ID of the stored object, namely the routine ID or package ID. |
| COMPILE_TIME | bigint(20) unsigned | NO | The compilation duration of the stored object, in microseconds. |
| SCHEMA_VERSION | bigint(20) | NO | The version of the schema. |
| PS_STMT_ID | bigint(20) | NO | The ID of the prepared statement corresponding to the request. Valid values:
|
| DB_ID | bigint(20) unsigned | NO | The database ID used in the compilation of the stored object. |
| PL_CG_MEM_HOLD | bigint(20) | NO | The memory held during the PL CG phase.
NoteThis column is available starting with V4.2.5 BP2. |
| SYS_VARS | varchar(4096) | NO | The system variables that affect the generation of the plan in the current session.
NoteThis column is available starting with V4.2.5 BP2. |
| PARAM_INFOS | longtext | NO | The parameterized type information of the anonymous block and call proc info. It is used to determine whether the match is successful.
NoteThis column is available starting with V4.2.5 BP2. |
Sample query
Query the basic information about cached PL objects of the current tenant.
obclient > SELECT * FROM oceanbase.V$OB_PL_CACHE_OBJECT\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1002
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
CACHE_OBJECT_ID: 173
PARAMETERIZE_TEXT: dbms_stats
OBJECT_TEXT: NULL
FIRST_LOAD_TIME: 2024-10-21 14:52:16.849673
LAST_ACTIVE_TIME: 2024-10-22 16:22:16.815587
AVG_EXE_USEC: 0
SLOWEST_EXE_TIME: 1970-01-01 08:00:00.000000
SLOWEST_EXE_USEC: 0
HIT_COUNT: 104
CACHE_OBJ_SIZE: 15872
EXECUTIONS: 0
ELAPSED_TIME: 0
OBJECT_TYPE: PACKAGE
OBJECT_ID: 310871
COMPILE_TIME: 30955
SCHEMA_VERSION: 1729493160037360
PS_STMT_ID: -1
DB_ID: 201001