Metrics|V4.2.5|OceanBase Database| docs|Distributed Database

Metrics provide general diagnostics. A metric is a specific value collected during event tracking, such as the number of SQL statement executions, execution time, and I/O throughput. OceanBase Database supports more than 600 metrics, which are classified into 14 classes to provide all key execution information such as the information about the network, request queues, transactions, SQL statements, cache, storage, resource quotas, and logs. Some metrics, such as QPS, TPS, QPS_RT, TPS_RT, and Active_sessions, provide real-time information and are collected by second in OceanBase Database. Metrics of other information are collected by minute.

The data source of the metrics is the GV$SYSSTAT view, which contains the following key fields:

CON_ID: the ID of the tenant.
SVR_IP: the IP address of the node.
CLASS: the class of the metric.
STAT_ID: the ID of the metric.
NAME: the name of the metric.
VALUE: the value of the metric.
VALUE_TYPE: the value type of the metric.

The CLASS field indicates the class of a metric. The following table describes the 14 classes supported by OceanBase Database V4.x.

Class code	Class name
1	Network
2	Request queue
4	Transaction
8	SQL
16	Cache
32	Storage
64	Resource
128	Log
256	Clog
512	Election
1024	OBServer
2048	RootService
3072	Analyze Report
4096	TableAPI

The STAT_ID and NAME fields describe the ID and name of a metric. For example, the following example shows the information about all metrics in the SQL class:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=8;

The return result is as follows:

+-------+---------+------------------------------------+------------+
| class | stat_id | name                               | VALUE_TYPE |
+-------+---------+------------------------------------+------------+
|     8 |   40000 | sql select count                   | ADD_VALUE  |
|     8 |   40001 | sql select time                    | ADD_VALUE  |
|     8 |   40002 | sql insert count                   | ADD_VALUE  |
|     8 |   40003 | sql insert time                    | ADD_VALUE  |
|     8 |   40004 | sql replace count                  | ADD_VALUE  |
|     8 |   40005 | sql replace time                   | ADD_VALUE  |
|     8 |   40006 | sql update count                   | ADD_VALUE  |
|     8 |   40007 | sql update time                    | ADD_VALUE  |
|     8 |   40008 | sql delete count                   | ADD_VALUE  |
|     8 |   40009 | sql delete time                    | ADD_VALUE  |
|     8 |   40018 | sql other count                    | ADD_VALUE  |
|     8 |   40019 | sql other time                     | ADD_VALUE  |
|     8 |   40020 | ps prepare count                   | ADD_VALUE  |
|     8 |   40021 | ps prepare time                    | ADD_VALUE  |
|     8 |   40022 | ps execute count                   | ADD_VALUE  |
|     8 |   40023 | ps close count                     | ADD_VALUE  |
|     8 |   40024 | ps close time                      | ADD_VALUE  |
|     8 |   40025 | sql commit count                   | ADD_VALUE  |
|     8 |   40026 | sql commit time                    | ADD_VALUE  |
|     8 |   40027 | sql rollback count                 | ADD_VALUE  |
|     8 |   40028 | sql rollback time                  | ADD_VALUE  |
|     8 |   40030 | opened cursors current             | ADD_VALUE  |
|     8 |   40031 | opened cursors cumulative          | ADD_VALUE  |
|     8 |   40010 | sql local count                    | ADD_VALUE  |
|     8 |   40011 | sql remote count                   | ADD_VALUE  |
|     8 |   40012 | sql distributed count              | ADD_VALUE  |
|     8 |   40013 | active sessions                    | ADD_VALUE  |
|     8 |   40014 | single query count                 | ADD_VALUE  |
|     8 |   40015 | multiple query count               | ADD_VALUE  |
|     8 |   40016 | multiple query with one stmt count | ADD_VALUE  |
|     8 |   40100 | sql inner select count             | ADD_VALUE  |
|     8 |   40101 | sql inner select time              | ADD_VALUE  |
|     8 |   40102 | sql inner insert count             | ADD_VALUE  |
|     8 |   40103 | sql inner insert time              | ADD_VALUE  |
|     8 |   40104 | sql inner replace count            | ADD_VALUE  |
|     8 |   40105 | sql inner replace time             | ADD_VALUE  |
|     8 |   40106 | sql inner update count             | ADD_VALUE  |
|     8 |   40107 | sql inner update time              | ADD_VALUE  |
|     8 |   40108 | sql inner delete count             | ADD_VALUE  |
|     8 |   40109 | sql inner delete time              | ADD_VALUE  |
|     8 |   40110 | sql inner other count              | ADD_VALUE  |
|     8 |   40111 | sql inner other time               | ADD_VALUE  |
|     8 |   40112 | user logons cumulative             | ADD_VALUE  |
|     8 |   40113 | user logouts cumulative            | ADD_VALUE  |
|     8 |   40114 | user logons failed cumulative      | ADD_VALUE  |
|     8 |   40115 | user logons time cumulative        | ADD_VALUE  |
|     8 |   40116 | sql local execute time             | ADD_VALUE  |
|     8 |   40117 | sql remote execute time            | ADD_VALUE  |
|     8 |   40118 | sql distributed execute time       | ADD_VALUE  |
|     8 |   40119 | sql fail count                     | ADD_VALUE  |
|     8 |   40120 | inner sql local count              | ADD_VALUE  |
|     8 |   40121 | inner sql remote count             | ADD_VALUE  |
|     8 |   40122 | inner sql distributed count        | ADD_VALUE  |
+-------+---------+------------------------------------+------------+
42 rows in set (0.07 sec)

