OceanBase Database provides a variety of views and internal tables to help you diagnose database issues. This topic describes several most commonly used views. You can identify the possible causes of issues by querying the views.
Check information in views
| View | Description |
|---|---|
| v$sysstat and gv$sysstat | These views display system statistics by server or cluster, such as queries per second (QPS), transactions per second (TPS), input/output operations per second (IOPS), and the cache hit rate. |
| v$latch and gv$latch | These views display the number of times latches are added, the number of spin operations performed by latches, and the waiting duration of latches. They can help you identify system hotspots. |
| v$lock_wait_stat and gv$lock_wait_stat | These views display the information about data locks. They can help you identify lock conflicts and hotspot rows in the system. |
| v$session_wait, gv$session_wait, v$session_wait_history, and gv$session_wait_history | These views display the number and the waiting duration of wait events, such as disk I/O wait events and remote procedure call (RPC) wait events, by session. They can help you identify the causes of slow SQL executions and identify system bottlenecks. |
The preceding table describes some common views that you can use to diagnose database issues.
General SQL statements
Query the number of executions, which is a cumulative value. The QPS is the difference between two cumulative values at an interval of 1s.
obclient> SELECT con_id,sum(value) FROM v$sysstat WHERE name in ('sql SELECT count','sql insert count','sql replace count','sql update count','sql delete count') GROUP BY con_id having con_id>1000 ;+--------+------------+ +--------+------------+ | con_id | sum(value) | +--------+------------+ | 1001 | 89 | +--------+------------+ 1 row in set (0.01 sec)con_idindicates the tenant ID. If the value ofcon_idis larger than or equal to 1000, the tenant is a business tenant. Tenants with other con_id values are system tenants.Query the statistics on latches.
obclient> SELECT con_id,svr_ip,gets,IMMEDIATE_GETS,SPIN_GETS,IMMEDIATE_MISSES FROM v$latch WHERE con_id>1000 GROUP BY con_id,svr_ip; +--------+----------------+------+----------------+-----------+------------------+ | con_id | svr_ip | gets | IMMEDIATE_GETS | SPIN_GETS | IMMEDIATE_MISSES | +--------+----------------+------+----------------+-----------+------------------+ | 1001 | 10.0.0.0 | 1068 | 0 | 1106 | 0 | +--------+----------------+------+----------------+-----------+------------------+ 1 row in set (0.01 sec)con_idindicates the tenant ID. If the value ofcon_idis larger than 1000, the tenant is a business tenant. Tenants with other con_id values are system tenants.svr_ipindicates the IP address of the server where the tenant is located.getsindicates the number of latch requests inWilling to waitmode.immediate_getsindicates the number of latch requests inimmediatemode.spin_getsindicates the number of successful requests with a failure of the first attempt.immediate_missesindicates the number of failed requests.Query row lock information.
SELECT tenant_id,table_name,rowkey,session_id FROM v$lock_wait_stat ;tenant_idindicates the tenant ID.table_nameindicates the table to which the lock belongs.rowkeyindicates the data row to which the lock belongs.session_idindicates the session ID.Query session wait information.
obclient> SELECT sid,con_id,event,state,wait_time_micro FROM v$session_wait; +------------+--------+----------+-------------------+-----------------+ | sid | con_id | event | state | wait_time_micro | +------------+--------+----------+-------------------+-----------------+ | 3221588444 | 1001 | sync rpc | WAITED SHORT TIME | 6126 | | 3221527210 | 1001 | sync rpc | WAITED KNOWN TIME | 12443 | | 3221527229 | 1001 | sync rpc | WAITED SHORT TIME | 9980 | | 3221585714 | 1001 | sync rpc | WAITED KNOWN TIME | 72616 | +------------+--------+----------+-------------------+-----------------+ 4 rows in set (0.01 sec)sidindicates the session ID.con_idindicates the tenant ID.eventindicates the wait event.stateindicates the state.wait_time_microindicates the wait time.
For more information, see "System views" in Reference Guide (MySQL Mode) and "System views" in Reference Guide (Oracle Mode).