This topic describes the performance monitoring and diagnostics for OceanBase tenants.
View the performance metrics of a tenant
Log on to the OCP console. In the left-side navigation pane, click Tenants.
In the Tenants list on the Tenant Overview page, find the target tenant and click its name.
In the left-side navigation pane on the page that appears, click Performance Monitoring.
Enable real-time performance monitoring to view the real-time performance changes.
| Filter criterion | Description |
|---|---|
| Select Time | The time range of the metric data to be displayed. You can view data for the last 30 days. |
| Stat Period | The statistical period of metric data. You can choose to calculate and display the average values of collected metric data by minute or second. By default, data is displayed by minute. |
| Zone | The zone whose metric data you want to view. OceanBase Database is a distributed database service, and data replicas of each tenant are distributed in multiple zones. Tenant metrics are collected and aggregated from replicas in multiple zones. The aggregation method depends on the metric type. You can select a zone to show metric data of the replica in the zone. |
| OBServer | The OBServer node whose metric data you want to view. OceanBase Database is a distributed database service, and data replicas of each tenant are distributed on multiple OBServer nodes. Tenant metrics are collected and aggregated from replicas on multiple OBServer nodes. The aggregation method depends on the metric type. You can select an OBServer node to show metric data of the replica on the OBServer node. |
Tenant performance metrics are classified by performance and SQL, transaction, and storage and cache.
Diagnose tenant SQL statements
Log on to the OCP console. In the left-side navigation pane, click Tenants.
In the Tenants list on the Tenant Overview page, find the target tenant and click its name.
In the left-side navigation pane of the page that appears, click SQL Diagnostics.
OCP provides three capabilities to diagnose SQL statements from the perspectives of performance exceptions, global control, and slow queries.
| Capability | Description |
|---|---|
| Suspicious SQL statements | The SQL diagnostics engine of OCP identifies abnormal SQL statements and provides diagnostics results. The engine can identify execution plan changes, performance degradation, and execution spikes. We recommend that you optimize SQL statements preferentially based on the suspected SQL diagnostics results. |
| TopSQL | TopSQL diagnostics is also known as full SQL diagnostics. It sorts SQL statements by response time and displays the top 2,000 ones. TopSQL diagnostics help you control all SQL statements that you execute. |
| SlowSQL | The SQL diagnostics engine of OCP extracts SQL statements that take longer than 100 ms in a single execution. You can optimize the statements of slow queries based on the results of SlowSQL diagnostics. |
If no optimal index is specified for an SQL execution plan, you can bind an index on the SQL Details page. After the index is bound, new requests are handled based on the new execution plan.
Monitor and diagnose sessions
Log on to the OCP console. In the left-side navigation pane, click Tenants.
In the Tenants list on the Tenant Overview page, find the target tenant and click its name.
In the left-side navigation pane of the page that appears, click Session Management.
The following table describes the modules of the session management feature.
| Module | Description |
|---|---|
| Tenant Session | This module shows information about the current sessions, including active and inactive sessions. You can kill a specific session. |
| Session Statistics | This module allows you to analyze sessions by user, source IP address, database, and status. |
| Deadlock Analyses | This module supports deadlock diagnostics for the current tenant or a specific session. |
| ASH Report | An Active Sessions History (ASH) report records active sessions in the last 8 days. It allows you to analyze the session history within any 60 minutes in the last 8 days at a time for troubleshooting. You can view and download the report. OCP retains ASH reports generated in the last 90 days. |
View performance data by using views
| Views | Description |
|---|---|
| v$sesstat and gv$sesstat | These views display statistics by session, such as QPS, TPS, IOPS, and the cache hit rate. |
| 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. |
| v$sql_audit | Displays the execution information about each SQL statement, such as the request source, amount of time consumed, and wait events. |