The following table describes the parameters.

Parameter	Description
sql select count	The number of times that the `SELECT` statement is executed.
sql select time	The total time spent in executing the `SELECT` statement, in microseconds.
sql insert count	The number times that the `INSERT` statement is executed.
sql insert time	The total time spent in executing the `INSERT` statement, in microseconds.
sql replace count	The number of times that the `REPLACE` statement is executed.
sql replace time	The total time spent in executing the `REPLACE` statement, in microseconds.
sql update count	The number of times that the `UPDATE` statement is executed.
sql update time	The total time spent in executing the `UPDATE` statement, in microseconds.
sql delete count	The number of times that the `DELETE` statement is executed.
sql delete time	The total time spent in executing the `DELETE` statement, in microseconds.
sql other count	The number of times that other statements are executed.
sql other time	The total time spent in executing other statements, in microseconds.
ps prepare count	The number of PrepareStatements.
ps prepare time	The time spent in executing PrepareStatements, in microseconds.
ps execute count	The number of executed PrepareStatements.
ps close count	The number of closed PrepareStatements.
ps close time	The time spent in closing PrepareStatements, in microseconds.
sql commit count	The number of transaction commits.
sql commit time	The time spent in transaction commits, in microseconds.
sql rollback count	The number of transaction rollbacks.
sql rollback time	The time spent in transaction rollbacks, in microseconds.
opened cursors current	The number of currently open cursors, which is the number of cursors cached in the session that OceanBase Cloud Platform (OCP) uses.
opened cursors cumulative	The cumulative number of open cursors.
sql local count	The number of times that local statements are executed.
sql remote count	The number of times that remote statements are executed.
sql distributed count	The number of times that distributed statements are executed.
active sessions	The number of active sessions.
single query count	The number of single queries.
multiple query count	The total number of statements sent over the Multiple Query protocol.
sql inner select count	The number of times that inner SQL `SELECT` statements are executed.
sql inner select time	The total time spent in executing inner SQL `SELECT` statements, in microseconds.
sql inner insert count	The number times that inner SQL `INSERT` statements are executed.
sql inner insert time	The total time spent in executing inner SQL `INSERT` statements, in microseconds.
sql inner replace count	The number of times that inner SQL `REPLACE` statements are executed.
sql inner replace time	The total time spent in executing inner SQL `REPLACE` statements, in microseconds.
sql inner update count	The number of times that inner SQL `UPDATE` statements are executed.
sql inner update time	The total time spent in executing inner SQL `UPDATE` statements, in microseconds.
sql inner delete count	The number of times that inner SQL `DELETE` statements are executed.
sql inner delete time	The total time spent in executing inner SQL `DELETE` statements, in microseconds.
sql inner other count	The number of times that other inner SQL statements are executed.
sql inner other time	The total time spent in executing other inner SQL statements, in microseconds.
user logons cumulative	The cumulative number of user logins.
user logouts cumulative	The cumulative number of user logouts.
user logons failed cumulative	The cumulative number of failed user logins.
user logons time cumulative	The total time spent on user logins, in microseconds.
sql local execute time	The time spent in executing SQL queries on local database nodes, in microseconds.
sql remote execute time	The time spent in executing SQL queries on remote nodes, in microseconds.
sql distributed execute time	The total time spent in executing SQL queries in distributed transactions or across multiple nodes, in microseconds.
sql fail count	The number of failed SQL executions.
inner sql local count	The number of times that internally generated local SQL statements are executed.
inner sql remote count	The number of times that internally generated remote SQL statements are executed.
inner sql distributed count	The number of times that internally generated distributed SQL statements are executed.

Some metrics, such as QPS, TPS, QPS_RT, and TPS_RT, provide real-time information and are collected by second.

