OAS provides the following background tasks:
Exception analysis: When the system detects an exception, it automatically analyzes the related SQL statements.
SQL routine inspection: Regularly inspects SQL statements in the cluster to identify suspicious SQL statements.
When OAS identifies the SQL statements related to an exception or detects deteriorating SQL execution plans during routine inspections, it can automatically perform the following actions:
Automatic refresh of Plan Cache: Clears the execution plan cache for the SQL statements, prompting the optimizer to generate a new execution plan.
Automatic outline binding: Binds, to the deteriorated execution plan, a historically used execution plan with lower CPU time based on performance statistics of the SQL statement's historical execution plans.
Procedure
Log in to the OceanBase Cloud console.
In the left-side navigation pane, click Instances.
In the instance list, find the target instance, click the instance name, and go to the Overview page of the instance.
Click Diagnostics in the left-side navigation pane, and then select the Root Cause Diagnostics tab on the Diagnostics page.
On the right side of the Root Cause Diagnostics tab, click Autonomy Settings.
In the pop-up window, turn on the feature switch and make the following settings:
Automatically refresh PlanCache: Set the enabled state and execution timing.
Automatically bind Outline: Set the activation status and execution timing.
Set non-effective objects (optional): Set databases, tenants, or SQL blacklists, and the system will automatically ignore these objects during self-healing.
Notification settings: Configure the Webhook address for DingTalk group notifications as follows:
Create a bot in the DingTalk group and copy the Webhook address link. For instructions on how to obtain it, see Get Custom Bot Webhook Address.
Enter the link in the text box, click Verify, and OAS will send a verification code to the DingTalk group.
Enter the verification code to complete the verification.
Instructions
After enabling the system autonomy feature, you can query the records of automatic refresh and automatic binding of execution plans in Diagnosis > Real-time Diagnosis > View Optimization Records > Optimization History. The operation source of the relevant records will be: System Autonomy.- Currently, the self-healing function only supports SELECT-type SQL.
Automatically Refresh PlanCache
During SQL inspection: Only automatically refresh the PlanCache for SQL with deteriorated execution plans.
During root cause analysis: Automatically refresh the PlanCache for all SQL statements associated with the abnormal event.
Automatic Binding Outline
Pre-binding check:
The system will attempt to refresh the execution plan of the SQL and observe the newly generated execution plan.
If the CPU time of the new execution plan is 20% lower than that of the historical execution plan, it is considered successfully self-healed, and the Outline will no longer be bound.
If a better execution plan is not generated, perform the binding operation and send an alert notification in the DingTalk group mentioned above.
Binding followed by observation:
After the binding is complete, the system will continue to monitor the execution plan of this SQL.
- If no better execution plan is generated after binding, the binding operation will be rolled back, and an alert notification will be sent to the DingTalk group mentioned above.
Precautions:
Automatically bind Outline based on performance statistics of historical execution plans, and bind SQL with deteriorated execution plans to historical execution plans with lower CPU time.
In the scenario of large and small accounts, the binding operation does not necessarily guarantee the improvement of SQL performance. Please pay close attention to the self-healing alert notifications and confirm the binding effect in time.
For clusters with kernel SPM (SQL Plan Management) enabled, it is recommended not to enable automatic binding of Outlines to avoid conflicts with the SPM function.