Description
This alert is triggered if the CPU time of a historical execution plan of an SQL statement is shorter than that of the current execution plan. The alerting criteria are the same as the diagnostic criteria for the diagnostic result After the execution plan was modified, the performance of the SQL statement was deteriorated on the Suspicious SQL tab.
Principle
| Parameter | Value |
|---|---|
| Metric | None |
| Source | None |
| Collected metric | None |
| Metric expression | None |
| Collection cycle | None |
Alert rule
A historical execution plan is way better than the current execution plan.
Alert information
| Trigger method | Alert level | Scope |
|---|---|---|
| Based on monitored conditions | Warning | Tenant |
Alert templates
Details
Template:
The execution plan of the SQL statement ${sql_id} in the ${db_name} database of the ${tenant_name} tenant of the ${ob_cluster_name} cluster has deteriorated during the period from ${start_time} to ${end_time}. The hash value of the current execution plan is ${plan_hash_of_current_plan}. The hash value of the historical execution plan is ${plan_hash_of_recommend_plan}. The average CPU time of the current execution plan is ${sql_cpu_time_ms} ms. The average CPU time of the historical execution plan is ${sql_baseline_cpu_time_ms} ms. Recommended action: [Refresh Plan Cache] [Bind Execution Plan].Example:
Overview: alarm_template_id=0:ob_cluster=admin-4:tenant_name=a****:db_name=test:sql_id=6CE3107B0430F8CA5AAE8B5094EC****:recommend_operations=FLUSH_PLAN_CACHE,PLAN_BINDING. It is detected during the SQL inspection that the execution plan of the SQL statement has deteriorated. Period: 2023-08-31T15:22:47 to 2023-08-31T15:24:17 SQL statement: SQL ID: 6CE3107B0430F8CA5AAE8B5094EC****. Database: test. Tenant: a****. Hit rule: The execution plan deteriorates. Basic information of the SQL statement: The hash value of the current execution plan is -75141822074798****. The hash value of the historical execution plan is 708693211292835****. The average CPU time of the current execution plan is 278.38 ms. The average CPU time of the historical execution plan is 0.2 ms. Recommended action: [Bind Execution Plan].
Impact on the system
The response time of the SQL statement and the consumption of system resources such as CPU resources are affected.
Possible causes
Unrepresentative parameters are used when the SQL execution plan is generated. As a result, the SQL execution plan is changed and the overall SQL execution performance degrades, causing an increase of the CPU utilization.
Solutions
- Refresh the plan cache of the SQL statement to regenerate the SQL execution plan. To do so, you can execute the alert clearance plan. For more information, see Execute the alert clearance plan.
- If the first solution is ineffective, you can analyze the execution plan of the SQL statement and bind a historical execution plan or an index to the SQL statement.