The CON_ID and SVR_IP fields describe the monitored objects of a metric, so that metrics can be flexibly aggregated in different scenarios. For example, you can aggregate the QPS and TPS metrics based on the CON_ID field to observe the changes in the number of requests of a tenant, or the SVR_IP field to observe the changes in the number of requests of a node. When you perform load balancing on a cluster, you can aggregate the QPS and TPS metrics for resource units based on both the CON_ID and SVR_IP fields.

The VALUE_TYPE field describes the value type of the metric. Valid values: ADD_VALUE, which indicates the cumulative type, and SET_VALUE, which indicates the status type. The values of cumulative metrics, such as SQL COUNT, SQL RT, and IO READ BYTES, keep increasing during event tracking in OceanBase Database. External monitoring systems regularly pull the cumulative metric values and calculate the differences to show the data change trend of these metrics. The latest values of status metrics, such as CACHE SIZE and CPU USAGE, are maintained during event tracking in OceanBase Database. External monitoring systems regularly pull and directly display the status metric values. In general, status metrics support aggregation only at the tenant level, while most cumulative metrics support aggregation both at the session and tenant levels.

Metrics of OceanBase Database are classified into 14 classes based on the components that they describe. The following sections describe the most frequently used metrics.

Network metrics

The following example shows how to query the network metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=1 and stat_id in (10000,10001,10002,10003,10004,10005,10006);

The return result is as follows:

+-------+---------+----------------------+------------+
| class | stat_id | name                 | VALUE_TYPE |
+-------+---------+----------------------+------------+
|     1 |   10000 | rpc packet in        | ADD_VALUE  |
|     1 |   10001 | rpc packet in bytes  | ADD_VALUE  |
|     1 |   10002 | rpc packet out       | ADD_VALUE  |
|     1 |   10003 | rpc packet out bytes | ADD_VALUE  |
|     1 |   10004 | rpc deliver fail     | ADD_VALUE  |
|     1 |   10005 | rpc net delay        | ADD_VALUE  |
|     1 |   10006 | rpc net frame delay  | ADD_VALUE  |
+-------+---------+----------------------+------------+
7 rows in set (0.06 sec)

The following table describes the parameters.

Parameter	Description
rpc packet in	The cumulative number of RPC packets sent to the current OBServer node.
rpc packet in bytes	The cumulative bytes of RPC packets sent to the current OBServer node.
rpc packet out	The cumulative number of RPC packets sent by the current OBServer node.
rpc packet out bytes	The cumulative bytes of RPC packets sent by the current OBServer node.
rpc deliver fail	The cumulative number of RPC distribution failures.
rpc net delay	The network latency during RPC distribution, which is the period between receiving time and the sending time.
rpc net frame delay	The latency of the RPC framework during RPC distribution.

Request queue metrics

The following example shows how to query the request queue metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=2 and stat_id in (20000,20001,20002);

The return result is as follows:

+-------+---------+-----------------------+------------+
| class | stat_id | name                  | VALUE_TYPE |
+-------+---------+-----------------------+------------+
|     2 |   20000 | request enqueue count | ADD_VALUE  |
|     2 |   20001 | request dequeue count | ADD_VALUE  |
|     2 |   20002 | request queue time    | ADD_VALUE  |
+-------+---------+-----------------------+------------+
3 rows in set (0.07 sec)

The following table describes the parameters.

Parameter	Description
request enqueue count	The cumulative number of requests that successfully enter the tenant waiting queue.
request dequeue count	The cumulative number of requests that successfully pop out of the tenant waiting queue.
request queue time	The cumulative time that requests take from being received to being processed and queued, which is the period requests wait to end minus the time in the queue, in microseconds.

Transaction metrics

The following example shows how to query the transaction metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=4 and stat_id in (30000,30001,30002,30005,30006,30007,30008,30009,30010,30011,30012,30013,30200,30201,30202,30203,30204,30205,30206,30207,30208,30209,30210,30211,30212,30213,30214,30215,30216,30217,30218,30219);

The return result is as follows:

