Help Center/ GaussDB/ Centralized_2.x/ Performance Tuning/ SQL Optimization/ Typical SQL Optimization Methods/ Optimizing SQL Self-Diagnosis
Updated on 2023-10-23 GMT+08:00
View PDF

Optimizing SQL Self-Diagnosis

Performance issues may occur when you query data or run the INSERT, DELETE, UPDATE, or CREATE TABLE AS statement. In this case, you can query the warning column in the PG_CONTROL_GROUP_CONFIG and GS_SESSION_MEMORY_DETAIL views to obtain reference for performance optimization.

Alarms that can trigger SQL self diagnosis depend on the settings of resource_track_level. If resource_track_level is set to query, alarms about the failures in collecting column statistics and pushing down SQL statements will trigger the diagnosis. If resource_track_level is set to operator, all alarms will trigger the diagnosis.

Whether a SQL plan will be diagnosed depends on the settings of resource_track_cost. A SQL plan will be diagnosed only if its execution cost is greater than resource_track_cost. You can use the EXPLAIN keyword to check the plan execution cost.

The SQL self-diagnosis function is affected by the enable_analyze_check parameter. Ensure that the function is enabled before using it.

If a large number of statements are executed, certain data may fail to be collected due to memory control. In this case, you can increase the value of instr_unique_sql_count.

Alarms

Currently, performance alarms will be reported when statistics about one or multiple columns are not collected.

An alarm will be reported if some column statistics are not collected. The current feature is a lab feature. Contact Huawei technical support before using it. For details about the optimization, see Updating Statistics and Optimizing Statistics.

Example alarms:

No statistics about a table are not collected.

Statistic Not Collect:
    schema_test.t1

The statistics about a single column are not collected.

Statistic Not Collect:
    schema_test.t2(c1,c2)

The statistics about multiple columns are not collected.

Statistic Not Collect:
    schema_test.t3((c1,c2))

The statistics about a single column and multiple columns are not collected.

Statistic Not Collect:
    schema_test.t4(c1,c2)    schema_test.t4((c1,c2))

Restrictions

  1. An alarm contains a maximum of 2048 characters. If the length of an alarm exceeds this value (for example, a large number of long table names and column names are displayed in the alarm when their statistics are not collected), a warning instead of an alarm will be reported.
    WARNING, "Planner issue report is truncated, the rest of planner issues will be skipped"
  2. If a query statement contains the Limit operator, alarms of operators lower than Limit will not be reported.