This topic describes the monitoring metrics related to the time consumption of wait events in an OceanBase Database tenant. OceanBase Cloud Platform (OCP) provides the following metrics related to the time consumption of wait events: all, application_wait, configuration_wait, administrative_wait, concurrency_wait, commit_wait, network_wait, user_io_wait, system_io_wait, cluster_wait, and other_wait. You can specify a statistical period to query the time consumption data of wait events of a tenant in a single zone or on a single OBServer node.
all
Definition
The average amount of time spent on a wait event in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| all | wait_event_rt | μs |
Calculation expression
sum(rate(ob_waitevent_wait_seconds_total{@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_waitevent_wait_total{@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, sum(total_waits) as total_waits, sum(time_waited_micro) / 1000000 as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_idOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, sum(total_waits) as total_waits, sum(time_waited_micro) / 1000000 as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id
application_wait
Definition
The average amount of time spent on a wait event of the APPLICATION class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| application_wait | system_event_application_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="APPLICATION",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
configuration_wait
Definition
The average amount of time spent on a wait event of the CONFIGURATION class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| configuration_wait | system_event_configuration_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="CONFIGURATION",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
administrative_wait
Definition
The average amount of time spent on a wait event of the ADMINISTRATIVE class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| administrative_wait | system_event_administrative_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="ADMINISTRATIVE",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
concurrency_wait
Definition
The average amount of time spent on a wait event of the CONCURRENCY class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| concurrency_wait | system_event_concurrency_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="CONCURRENCY",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
commit_wait
Definition
The average amount of time spent on a wait event of the COMMIT class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| commit_wait | system_event_commit_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="COMMIT",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
network_wait
Definition
The average amount of time spent on a wait event of the NETWORK class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| network_wait | system_event_network_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="NETWORK",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
user_io_wait
Definition
The average amount of time spent on a wait event of the USER_IO class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| user_io_wait | system_event_user_io_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="USER_IO",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
system_io_wait
Definition
The average amount of time spent on a wait event of the SYSTEM_IO class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| system_io_wait | system_event_system_io_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="SYSTEM_IO",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
cluster_wait
Definition
The average amount of time spent on a wait event of the CLUSTER class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| cluster_wait | system_event_cluster_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_time_waited{event_group="CLUSTER",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, wait_class as event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
other_wait
Definition
The average amount of time spent on a wait event of the OTHER class in the tenant within the specified statistical period.
Description
| Metric | Metric name | Unit |
|---|---|---|
| other_wait | system_event_other_time_waited | μs |
Calculation expression
sum(rate(ob_system_event_total_waits{event_group="OTHER",@LABELS}[@INTERVAL])) by (@GBLABELS)
SQL statements for metric collection
OceanBase Database of a version earlier than V4.0:
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) */ con_id tenant_id, case when event_id = 10000 then 'INTERNAL' when event_id = 13000 then 'SYNC_RPC' when event_id = 14003 then 'ROW_LOCK_WAIT' when (event_id >= 10001 and event_id <= 11006) or (event_id >= 11008 and event_id <= 11011) then 'IO' when event like 'latch:%' then 'LATCH' else 'OTHER' END event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_groupOceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, case when event_id = 10000 then 'INTERNAL' when event_id = 13000 then 'SYNC_RPC' when event_id = 14003 then 'ROW_LOCK_WAIT' when (event_id >= 10001 and event_id <= 11006) or (event_id >= 11008 and event_id <= 11011) then 'IO' when event like 'latch:%' then 'LATCH' else 'OTHER' END event_group, sum(total_waits) as total_waits, sum(time_waited_micro / 1000000) as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id, event_group