+-------+---------+----------------------------------+------------+
| class | stat_id | name                             | VALUE_TYPE |
+-------+---------+----------------------------------+------------+
|     4 |   30000 | trans commit log sync time       | ADD_VALUE  |
|     4 |   30001 | trans commit log sync count      | ADD_VALUE  |
|     4 |   30002 | trans commit log submit count    | ADD_VALUE  |
|     4 |   30005 | trans start count                | ADD_VALUE  |
|     4 |   30006 | trans total used time            | ADD_VALUE  |
|     4 |   30007 | trans commit count               | ADD_VALUE  |
|     4 |   30008 | trans commit time                | ADD_VALUE  |
|     4 |   30009 | trans rollback count             | ADD_VALUE  |
|     4 |   30010 | trans rollback time              | ADD_VALUE  |
|     4 |   30011 | trans timeout count              | ADD_VALUE  |
|     4 |   30012 | trans local trans count          | ADD_VALUE  |
|     4 |   30013 | trans distribute trans count     | ADD_VALUE  |
|     4 |   30200 | xa start total count             | ADD_VALUE  |
|     4 |   30201 | xa start total used time         | ADD_VALUE  |
|     4 |   30202 | xa start with rpc total count    | ADD_VALUE  |
|     4 |   30203 | failed xa start total count      | ADD_VALUE  |
|     4 |   30204 | xa end total count               | ADD_VALUE  |
|     4 |   30205 | xa end total used count          | ADD_VALUE  |
|     4 |   30206 | xa end with rpc total count      | ADD_VALUE  |
|     4 |   30207 | failed xa end total count        | ADD_VALUE  |
|     4 |   30208 | xa prepare total count           | ADD_VALUE  |
|     4 |   30209 | xa prepare total used time       | ADD_VALUE  |
|     4 |   30210 | xa prepare with rpc total count  | ADD_VALUE  |
|     4 |   30211 | failed xa prepare total count    | ADD_VALUE  |
|     4 |   30212 | xa commit total count            | ADD_VALUE  |
|     4 |   30213 | xa commit total used time        | ADD_VALUE  |
|     4 |   30214 | xa commit with rpc total count   | ADD_VALUE  |
|     4 |   30215 | failed xa commit total count     | ADD_VALUE  |
|     4 |   30216 | xa rollback total count          | ADD_VALUE  |
|     4 |   30217 | xa rollback total used time      | ADD_VALUE  |
|     4 |   30218 | xa rollback with rpc total count | ADD_VALUE  |
|     4 |   30219 | failed xa rollback total count   | ADD_VALUE  |
+-------+---------+----------------------------------+------------+
32 rows in set (0.013 sec)

The following table describes the parameters.

Parameter	Description
trans commit log sync time	The total duration for synchronizing commit logs (clogs) to the majority of replicas, in microseconds.
trans commit log sync count	The total number of clogs.
trans commit log submit count	The number of clogs committed in the tenant.
trans start count	The number of transactions started in the tenant.
trans total used time	The total time spent on transactions in the tenant, in microseconds.
trans commit count	The number of times that the transaction was committed.
trans commit time	The time consumed in the transaction commit phase, in microseconds.
trans rollback count	The number of transaction rollbacks.
trans rollback time	The time consumed in the transaction rollback phase, in microseconds.
trans timeout count	The number of timed-out transactions.
trans local trans count	The number of local transactions. In OceanBase Database V3.x, the parameter indicates the number of single-partition transactions.
trans distribute trans count	The number of distributed transactions. In OceanBase Database V3.x, the parameter indicates the number of multi-partition transactions.
xa start total count	The total number of `XA_START` statements, including successful and failed ones.
xa start total used time	The total time spent in executing the `XA_START` statements, including successful and failed ones, in microseconds.
xa start with rpc total count	The total number of successful `XA_START` statements by using RPCs.
failed xa start total count	The total number of failed `XA_START` statements.
xa end total count	The total number of `XA_END` statements, including successful and failed ones.
xa end total used count	The total time spent in executing the `XA_END` statements, including successful and failed ones, in microseconds.
xa end with rpc total count	The total number of successful `XA_END` statements by using RPCs.
failed xa end total count	The total number of failed `XA_END` statements.
xa prepare total count	The total number of `XA_PREPARE` statements, including successful and failed ones.
xa prepare total used time	The total time spent in executing the `XA_PREPARE` statements, including successful and failed ones, in microseconds.
xa prepare with rpc total count	The total number of successful `XA_PREPARE` statements by using RPCs.
failed xa prepare total count	The total number of failed `XA_PREPARE` statements.
xa commit total count	The total number of `XA_COMMIT` statements, including successful and failed ones.
xa commit total used time	The total time spent in executing the `XA_COMMIT` statements, including successful and failed ones, in microseconds.
xa commit with rpc total count	The total number of successful `XA_COMMIT` statements by using RPCs.
failed xa commit total count	The total number of failed `XA_COMMIT` statements.
xa rollback total count	The total number of `XA_ROLLBACK` statements, including successful and failed ones.
xa rollback total used time	The total time spent in executing the `XA_ROLLBACK` statements, including successful and failed ones, in microseconds.
xa rollback with rpc total count	The total number of successful `XA_ROLLBACK` statements by using RPCs.
failed xa rollback total count	The total number of failed `XA_ROLLBACK` statements.

