Overview
The information about wait events is displayed in the following dynamic performance views:
V$SESSION_WAIT: displays the current or last wait events of sessions.V$SYSTEM_EVENT: displays all wait events of the current tenant.V$SESSION_EVENT: displays all wait events for each session. This view is similar toV$SYSTEM_EVENT.
Many wait events are associated with internal implementation, and therefore may be deleted or changed. The actual wait events shall prevail.
Wait event classes
Each wait event belongs to a specific class of wait event. Wait events are classified into the following classes:
- Administrative User wait events caused by a DBA command, such as index rebuilding.
- Application Wait events caused by user application code, such as a lock wait event caused by a row lock or by an explicit lock command.
- Cluster Wait events associated with cluster resources, such as the "gc cr block busy" event and other events related to global cache resources.
- Commit This class contains only one wait event, which is the event of waiting for the confirmation of Redo log writes upon commit, where the confirmation is also known as "log file synchronization".
- Concurrency Events of waiting for internal database resources, such as latches.
- Configuration Wait events caused by insufficient database or instance resource specifications, such as insufficient log file size or shared pool size.
- Idle Wait events that indicate the inactive status of a session, such as "SQL*Net message from client".
- Network Wait events associated with network messaging, such as "SQL*Net more data to dblink".
- Other Unexpected wait events in the system, such as "waiting for EMON generation".
- Queueing Delay events caused by the obtaining of additional data in a pipeline environment. A long time consumed by these events indicates the inefficiency or other problems of the pipeline. They affect features such as parallel queries and DBMS_PIPE PL/SQL packages.
- Scheduler Wait events associated with the resource manager, such as 'resmgr: become active'.
- System I/O Events of waiting for background process I/O resources, such as DBWR waits for 'db file parallel write'.
- User I/O Events of waiting for user I/O resources, such as "db file sequential read".
View wait events
You can execute SQL statements under the system tenant to display all wait events and their classes in alphabetical order. Here is an example:
obclient> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
General wait events
| Event name | Description | Event class | 1st parameter of the wait event | 2nd parameter of the wait event | 3rd parameter of the wait event |
|---|---|---|---|---|---|
| wait remove partition | The event of waiting for the deletion of partitions to complete. | ADMINISTRATIVE | tenant_id | table_id | partition_id |
| wait end stmt | The event of waiting for a statement to end. | CLUSTER | rollback | trans_hash_value | physic_plan_type |
| wait start stmt | The event of waiting for a statement to start. | CLUSTER | trans_hash_value | physic_plan_type | participant_count |
| wait end trans | The event of waiting for a transaction to commit. | COMMIT | rollback | trans_hash_value | participant_count |
| async rpc proxy condition wait | The asynchronous RPC wait event in top-level encapsulation. | CONCURRENCY | address | - | - |
| dag worker condition wait | The dag thread wait event in the background. | CONCURRENCY | address | - | - |
| latch: alive server tracer lock wait | The event of waiting for the OBServer status lock in the OBAliveServerMap operation memory. | CONCURRENCY | address | number | tries |
| latch: alloc object lock wait | The lock wait event during the management of small memory blocks in OceanBase Database by using the malloc() function. | CONCURRENCY | address | number | tries |
| latch: clog cascading info lock wait | The lock wait event of the clog replica cascading module. | CONCURRENCY | address | number | tries |
| latch: clog locality lock wait | The event of waiting for the lock of locality information for internal protection of clogs. | CONCURRENCY | address | number | tries |
| latch: clog task lock wait | The event of waiting for the log_task lock of the clog module. | CONCURRENCY | address | number | tries |
| latch: config lock wait | The event of waiting for the parameter lock. | CONCURRENCY | address | number | tries |
| latch: default drw lock wait | The DRW lock wait event. The DRW lock is a read-write lock that adapts to scenarios with many read operations and few write operations. | CONCURRENCY | address | number | tries |
| latch: default mutex wait | The default mutex wait event. | CONCURRENCY | address | number | tries |
| latch: default recursive mutex wait | The default recursive mutex wait event. | CONCURRENCY | address | number | tries |
| latch: default spin lock wait | The default spin lock wait event. | CONCURRENCY | address | number | tries |
| latch: default spin rwlock wait | The default spin read-write lock wait event. | CONCURRENCY | address | number | tries |
| latch: id map node lock wait | The ID Map wait event. The ID Map maps integers to pointers. | CONCURRENCY | address | number | tries |
| latch: server maintaince lock wait | The lock wait event when the RootServer (RS) performs an operation on an OBServer node. | CONCURRENCY | address | number | tries |
| latch: server status lock wait | The read-write lock wait event when an operation is performed on server_statuses recorded by the RS. | CONCURRENCY | address | number | tries |
| latch: unit manager lock wait | The lock wait event when the Unit Manager operates on data. | CONCURRENCY | address | number | tries |
| latch: zone infos rw lock wait | The lock wait event when OBZoneManagerBase operates on data. | CONCURRENCY | address | number | tries |
| latch: zone manager maintaince lock wait | The lock wait event when the Zone Manager operates on data. | CONCURRENCY | address | number | tries |
| partition table updater condition wait | The conditional wait event when OBUniqTaskQueue processes tasks. | CONCURRENCY | address | - | - |
| rebalance task mgr condition wait | The conditional wait event when OBDRTaskMgr processes disaster recovery tasks. | CONCURRENCY | address | - | - |
| reentrant thread condition wait | The conditional wait event when OBReentrantThread processes tasks. | CONCURRENCY | address | - | - |
| rpc session handler condition wait | The streaming RPC wait event. | CONCURRENCY | address | - | - |
| th worker condition wait | The multi-tenant thread suspension wait event. | CONCURRENCY | address | - | - |
| thread idling condition wait | The RootService idle thread wait event. | CONCURRENCY | address | - | - |
| sched idle | The task execution wait event of the multi-tenant thread. | IDLE | wait start timestamp | - | - |
| mysql response wait client | The client response wait event. | NETWORK | - | - | - |
| sync rpc | The synchronization RPC wait event. | NETWORK | pcode | size | - |
| sched wait | The event of waiting for the multi-tenant thread to execute large queries. | SCHEDULER | req type | req start timestamp | wait start timestamp |
| bloomfilter build read | The event of waiting for the background BloomFilter to build data read operation. | SYSTEM_IO | fd | offset | size |
| db file compact read | The event of waiting for data read in a compaction task. | SYSTEM_IO | fd | offset | size |
| db file compact write | The event of waiting for data write in a compaction task. | SYSTEM_IO | fd | offset | size |
| db file index build read | The event of waiting for index building and reading for the database file. | SYSTEM_IO | fd | offset | size |
| db file index build write | The event of waiting for index building and writing for the database file. | SYSTEM_IO | fd | offset | size |
| db file migrate read | The migration data read wait event. | SYSTEM_IO | fd | offset | size |
| db file migrate write | The migration data write wait event. | SYSTEM_IO | fd | offset | size |
| memstore memory page alloc wait | The event of waiting for MemStore write throttling to be triggered. | SYSTEM_IO | cur_mem_hold | sleep_interval | cur_ts |
| db file data read | The event of reading user data. | USER_IO | fd | offset | size |
| interm result disk read | The event of reading intermediate result data. | USER_IO | fd | offset | size |
| interm result disk write | The event of writing intermediate result data. | USER_IO | fd | offset | size |
| row store disk read | The event of reading SQL operator data from the disk. | USER_IO | fd | offset | size |
| row store disk write | The event of writing SQL operator data to the disk. | USER_IO | fd | offset | size |
| build index scheduler condition wait | The wait before DDL task scheduling is performed again. | CONCURRENCY | address | - | - |
| debug sync condition wait | This event is meaningful only in Errsim debug mode and is meaningless in user scenarios. | CONCURRENCY | address | - | - |
| inner connection pool condition wait | The conditional wait event of the internal connection pool. | CONCURRENCY | address | - | - |
| latch: bandwidth throttle lock wait | The bandwidth throttling lock wait event. | CONCURRENCY | address | number | tries |
| latch: default bucket lock wait | The bucket lock wait event, which is not classified. | CONCURRENCY | address | number | tries |
| latch: election group trace recorder lock wait | The event of waiting for the lock that protects the election group trace information. | CONCURRENCY | address | number | tries |
| latch: group migrate lock wait | The batch migration lock wait event. | CONCURRENCY | address | number | tries |
| latch: group migrate task lock wait | The task lock wait event in batch migration. | CONCURRENCY | address | number | tries |
| latch: io queue lock wait | The lock wait event in I/O queue reads. | CONCURRENCY | address | number | tries |
| latch: migrate lock wait | The lock wait event in migration. | CONCURRENCY | address | number | tries |
| latch: multi tenant lock wait | The lock wait event in multi-tenant management. | CONCURRENCY | address | number | tries |
| latch: rebuild retry list lock wait | The rebuild task lock wait event. | CONCURRENCY | address | number | tries |
| latch: session trace recorder lock wait | The trace record lock wait event in a session. | CONCURRENCY | address | number | tries |
| latch: tenant lock wait | The event of waiting for the lock for tenant management. | CONCURRENCY | address | number | tries |
| latch: timezone lock wait | The Timezone structure of lock wait event in a tenant. The lock is added for a tenant operation. | CONCURRENCY | address | number | tries |
| oblog part mgr schema version wait | The event of waiting for the liboblog to refresh the schema version logic. | CONCURRENCY | - | - | - |
| server object pool arena lock wait | The event of waiting for the lock of large object cache structures. | USER_IO | fd | offset | size |