This topic describes the OBKV-Table performance metrics related to the response time of SQL statements within a tenant. The response time is the time consumed by an OBServer node in processing a single SQL statement. To be specific, the response time refers to the period from the time when the OBServer node receives an SQL statement to the time when the SQL statement is executed. You can specify a statistical period to query these metrics of a tenant in a single zone or on a single OBServer node.
all
Definition
The average time spent in processing an SQL statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| all | obkv_table_sql_all_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="190002",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190102",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190202",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190302",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190402",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190502",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191102",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191202",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191302",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190004",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190104",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190204",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190304",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190404",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190504",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191104",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191204",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191304",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190902",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190905",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="190001",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190101",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190201",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190301",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190401",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190501",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191101",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191201",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191301",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190003",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190103",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190203",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190303",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190403",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190503",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191103",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191203",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191303",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190901",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190904",@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 (190002, 190102, 190202, 190302, 190402, 190502, 191102, 191202, 191302, 190004, 190104, 190204, 190304, 190404, 190504, 191104, 191204, 191304, 190902, 190905, 190001, 190101, 190201, 190301, 190401, 190501, 191101, 191201, 191301, 190003, 190103, 190203, 190303, 190403, 190503, 191103, 191203, 191303, 190901, 190904) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190002, 190102, 190202, 190302, 190402, 190502, 191102, 191202, 191302, 190004, 190104, 190204, 190304, 190404, 190504, 191104, 191204, 191304, 190902, 190905, 190001, 190101, 190201, 190301, 190401, 190501, 191101, 191201, 191301, 190003, 190103, 190203, 190303, 190403, 190503, 191103, 191203, 191303, 190901, 190904) and (con_id > 1000 or con_id = 1)
select
Definition
The average time spent in processing a SELECT statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| select | obkv_table_sql_select_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="190002",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190004",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190902",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="190001",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190003",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190901",@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 (190002, 190004, 190902, 190001, 190003, 190901) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190002, 190004, 190902, 190001, 190003, 190901) and (con_id > 1000 or con_id = 1)
insert
Definition
The average time spent in processing an INSERT statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| insert | obkv_table_sql_insert_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="190102",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190402",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191302",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190104",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190404",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191304",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="190101",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190401",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191301",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190103",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190403",@LABELS}[@INTERVAL])) by (@GBLABELS) R_FILL_0_ADD sum(rate(ob_sysstat{stat_id="191303",@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 (190102, 190402, 191302, 190104, 190404, 191304, 190101, 190401, 191301, 190103, 190403, 191303) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190102, 190402, 191302, 190104, 190404, 191304, 190101, 190401, 191301, 190103, 190403, 191303) and (con_id > 1000 or con_id = 1)
update
Definition
The average time spent in processing an UPDATE statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| update | obkv_table_sql_update_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="190302",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191102",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191202",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190304",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191104",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191204",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="190301",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191101",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191201",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190303",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191103",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="191203",@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 (190302, 191102, 191202, 190304, 191104, 191204, 190301, 191101, 191201, 190303, 191103, 191203) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190302, 191102, 191202, 190304, 191104, 191204, 190301, 191101, 191201, 190303, 191103, 191203) and (con_id > 1000 or con_id = 1)
replace
Definition
The average time spent in processing a REPLACE statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| replace | obkv_table_sql_replace_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="190502",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190504",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="190501",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190503",@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 (190502, 190504, 190501, 190503) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190502, 190504, 190501, 190503) and (con_id > 1000 or con_id = 1)
delete
Definition
The average time spent in processing a DELETE statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| delete | obkv_table_sql_delete_rt | μs |
Calculation expression
(sum(rate(ob_sysstat{stat_id="190202",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190204",@LABELS}[@INTERVAL])) by (@GBLABELS)) / (sum(rate(ob_sysstat{stat_id="190201",@LABELS}[@INTERVAL])) by (@GBLABELS) + sum(rate(ob_sysstat{stat_id="190203",@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 (190202, 190204, 190201, 190203) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190202, 190204, 190201, 190203) and (con_id > 1000 or con_id = 1)
query and mutate
Definition
The average time spent in processing a query and mutation statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| query and mutate | obkv_table_sql_query_mutate_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="190905",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="190904",@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 (190905, 190904) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190905, 190904) and (con_id > 1000 or con_id = 1)
other
Definition
The average time spent in processing each of other statements, such as a data definition language (DDL), data control language (DCL), or data transaction language (DTL) statement.
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| other | obkv_table_sql_other_rt | μs |
Calculation expression
sum(rate(ob_sysstat{stat_id="190702",@LABELS}[@INTERVAL])) by (@GBLABELS) / sum(rate(ob_sysstat{stat_id="190701",@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 (190702, 190701) and (con_id > 1000 or con_id = 1)OceanBase Database V4.0 and later:
select /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat where stat_id IN (190702, 190701) and (con_id > 1000 or con_id = 1)
99-all
Definition
The average time spent in processing an SQL statement (TP99).
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| 99-all | obkv_table_sql_all_rt_tp99 | μs |
Calculation expression
histogram_quantile(0.99, sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI SELECT", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI INSERT", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI DELETE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI UPDATE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI REPLACE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI QUERY AND MUTATE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI OTHER", @LABELS}[@INTERVAL])) by (@GBLABELS,le))
SQL statements for metric collection
WITH histogram_data AS (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 ON 1=1 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 = ? AND t1.sql_type IN ('SELECT', 'INSERT', 'DELETE', 'UPDATE', 'REPLACE', 'COMMIT', 'OTHER', 'INNER SQL', 'TABLEAPI SELECT', 'TABLEAPI INSERT', 'TABLEAPI DELETE', 'TABLEAPI UPDATE', 'TABLEAPI REPLACE', 'TABLEAPI QUERY AND MUTATE', 'TABLEAPI OTHER', 'HBASE SCAN', 'HBASE PUT', 'HBASE DELETE', 'HBASE APPEND', 'HBASE INCREMENT', 'HBASE CHECK AND PUT', 'HBASE CHECK AND DELETE', 'HBASE HYBRID BATCH', 'REDIS SET', 'REDIS GET', 'REDIS HSET', 'REDIS HGET', 'REDIS LPUSH', 'REDIS LPOP', 'REDIS SADD', 'REDIS ZADD') GROUP BY t1.response_time, t1.tenant_id, t1.sql_type) SELECT * FROM (SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type NOT LIKE 'TABLEAPI %' AND sql_type NOT LIKE 'HBASE %' AND sql_type NOT LIKE 'REDIS %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type = sql_type AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'TABLEAPI %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'TABLEAPI %' AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'HBASE %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'HBASE %' AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'REDIS %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'REDIS %' AND cumulative_count > 0)) all_histogram_data ORDER BY tenant_id, svr_ip, sql_type, response_time
95-all
Definition
The average time spent in processing an SQL statement (TP95).
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| 95-all | obkv_table_sql_all_rt_tp95 | μs |
Calculation expression
histogram_quantile(0.95, sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI SELECT", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI INSERT", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI DELETE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI UPDATE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI REPLACE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI QUERY AND MUTATE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI OTHER", @LABELS}[@INTERVAL])) by (@GBLABELS,le))
SQL statements for metric collection
WITH histogram_data AS (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 ON 1=1 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 = ? AND t1.sql_type IN ('SELECT', 'INSERT', 'DELETE', 'UPDATE', 'REPLACE', 'COMMIT', 'OTHER', 'INNER SQL', 'TABLEAPI SELECT', 'TABLEAPI INSERT', 'TABLEAPI DELETE', 'TABLEAPI UPDATE', 'TABLEAPI REPLACE', 'TABLEAPI QUERY AND MUTATE', 'TABLEAPI OTHER', 'HBASE SCAN', 'HBASE PUT', 'HBASE DELETE', 'HBASE APPEND', 'HBASE INCREMENT', 'HBASE CHECK AND PUT', 'HBASE CHECK AND DELETE', 'HBASE HYBRID BATCH', 'REDIS SET', 'REDIS GET', 'REDIS HSET', 'REDIS HGET', 'REDIS LPUSH', 'REDIS LPOP', 'REDIS SADD', 'REDIS ZADD') GROUP BY t1.response_time, t1.tenant_id, t1.sql_type) SELECT * FROM (SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type NOT LIKE 'TABLEAPI %' AND sql_type NOT LIKE 'HBASE %' AND sql_type NOT LIKE 'REDIS %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type = sql_type AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'TABLEAPI %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'TABLEAPI %' AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'HBASE %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'HBASE %' AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'REDIS %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'REDIS %' AND cumulative_count > 0)) all_histogram_data ORDER BY tenant_id, svr_ip, sql_type, response_time
90-all
Definition
The average time spent in processing an SQL statement (TP90).
Description
Metric category |
Metric name |
Unit |
|---|---|---|
| 90-all | obkv_table_sql_all_rt_tp90 | μs |
Calculation expression
histogram_quantile(0.90, sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI SELECT", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI INSERT", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI DELETE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI UPDATE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI REPLACE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI QUERY AND MUTATE", @LABELS}[@INTERVAL])) by (@GBLABELS,le) FILL_0_ADD sum(rate(ob_query_rt_cumulative_count{sql_type="TABLEAPI OTHER", @LABELS}[@INTERVAL])) by (@GBLABELS,le))
SQL statements for metric collection
WITH histogram_data AS (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 ON 1=1 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 = ? AND t1.sql_type IN ('SELECT', 'INSERT', 'DELETE', 'UPDATE', 'REPLACE', 'COMMIT', 'OTHER', 'INNER SQL', 'TABLEAPI SELECT', 'TABLEAPI INSERT', 'TABLEAPI DELETE', 'TABLEAPI UPDATE', 'TABLEAPI REPLACE', 'TABLEAPI QUERY AND MUTATE', 'TABLEAPI OTHER', 'HBASE SCAN', 'HBASE PUT', 'HBASE DELETE', 'HBASE APPEND', 'HBASE INCREMENT', 'HBASE CHECK AND PUT', 'HBASE CHECK AND DELETE', 'HBASE HYBRID BATCH', 'REDIS SET', 'REDIS GET', 'REDIS HSET', 'REDIS HGET', 'REDIS LPUSH', 'REDIS LPOP', 'REDIS SADD', 'REDIS ZADD') GROUP BY t1.response_time, t1.tenant_id, t1.sql_type) SELECT * FROM (SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type NOT LIKE 'TABLEAPI %' AND sql_type NOT LIKE 'HBASE %' AND sql_type NOT LIKE 'REDIS %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type = sql_type AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'TABLEAPI %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'TABLEAPI %' AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'HBASE %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'HBASE %' AND cumulative_count > 0) UNION SELECT tenant_id, svr_ip, sql_type, response_time, cumulative_count FROM histogram_data WHERE sql_type LIKE 'REDIS %' AND EXISTS (SELECT 1 FROM histogram_data g WHERE g.sql_type LIKE 'REDIS %' AND cumulative_count > 0)) all_histogram_data ORDER BY tenant_id, svr_ip, sql_type, response_time