SQL metrics

The following example shows how to query the SQL statement metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=8 and stat_id in (40000, 40001, 40002, 40003, 40004, 40005, 40006, 40007, 40008, 40009, 40010, 40011, 40012, 40018, 40019);

The return result is as follows:

+-------+---------+-----------------------+------------+
| class | stat_id | name                  | VALUE_TYPE |
+-------+---------+-----------------------+------------+
|     8 |   40000 | sql select count      | ADD_VALUE  |
|     8 |   40001 | sql select time       | ADD_VALUE  |
|     8 |   40002 | sql insert count      | ADD_VALUE  |
|     8 |   40003 | sql insert time       | ADD_VALUE  |
|     8 |   40004 | sql replace count     | ADD_VALUE  |
|     8 |   40005 | sql replace time      | ADD_VALUE  |
|     8 |   40006 | sql update count      | ADD_VALUE  |
|     8 |   40007 | sql update time       | ADD_VALUE  |
|     8 |   40008 | sql delete count      | ADD_VALUE  |
|     8 |   40009 | sql delete time       | ADD_VALUE  |
|     8 |   40018 | sql other count       | ADD_VALUE  |
|     8 |   40019 | sql other time        | ADD_VALUE  |
|     8 |   40010 | sql local count       | ADD_VALUE  |
|     8 |   40011 | sql remote count      | ADD_VALUE  |
|     8 |   40012 | sql distributed count | ADD_VALUE  |
+-------+---------+-----------------------+------------+
15 rows in set (0.08 sec)

The following table describes the parameters.

Parameter	Description
sql select count	The number of times that the `SELECT` statement is executed.
sql select time	The total time spent in executing the `SELECT` statement, in microseconds.
sql insert count	The number times that the `INSERT` statement is executed.
sql insert time	The total time spent in executing the `INSERT` statement, in microseconds.
sql replace count	The number of times that the `REPLACE` statement is executed.
sql replace time	The total time spent in executing the `REPLACE` statement, in microseconds.
sql update count	The number of times that the `UPDATE` statement is executed.
sql update time	The total time spent in executing the `UPDATE` statement, in microseconds.
sql delete count	The number of times that the `DELETE` statement is executed.
sql delete time	The total time spent in executing the `DELETE` statement, in microseconds.
sql other count	The number of times that other statements are executed.
sql other time	The total time spent in executing other statements, in microseconds.
sql local count	The number of times that local statements are executed.
sql remote count	The number of times that remote statements are executed.
sql distributed count	The number of times that distributed statements are executed.

KVCache metrics

The following example shows how to query the KVCache metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=16 and stat_id in (50000, 50001, 50004, 50005, 50008, 50009, 50010, 50011, 50037, 50038,120000,120001,120008,120009);

The return result is as follows:

+-------+---------+-------------------------+------------+
| class | stat_id | name                    | VALUE_TYPE |
+-------+---------+-------------------------+------------+
|    16 |   50000 | row cache hit           | ADD_VALUE  |
|    16 |   50001 | row cache miss          | ADD_VALUE  |
|    16 |   50004 | bloom filter cache hit  | ADD_VALUE  |
|    16 |   50005 | bloom filter cache miss | ADD_VALUE  |
|    16 |   50008 | block cache hit         | ADD_VALUE  |
|    16 |   50009 | block cache miss        | ADD_VALUE  |
|    16 |   50010 | location cache hit      | ADD_VALUE  |
|    16 |   50011 | location cache miss     | ADD_VALUE  |
|    16 |   50037 | tablet ls cache hit     | ADD_VALUE  |
|    16 |   50038 | tablet ls cache miss    | ADD_VALUE  |
|    16 |  120000 | location cache size     | SET_VALUE  |
|    16 |  120001 | tablet ls cache size    | SET_VALUE  |
|    16 |  120008 | user row cache size     | SET_VALUE  |
|    16 |  120009 | bloom filter cache size | SET_VALUE  |
+-------+---------+-------------------------+------------+
14 rows in set (0.07 sec)

The following table describes the parameters.

Parameter	Description
row cache hit	The number of row cache hits.
row cache miss	The number of row cache misses.
bloom filter cache hit	The number of Bloom filter cache hits.
bloom filter cache miss	The number of Bloom filter cache misses.
block cache hit	The number of block cache hits.
block cache miss	The number of block cache misses.
location cache hit	The number of location cache hits.
location cache miss	The number of location cache misses.
tablet ls cache hit	The cache hit.
tablet ls cache miss	The cache miss.
location cache size	The size of the location cache.
tablet ls cache size	The size of the clog cache.
user row cache size	The size of the user row cache.
bloom filter cache size	The size of the Bloom filter cache.

