When you suspect that plan degradation is caused by stale statistics, you can use the dialog diagnostic feature to check the statistics collection status for a specified tenant.
When to use
The execution plan for the same SQL statement has changed, and performance has significantly degraded.
The estimated row count in the execution plan differs significantly from the actual row count.
After bulk data import or deletion, you need to confirm whether statistics have been updated.
Prerequisites
You have configured an OAS connection for the product of the target cluster.
Procedure
Type
@in the session to select the target cluster and tenant.Describe your diagnostic request, for example:
Check if there are any issues with the statistics for this tenant.Press Enter to send the request and wait for the Agent to complete the diagnostic.
The Agent queries the following information to determine if there are risk tables with untimely updated statistics:
Query contentPurposeOverview of statistics collection Understand the overall collection coverage of the tenant. Task execution history Confirm whether the most recent collection task was successfully executed. Top N table update counts Identify tables with frequent data changes but stale statistics. Automatic collection configuration Check whether automatic collection is enabled and its time window configuration. Estimated row count deviation Compare the estimated row count from the execution plan with the actual row count to identify plan degradation caused by stale statistics. Partition-level statistics collection Confirm whether partition statistics and global statistics are consistent to avoid plan deviation after partition pruning. View the diagnostic conclusion.
The Agent lists the risk tables and provides suggestions on manual collection or adjusting the automatic collection configuration. For SQL statements found to have significant estimated row count deviations, it marks the corresponding tables and the deviation points in the execution plan.
Note
Manually collecting statistics consumes cluster resources. It is recommended to perform this during off-peak business hours and follow your enterprise change management process.
Row count skew and partition statistics consistency
When the execution efficiency of an SQL statement deteriorates significantly, and the estimated row count in the execution plan deviates greatly from the actual row count, you can directly target this SQL for targeted diagnostics:
SQL_ID xxx has seen a recent deterioration in plan quality. The estimated row count differs significantly from the actual row count. Please check if it's a statistics issue.
Are the global statistics and partition-level statistics of the partitioned table t_order consistent?
- Row count skew: The Agent compares the estimated
rowsvalue in the execution plan with the actualA-rowsvalue. It also determines whether the statistics are stale based on the number of table updates and generates a risk report along with collection recommendations. - Partition statistics consistency: The Agent verifies whether the partition-level statistics and global statistics were updated at the same time. If a significant deviation is detected, it indicates that partition statistics collection is lagging.
What to do next
After updating the statistics, re-execute the pre-analysis for the problematic SQL to confirm whether the execution plan has returned to normal. For more information, see SQL pre-analysis and tuning suggestions.