oas_anomaly_sql_from_sql_inspection_plan_changed

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

Alert rule

A historical execution plan is way better than the current execution plan.

Alert information

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

1. 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.

2. 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.

3. 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":false in the OCP system parameter ocp.perf.sql-diag.performance-degradation-after-plan-changed-config. If the parameter does not contain the alarm attribute, you can manually add it. Make sure that the modified parameter is in the JSON format.

   • Reduce the alert push frequency: Specify "cpuTimeLimitMs":1.0 in the OCP system parameter ocp.perf.sql-diag.performance-degradation-after-plan-changed-config. If the parameter does not contain the cpuTimeLimitMs attribute, 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-config parameter, see Parameters related to SQL performance diagnostics.