The GV$SYSSTAT view describes the hit rate of KVCache. To learn about the actual memory usage of each module of KVCache, query the GV$OB_KVCACHE view. Here is an example:

obclient> select DBA_OB_TENANTS.tenant_id,tenant_name, svr_ip,svr_port, cache_name, cache_size 
from GV$OB_KVCACHE, DBA_OB_TENANTS where DBA_OB_TENANTS.tenant_type!='META' and DBA_OB_TENANTS.tenant_id = GV$OB_KVCACHE.tenant_id limit 10;

The return result is as follows:

+-----------+----------------+------------+----------+-----------------------+------------+
| tenant_id | tenant_name    | svr_ip     | svr_port | cache_name            | cache_size |
+-----------+----------------+------------+----------+-----------------------+------------+
|         1 | sys            |xx.xx.xx.xx |     2882 | schema_cache          |   14564352 |
|         1 | sys            |xx.xx.xx.xx |     2882 | tablet_table_cache    |    6241280 |
|         1 | sys            |xx.xx.xx.xx |     2882 | vtable_cache          |   35372032 |
|         1 | sys            |xx.xx.xx.xx |     2882 | opt_column_stat_cache |    6241280 |
|      1008 | mq_t1          |xx.xx.xx.xx |     2882 | index_block_cache     |    6241280 |
|      1008 | mq_t1          |xx.xx.xx.xx |     2882 | user_block_cache      |   10402816 |
|      1008 | mq_t1          |xx.xx.xx.xx |     2882 | user_row_cache        |    6241280 |
|      1010 | oracle_tenant1 |xx.xx.xx.xx |     2882 | index_block_cache     |    6241280 |
|      1008 | mq_t1          |xx.xx.xx.xx |     2882 | bf_cache              |    4160512 |
|      1010 | oracle_tenant1 |xx.xx.xx.xx |     2882 | user_block_cache      |    8322048 |
+-----------+----------------+------------+----------+-----------------------+------------+
10 rows in set (0.06 sec)

Storage metrics

The following example shows how to query the storage metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=32 and stat_id in (60000, 60001, 60002, 60003, 60004, 60005, 60019, 60020, 60021, 60022, 60023, 60024,130000,130001,130002,130004);

The return result is as follows:

+-------+---------+--------------------------------+------------+
| class | stat_id | name                           | VALUE_TYPE |
+-------+---------+--------------------------------+------------+
|    32 |   60000 | io read count                  | ADD_VALUE  |
|    32 |   60001 | io read delay                  | ADD_VALUE  |
|    32 |   60002 | io read bytes                  | ADD_VALUE  |
|    32 |   60003 | io write count                 | ADD_VALUE  |
|    32 |   60004 | io write delay                 | ADD_VALUE  |
|    32 |   60005 | io write bytes                 | ADD_VALUE  |
|    32 |   60019 | memstore read lock succ count  | ADD_VALUE  |
|    32 |   60020 | memstore read lock fail count  | ADD_VALUE  |
|    32 |   60021 | memstore write lock succ count | ADD_VALUE  |
|    32 |   60022 | memstore write lock fail count | ADD_VALUE  |
|    32 |   60023 | memstore wait write lock time  | ADD_VALUE  |
|    32 |   60024 | memstore wait read lock time   | ADD_VALUE  |
|    32 |  130000 | active memstore used           | SET_VALUE  |
|    32 |  130001 | total memstore used            | SET_VALUE  |
|    32 |  130002 | major freeze trigger           | SET_VALUE  |
|    32 |  130004 | memstore limit                 | SET_VALUE  |
+-------+---------+--------------------------------+------------+
16 rows in set (0.06 sec)

The following table describes the parameters.

Parameter	Description
io read count	The number of I/O reads.
io read delay	The I/O read latency.
io read bytes	The number of I/O read bytes.
io write count	The number of I/O writes.
io write delay	The I/O write latency.
io write bytes	The number of I/O write bytes.
memstore read lock succ count	The number of successful snapshot read checks performed on rows.
memstore read lock fail count	The number of failed snapshot read checks performed on rows.
memstore write lock succ count	The number of row locks successfully added to the MemStore.
memstore write lock fail count	The number of row locks failed to be added to the MemStore.
memstore wait write lock time	The time spent in waiting for row locks, in microseconds.
memstore wait read lock time	The time spent in waiting for snapshot reads, in microseconds.
active memstore used	The active MemStore used, which may be larger than the actual value.
total memstore used	The total MemStore used.
major freeze trigger	The threshold for triggering a major compaction.
memstore limit	The maximum memory that the MemStore can use.

