SQL issues are the most common type of performance issues. OceanBase Cloud Platform (OCP) provides multi-dimensional SQL diagnostics and classifies SQL statements into suspicious SQL statements, top SQL statements, slow SQL statements, and parallel SQL statements based on their running characteristics. If an error occurs during the execution of an SQL statement, you can select a diagnostic type based on the scenario to analyze the error. You can also view and modify execution plans in SQL plans and outlines.
TopSQL diagnostics
The TopSQL diagnostics feature collects SQL statement execution statistics by SQL ID, displays historical execution trends of SQL statements, and allows you to sort SQL statements by different resource consumption metrics. TopSQL allows you to analyze these SQL statements to identify abnormal requests or optimize specific SQL statements.
Suspicious SQL diagnostics
The suspicious SQL diagnostics feature processes and analyzes the TopSQL statistics data in OCP based on built-in expert experience to identify suspicious SQL statements. The data source for SQL diagnostics is the same as that for TopSQL diagnostics. The base data of each diagnostic item is obtained from the MonitorDB metadata in OCP. OCP diagnoses the objects based on certain rules, such as the CPU time and execution frequency. The Suspicious SQL tab displays the SQL statements with deteriorated performance and modified execution plans. You must pay special attention to these SQL statements. In addition, the diagnostic results of a suspicious SQL statement contain possible causes of the performance deterioration, which provide guidance for SQL tuning.
SlowSQL diagnostics
In OCP, an SQL statement whose execution time exceeds the specified threshold, which is 100 ms by default, is considered a slow SQL statement. You can modify the threshold based on your business scenarios. A slow SQL statement has the following impacts:
- Long response time
- Large system resource consumption, even system unavailability in extreme cases
Therefore, to ensure system stability, you must collect and analyze SlowSQL statistics to identify issues and avoid risks in an early stage. The SlowSQL diagnostics feature focuses on the information about a single SQL execution. OCP samples data from v$sql_audit in OceanBase Database to allow you to analyze the resource consumption and execution details of slow SQL statements from various dimensions.
ParallelSQL diagnostics
A parallel SQL statement is an SQL statement executed through parallel scheduling. The ParallelSQL diagnostics feature identifies SQL statements that do not meet query performance expectations in analytical processing. In the SQL Execution Profile section, you can view the execution details of the SQL statement at the operator level in the specified time range.
Outline binding
After abnormal SQL statements are identified with the preceding SQL diagnostic features, you can use the index-based outline binding feature in OCP to modify improper SQL execution plans. The outline binding feature helps you solve issues in the following scenarios:
- The database optimizer has defects and may use incorrect execution plans for some scenarios. For example, the optimizer may generate improper execution plans based on cost computation on statistical information when a large amount of incremental data exists.
- When SQL request load is heavy on the database, user requests must be slowed down for the database.
SQL plans
On the SQL Diagnostics page, you can click an SQL statement on any tab to go to the SQL Details page. In addition to the detailed statement executed, the page also displays the history of SQL execution plans, including the following information:
- Historical trends of execution plans.
- List and details of execution plans.