Top SQL statements refer to the SQL statements that take the longest amount time to execute. On the Tenant Overview page, you can filter top SQL statements by time range and OBserver, and view the details of each top SQL statement in the list.
Procedure
In the left-side navigation pane, click Tenants and click a tenant in the Tenants list to go to the Overview page of the tenant.
In the left-side navigation pane, click SQL Diagnosis . The SQL Diagnosis page appears.
The SQL diagnostic data is not displayed on the SQL Diagnosis page if you do not set the values of both the cluster parameter enable_sql_audit and the tenant parameter ob_enable_sql_audit to True. You can click Change Cluster Parameters in the prompt box to modify the parameter values.
Click the TopSQL tab.
Filter the top SQL statements.
Specify the filter conditions.
Time Range: You can select the last 5 minutes, last 10 minutes, last 20 minutes, last 30 minutes, last 1 hour, and last 3 hours from the drop-down list of the Duration . You can also select Custom Time from the drop-down list and specify the start time and end time as needed. By default, the information of the last 30 minutes is displayed.
OBServer: You can select an OBServer or All OBServers in the list. If you select an OBServer, only SQL statements executed on the selected OBServer are queried.
Internal SQL: If you select this option, the SQL statements internally initiated by OceanBase Database are displayed in the query result.
Keywords: The SQL statements that contain the specified keywords are displayed in the query result.
Advanced Search: You can select SQL ID, Executions, and Executions per Second from the drop-down list, and specify the values. The SQL statements matching the metric values are displayed in the query result.
Click Search to list all the SQL statements that meet the filter conditions.
Click Export TopSQL to export all the SQL statements in the query result.
View TopSQL information.
Click Column Management . In the dialog box that appears, select the columns to display. Then, you can view the selected information in the TopSQL list.
On the TopSQL tab, you can view the selected columns that you want to display. You can also copy the SQL text, filter it by database and user, and sort it by the number of executions, total response time, response time, and CPU time.
You can click the SQL text to go to corresponding SQL text page.
On the SQL Details page, you can view the following SQL statement details:
In the SQL Text section, you can view the text of the SQL statement.
On the Previous Tendency tab, you can view the historical trends of the SQL statement. For more information, see View the historical trends of an SQL statement.
On the Execution Plans tab, you can view the execution plans of the SQL statement, or bind an execution plan to the statement. For more information, see View the execution plan of an SQL statement.
On the Index tab, you can view the indexes bound to the SQL statement. For more information, see View and bind indexes.
On the SQL Throttling tab, you can view or set throttling of the SQL statement. For more information, see Set SQL throttling.
You can view the binding records of the SQL statement in section ① as illustrated on the Execution Plans , Index , and SQL Throttling tabs.
In a binding record, you can view the status of the bound execution plan, click Unbind to unbind the plan from the SQL statement, and click Bind Plan to bind the plan to the SQL statement again.