Resource metrics

The following example shows how to query the resource metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=64 and stat_id in (140002,140003,140005,140006);

The return result is as follows:

+-------+---------+-----------------+------------+
| class | stat_id | name            | VALUE_TYPE |
+-------+---------+-----------------+------------+
|    64 |  140002 | max memory size | SET_VALUE  |
|    64 |  140003 | memory usage    | SET_VALUE  |
|    64 |  140005 | max cpus        | SET_VALUE  |
|    64 |  140006 | cpu usage       | SET_VALUE  |
+-------+---------+-----------------+------------+
4 rows in set (0.06 sec)

The following table describes the parameters.

Parameter	Description
max memory size	The maximum memory for the tenant.
memory usage	The memory currently in use by the tenant.
min cpus	The value of the `min_cpu` field of UNIT_CONFIG for the tenant × 100.
max cpus	The value of the `max_cpu` field of UNIT_CONFIG for the tenant × 100.
cpu usage	The logical CPU usage. For example, the value `148` represents 148%, indicating that the tenant uses multiple logical CPU cores.

The metrics described in the preceding sample code can be used to calculate the CPU utilization, thread utilization, and memory usage of the tenant based on the following formulas:

Tenant CPU utilization = cpu usage (stat_id = 140006) / max cpus (stat_id = 140005)
Tenant thread utilization = cpu usage (stat_id = 140006) / max cpus (stat_id = 140005)
Tenant memory usage = memory usage (stat_id = 140003) / max memory size (stat_id = 140002)

Clog metrics

The following example shows how to query the clog metrics:

obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=256 and stat_id in (80040,80041,80057);

The return result is as follows:

+-------+---------+---------------------------+------------+
| class | stat_id | name                      | VALUE_TYPE |
+-------+---------+---------------------------+------------+
|   256 |   80040 | clog write count          | ADD_VALUE  |
|   256 |   80041 | clog write time           | ADD_VALUE  |
|   256 |   80057 | clog trans log total size | ADD_VALUE  |
+-------+---------+---------------------------+------------+
3 rows in set (0.07 sec)

The following table describes the parameters.

Parameter	Description
clog write count	The number of disk writes of clogs.
clog write time	The time spent on disk writes of clogs, in microseconds.
clog trans log total size	The total size of committed clogs.

Time model metrics

The following example shows how to query the time model metrics:

obclient> select * from oceanbase.V$STATNAME where class = 3072;

The return result is as follows:

+--------+---------+------------+----------------------------------------+----------------------------------------+-------+
| CON_ID | STAT_ID | STATISTIC# | NAME                                   | DISPLAY_NAME                           | CLASS |
+--------+---------+------------+----------------------------------------+----------------------------------------+-------+
|   1002 |  200001 |        368 | DB time                                | DB time                                |  3072 |
|   1002 |  200002 |        369 | DB CPU                                 | DB CPU                                 |  3072 |
|   1002 |  200005 |        370 | background elapsed time                | background elapsed time                |  3072 |
|   1002 |  200006 |        371 | background cpu time                    | background cpu time                    |  3072 |
|   1002 |  200010 |        372 | non idle wait time                     | non idle wait time                     |  3072 |
|   1002 |  200011 |        373 | idle wait time                         | idle wait time                         |  3072 |
|   1002 |  200012 |        374 | background database time               | background database time               |  3072 |
|   1002 |  200013 |        375 | background database non-idle wait time | background database non-idle wait time |  3072 |
|   1002 |  200014 |        376 | background database idle wait time     | background database idle wait time     |  3072 |
|   1002 |  220001 |        388 | concurrency wait total time            | concurrency wait total time            |  3072 |
|   1002 |  220002 |        389 | user io wait total time                | user io wait total time                |  3072 |
|   1002 |  220003 |        390 | application wait total time            | application wait total time            |  3072 |
+--------+---------+------------+----------------------------------------+----------------------------------------+-------+
12 rows in set (0.184 sec)

The following table describes the parameters.

Notice

If the name of a metric does not contain background, the metric does not incorporate the time spent in the background. For example, the idle wait time metric measures only the time spent on sessions in the foreground, and does not incorporate the time measured by the background database idle wait time metric. The statistical values of the two metrics are mutually exclusive.

