This topic describes performance metrics related to the query response time in an OceanBase cluster. The query response time metrics collect statistics on the average time consumed by an OBServer node in processing an SQL statement. These metrics are all, select, insert, update, replace, delete, and other. They are classified based on the SQL statement types. You can specify a statistical period to query statistics about the query response time in a single zone or on a single OBServer node of an OceanBase cluster.
all
Definition
The average time consumed by the OBServer node in processing each SQL statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| all | sql_all_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="40001",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40003",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40005",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40007",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40009",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="40019",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="40000",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40002",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40004",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40006",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="40008",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="40018",@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, stat_id, value from v$sysstat where stat_id IN (40001, 40003, 40005, 40007, 40009, 40019, 40000, 40002, 40004, 40006, 40008, 40018) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40001, 40003, 40005, 40007, 40009, 40019, 40000, 40002, 40004, 40006, 40008, 40018) and (con_id > 1000 or con_id = 1) and class < 1000
select
Definition
The average time consumed by the OBServer node in processing each SELECT statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| select | sql_select_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="40001",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="40000",@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, stat_id, value from v$sysstat where stat_id IN (40000, 40001) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40000, 40001) and (con_id > 1000 or con_id = 1) and class < 1000
insert
Definition
The average time consumed by the OBServer node in processing each INSERT statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| insert | sql_insert_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="40003",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="40002",@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, stat_id, value from v$sysstat where stat_id IN (40002, 40003) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40002, 40003) and (con_id > 1000 or con_id = 1) and class < 1000
update
Definition
The average time consumed by the OBServer node in processing each UPDATE statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| update | sql_update_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="40007",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="40006",@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, stat_id, value from v$sysstat where stat_id IN (40006, 40007) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40006, 40007) and (con_id > 1000 or con_id = 1) and class < 1000
replace
Definition
The average time consumed by the OBServer node in processing each REPLACE statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| replace | sql_replace_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="40005",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="40004",@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, stat_id, value from v$sysstat where stat_id IN (40004, 40005) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40004, 40005) and (con_id > 1000 or con_id = 1) and class < 1000
delete
Definition
The average time consumed by the OBServer node in processing a DELETE statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| delete | sql_delete_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="40009",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="40008",@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, stat_id, value from v$sysstat where stat_id IN (40008, 40009) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40008, 40009) and (con_id > 1000 or con_id = 1) and class < 1000
other
Definition
The average time consumed by the OBServer node in processing each of other statements such as data definition language (DDL), data control language (DCL), and data transaction language (DTL) statements.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| other | sql_other_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="40019",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="40000",@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, stat_id, value from v$sysstat where stat_id IN (40000, 40019) and (con_id > 1000 or con_id = 1) and class < 1000OceanBase Database V4.0 and later
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (40000, 40019) and (con_id > 1000 or con_id = 1) and class < 1000
99-all
Definition
The average time spent in processing an SQL statement (TP99).
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| 99-all | sql_all_rt_tp99 | μs |
Calculation expression
histogram_quantile(0.99, rate(ob_query_rt_cumulative_count{@LABELS}[@INTERVAL]))
SQL statements for metric collection
OceanBase Database V4.2.1.7 and later but earlier than V4.2.2.0:
select t1.tenant_id, t1.svr_ip, t1.sql_type, (case when t1.response_time >= t0.max_rt then '+Inf' else t1.response_time * 1000000 end) as response_time, t1.total_count, t1.total_value * 1000000 as total_value, sum(t2.total_count) as cumulative_count, sum(t2.total_value) * 1000000 as cumulative_value from (select max(response_time) as max_rt from gv$ob_query_response_time_histogram) t0 join (select tenant_id, svr_ip, sql_type, response_time, sum(count) as total_count, sum(total) as total_value from gv$ob_query_response_time_histogram group by response_time, tenant_id, svr_ip, sql_type) t1 inner join (select tenant_id, svr_ip, sql_type, response_time, sum(count) as total_count, sum(total) as total_value from gv$ob_query_response_time_histogram group by response_time, tenant_id, svr_ip, sql_type) t2 on t1.response_time >= t2.response_time and t1.tenant_id = t2.tenant_id and t1.svr_ip = t2.svr_ip and t1.sql_type = t2.sql_type where t1.svr_ip = ? group by t1.response_time, t1.tenant_id, t1.sql_type
95-all
Definition
The average time spent in processing an SQL statement (TP95).
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| 95-all | sql_all_rt_tp95 | μs |
Calculation expression
histogram_quantile(0.95, rate(ob_query_rt_cumulative_count{@LABELS}[@INTERVAL]))
SQL statements for metric collection
OceanBase Database V4.2.1.7 and later but earlier than V4.2.2.0:
select t1.tenant_id, t1.svr_ip, t1.sql_type, (case when t1.response_time >= t0.max_rt then '+Inf' else t1.response_time * 1000000 end) as response_time, t1.total_count, t1.total_value * 1000000 as total_value, sum(t2.total_count) as cumulative_count, sum(t2.total_value) * 1000000 as cumulative_value from (select max(response_time) as max_rt from gv$ob_query_response_time_histogram) t0 join (select tenant_id, svr_ip, sql_type, response_time, sum(count) as total_count, sum(total) as total_value from gv$ob_query_response_time_histogram group by response_time, tenant_id, svr_ip, sql_type) t1 inner join (select tenant_id, svr_ip, sql_type, response_time, sum(count) as total_count, sum(total) as total_value from gv$ob_query_response_time_histogram group by response_time, tenant_id, svr_ip, sql_type) t2 on t1.response_time >= t2.response_time and t1.tenant_id = t2.tenant_id and t1.svr_ip = t2.svr_ip and t1.sql_type = t2.sql_type where t1.svr_ip = ? group by t1.response_time, t1.tenant_id, t1.sql_type