This topic describes how to use SQL Diagnoser from the following aspects: diagnose tenant SQL statements, review SQL statements, and stop SQL Diagnoser.
Tenant-level SQL diagnostics
On the homepage of SQL Diagnoser, you can use this feature to directly analyze the SQL statements executed in a business cluster to find common suspicious SQL statements. This feature helps you identify hidden performance issues and provides optimization suggestions. The following table describes the fields that you can specify to use this feature.
| Field | Description | Required? |
|---|---|---|
| IP Address | The IP address of the business cluster. | Yes |
| Port | The port number of the business cluster. | Yes |
| Username | The username of the tenant. | Yes |
| Tenant Password | The logon password of the tenant. | Yes |
| Cluster Name | The name of the cluster. If you connect to the cluster by using OceanBase Database Proxy (ODP), you must specify this field. Otherwise, you cannot connect to the cluster. | No |
| Start Time | Specifies to diagnose the SQL execution records generated since the specified point in time. | Yes |
| Samples | The number of records to be retrieved from the sql_audit view. The default value is 10000. |
No |
| End Time | Specifies to diagnose the SQL execution records generated until the specified point in time. If the diagnostic speed is low, we recommend that you narrow down the query range and try again. | Yes |
| Response Time | The threshold of the response time, in μs. Only the records of SQL statements whose response time exceeds this threshold are diagnosed. The default value is 20000 μs. When the diagnostic API runs slowly, you can increase this threshold. | No |
Notice
- No unit is specified for the time attributes in the diagnostic rules and results. Their units are the same as the original units in
gv$ob_sql_audit(orgv$sql_auditin OceanBase Database V4.0 and earlier). For example, the unit ofelapsed_timeis μs, and the CPU time is calculated by using the following formula:execution_time + get_plan_time - total_wait_time_micro. Therefore, the unit of the CPU time is also μs. - When you start SQL Diagnoser for the first time, it will create a folder named
h2and related data files in the home directory of the current user. If these files are deleted, the diagnostic rules will be reset. To persist the modifications on the diagnostic rules, do not move, modify, or delete these files. - By default, a default database named `oceanbase` is created after OceanBase Database is deployed. This database will be skipped by SQL Diagnoser. Do not test the features of SQL Diagnoser in this database.
- To reduce the pressure of diagnostics on the sys tenant and the load of SQL Diagnoser, a maximum of 10,000 rows (sorted in descending order of response time) are retrieved from
gv$ob_sql_audit(orgv$sql_auditin OceanBase Database V4.0 and earlier) for diagnostics each time. Therefore, you cannot diagnose all the data at a time. To adjust the amount of data for diagnostics, we recommend that you specify the maximum number of rows returned (queryLimit) by calling the corresponding API operation or set the environment variablesql.diagnose.sql.query-sql-audit-limitwhen you start SQL Diagnoser. For example, you can use the following configuration:nohup java -Dsql.diagnose.sql.query-sql-audit-limit=12345 -Dserver.port=9090 -jar sql-diagnoser-4.2.0.0.jar &.
Review SQL statements
You can use the SQL review feature on the GUI by simply entering the SQL statement that you want to review. Then the feature analyzes the SQL statement based on common diagnostic items and provides suggestions for improvement. The following table describes the fields that you can specify to use this feature.
| Field | Description | Required? |
|---|---|---|
| Endpoint | The IP address of the business cluster. | Yes |
| Port | The port number of the business cluster. | Yes |
| Username | The username of the tenant. | Yes |
| Tenant Password | The logon password of the tenant. | Yes |
| SQL text box | Enter the SQL statement that you want to review. | Yes |
The SQL review feature checks the SQL statement that you entered in the SQL text box based on static rules. It also connects to OceanBase Database to query some internal views. Therefore, you must enter the connection information of OceanBase Database. The SQL statement is not executed during review, thus ensuring security.
Stop SQL Diagnoser
You can stop SQL Diagnoser by ending its process.
- Run
ps -ef | grep sql-diagnoserto view the process ID of SQL Diagnoser. - Run
kill -9 process IDto end the process.