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 | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|---|---|
| wait remove partition | The event of waiting for the removal 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 event of waiting for the encapsulation of asynchronous RPCs in the upper layer. | CONCURRENCY | address | - | - |
| dag worker condition wait | The wait event for background DAG threads. | 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 locality protection locks in 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 waiting for user data reading. | USER_IO | fd | offset | size |
| interm result disk read | The event of waiting for intermediate result data reading. | USER_IO | fd | offset | size |
| interm result disk write | The event of waiting for intermediate result data writing. | 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 waiting for writing SQL operator data to the disk. | USER_IO | fd | offset | size |
| build index scheduler condition wait | The event of waiting between two DDL tasks. | 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 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 |
| async commiting wait | The event of waiting for asynchronous clog commits | COMMIT | - | - | - |
| exec inner sql wait | The event of waiting for the execution of inner SQL statements. | OTHER | wait inner SQL class | inner session id | - |
| sync rpc | The synchronization RPC wait event. | NETWORK | pcode | size | - |
| sleep: wait refresh schema | The event of waiting for the refresh of the specified schema version. | CONFIGURATION | sleep_interval | schema_version | - |
| palf throttling sleep | The event of waiting for the throttling of log threads. | USER_IO | sleep_interval | - | - |
| palf read | The event of waiting for log writes of log threads. | SYSTEM_IO | fd | offset | size |
| palf write | The event of waiting for log reads of log threads. | SYSTEM_IO | fd | offset | size |