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 GV$OB_PL_CACHE_OBJECT view displays the basic information about cached PL objects on all OBServer nodes 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 PS 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 time 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 time 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:
|
Sample query
Query the basic information about cached PL objects on all OBServer nodes of the current tenant.
obclient > SELECT * FROM oceanbase.GV$OB_PL_CACHE_OBJECT limit 1 \G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1002
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
CACHE_OBJECT_ID: 632
PARAMETERIZE_TEXT: dbms_stats
OBJECT_TEXT: NULL
FIRST_LOAD_TIME: 2024-05-08 22:00:00.035377
LAST_ACTIVE_TIME: 2024-05-09 14:22:06.954671
AVG_EXE_USEC: 0
SLOWEST_EXE_TIME: 1970-01-01 08:00:00.000000
SLOWEST_EXE_USEC: 0
HIT_COUNT: 1
CACHE_OBJ_SIZE: 15872
EXECUTIONS: 0
ELAPSED_TIME: 0
OBJECT_TYPE: PACKAGE
OBJECT_ID: 310837
COMPILE_TIME: 11624
SCHEMA_VERSION: 1715151645299560
PS_STMT_ID: -1
1 row in set (0.034 sec)