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.
Troubleshooting method
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.
If you want to ignore this alert, you can disable the alert push configuration or reduce the alert push frequency.
Disable the alert push configuration: Specify
"alarm":falsein the OCP system parameterocp.perf.sql-diag.performance-degradation-after-plan-changed-config. If the parameter does not contain thealarmattribute, you can manually add it. Make sure that the modified parameter is in the JSON format.Reduce the alert push frequency: Specify
"cpuTimeLimitMs":1.0in the OCP system parameterocp.perf.sql-diag.performance-degradation-after-plan-changed-config. If the parameter does not contain thecpuTimeLimitMsattribute, you can manually add it and set its value based on your business needs. Make sure that the modified parameter is in the JSON format.
For more information about the
ocp.perf.sql-diag.performance-degradation-after-plan-changed-configparameter, see Parameters related to SQL performance diagnostics.