Parameter	Description
DB time	The total amount of active time of the database, which is an accumulative value of the CPU time and non-idle wait time, in microseconds.
DB CPU	The CPU time consumed by the database in the active state, in microseconds.
background elapsed time	The total amount of time spent on background process execution, in microseconds.
background cpu time	The CPU time consumed by background processes, in microseconds.
non idle wait time	The amount of non-idle wait time, namely, the total amount of time elapsed when the session is in the wait but not idle state, in microseconds.
idle wait time	The amount of idle wait time, namely, the total amount of time elapsed when the session is in the idle wait state, in microseconds.
background database time	The total amount of time spent in executing database operations by background processes, in microseconds.
background database non-idle wait time	The amount of time spent in executing database operations by background processes in the non-idle wait state, in microseconds.
background database idle wait time	The amount of time spent in executing database operations by background processes in the idle wait state, in microseconds.
concurrency wait total time	The total amount of wait time caused by concurrency issues, such as the wait time caused by resource locking, in microseconds.
user io wait total time	The total amount of time spent by user processes in waiting for the I/O operations, such as data reads from or data writes to the disk, to complete, in microseconds.
application wait total time	The total amount of wait time caused by the application code, such as lock waits caused by row-level locking or explicit lock commands, in microseconds.

Others metrics

In addition to the GV$SYSSTAT view, OceanBase Database provides many other views to describe the metrics from various aspects.

For example, the GV$OB_PROCESSLIST view describes the status of the current sessions of the system and the IDs of the executed SQL statements. This view also provides important information in fields such as SID, SQL_ID, TRANS_ID, and TRACE_ID. It is a useful tool for end-to-end tracing.

The following example shows how to query the GV$OB_PROCESSLIST view for information about active sessions:

obclient> select TENANT,SVR_IP,SVR_PORT,USER,HOST,COMMAND,STATE,INFO,SQL_ID,TRACE_ID 
from GV$OB_PROCESSLIST 
where STATE='ACTIVE' or null limit 10;

The return result is as follows:

+--------+------------+----------+-----------+-------------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------------------------------+
| TENANT | SVR_IP     | SVR_PORT | USER      | HOST              | COMMAND | STATE  | INFO                                                                                                                                    | SQL_ID                           | TRACE_ID                          |
+--------+------------+----------+-----------+-------------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------------------------------+
| sys    |xx.xx.xx.xx |     2882 | DBA_query | xx.xx.xx.xx:51588 | Query   | ACTIVE | select TENANT,SVR_IP,SVR_PORT,USER,HOST,COMMAND,STATE,INFO,SQL_ID,TRACE_ID from GV$OB_PROCESSLIST where STATE='ACTIVE' or null limit 10 | D18F6AE855AD0D7478162DD8EF48C781 | YB4206008451-0005F55893CEA363-0-0 |
+--------+------------+----------+-----------+-------------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------------------------------+
1 row in set (0.04 sec)

The following example shows how to query the GV$OB_PROCESSLIST view for the number of active sessions at the unit level:

obclient>
select 
tenant_id, tenant_name,  svr_ip,
case 
    when cnt is null then 0 
    else cnt end 
as cnt
from (
    select DBA_OB_TENANTS.tenant_name, DBA_OB_TENANTS.tenant_id, svr_ip, cnt 
    from DBA_OB_TENANTS 
    left join (
        select count(`state`='ACTIVE' OR NULL) as cnt, tenant as tenant_name, svr_ip
        from GV$OB_PROCESSLIST
        group by tenant,svr_ip ) t1 
    on DBA_OB_TENANTS.tenant_name = t1.tenant_name 
where DBA_OB_TENANTS.tenant_type!='META'
) t2;

The return result is as follows:

+-----------+-----------------+-------------+------+
| tenant_id | tenant_name     | svr_ip      | cnt  |
+-----------+-----------------+-------------+------+
|         1 | sys             | xx.xx.xx.xx |    0 |
|         1 | sys             | xx.xx.xx.xx |   0  |
|         1 | sys             | xx.xx.xx.xx |   0  |
|      1002 | midas0_3790     | xx.xx.xx.xx |    0 |
|      1002 | midas0_3790     | xx.xx.xx.xx |   0  |
|      1002 | midas0_3790     | xx.xx.xx.xx |   0  |
+-----------+-----------------+-------------+------+
6 rows in set (0.05 sec)

Customer Stories

Documentation

Metrics

Network metrics

Request queue metrics

Transaction metrics

SQL metrics

KVCache metrics

Storage metrics

Resource metrics

Clog metrics

Time model metrics

Notice

Others metrics

Metrics

Network metrics

Request queue metrics

Transaction metrics

SQL metrics

KVCache metrics

Storage metrics

Resource metrics

Clog metrics

Time model metrics

Notice

Others metrics