Description
This alert is triggered when a suspicious SQL statement with degraded performance is detected.
Principle
| Parameter | Value |
|---|---|
| Metric | None |
| Source | None |
| Collected metric | None |
| Metric expression | None |
| Collection cycle | None |
Alert rule
The CPU time of the SQL statement is longer than the average CPU time of the SQL statement in the last 1 hour or 24 hours. The alerting criteria are the same as the diagnostic criteria for the diagnostic result Deteriorated performance on the Suspicious SQL tab.
Alert information
| Trigger method | Alert level | Scope |
|---|---|---|
| Based on monitored conditions | Warning | Tenant |
Alert templates
Details
Template:
The execution performance of the SQL statement ${sql_id} in the ${db_name} database of the ${tenant_name} tenant of the ${ob_cluster_name} cluster has degraded during the period from ${start_time} to ${end_time}. The average CPU time is ${sql_cpu_time_ms} ms. The average response time is ${sql_elapsed_time_ms} ms. The baseline period is from ${baseline_start_time} to ${baseline_end_time}. During the baseline period, the average CPU time is ${sql_baseline_cpu_time_ms} ms, and the average response time is ${sql_baseline_elapsed_time_ms} ms. Recommended action: [Refresh Plan Cache].Example:
Overview: alarm_template_id=0:ob_cluster=admin-4:tenant_name=s**:db_name=oceanbase:sql_id=C38F5CFF605DC65C6FFD9ED72026****:recommend_operations=FLUSHING_PLAN_CACHE. It is detected during the SQL inspection that the execution performance of the SQL statement has degraded. Period: 2023-09-08T14:24:17 to 2023-09-08T14:28:47 Associated SQL statement: SQL ID: C38F5CFF605DC65C6FFD9ED72026****. Database: oceanbase. Tenant: s**. Hit rule: The execution performance degrades. Basic information of the SQL statement: The average CPU time is 1.78 ms. The average response time is 1.78 ms. Baseline period: 2023-09-08T14:04:17 to 2023-09-08T14:23:47 Information about the baseline SQL statements: The average CPU time is 1.39 ms. The average response time is 1.39 ms. Recommended action: [Refresh Plan Cache].
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
- The SQL statement is poorly or frequently executed.
- The volume of business data increases or the leader role is switched.
- The SQL execution plan has changed.
Solutions
- Refresh the plan cache. To do so, you can execute the alert clearance plan. For more information, see Execute the alert clearance plan.
- Optimize the SQL statement.
