Help Center/ Data Warehouse Service / User Guide/ DWS Cluster O&M/ Viewing GaussDB(DWS) Cluster Monitoring Information on the Monitoring Panel (DMS)/ Diagnosing SQL Statements of DWS
Updated on 2025-09-25 GMT+08:00
View PDF
Share

Diagnosing SQL Statements of DWS

DWS provides a tool for diagnosing SQL query statements. This tool provides alarm information and execution plan analysis for the statements, and identifies bottlenecks in statement execution. It can help you optimize SQL query performance, improve database query efficiency, reduce system resource consumption, and improve the response speed.

Prerequisites

To enable SQL diagnosis, choose Monitoring Settings. On the Monitoring Collection page, enable Historical Query Monitoring. For details, see Monitoring Collection.

Viewing SQL Diagnosis

  1. Log in to the DWS console.
  2. Choose Dedicated Clusters > Clusters and locate the cluster to be monitored.
  3. In the Operation column of the target cluster, click Monitoring Panel.
  4. In the navigation pane on the left, choose Utilities > SQL Diagnosis.
  5. On the SQL Diagnosis page, you can view the SQL diagnosis information. For details about the metrics, see Table 1.

    Table 1 SQL diagnosis metrics

    Metric Name

    Description

    Query ID

    Internal query_ID used for statement execution

    Database

    Database name

    Schema Name

    Name of the schema

    Username

    Username

    Client

    Name of the application that is connected to the backend

    Client IP Address

    IP address of the client connected to the backend. If this column is null, it indicates that the client is connected via a Unix socket on the server machine or that it is an internal process, such as autovacuum.

    Running Time (ms)

    Execution time of the statement, in milliseconds

    CPU Time (ms)

    Total CPU time of a statement across all DNs, in milliseconds

    Start Time

    Time when the statement starts to be executed

    Completion Time

    End time of statement execution

    Details

    Click View to view the detailed diagnosis result of the SQL statement. The result contains the following information:

    • Alarm information about the SQL statement
    • SQL statement
    • Execution plan of the SQL statement

Setting GUC Parameters

GUC parameters related to SQL diagnosis are as follows. For details, see "GUC Parameters" in the Data Warehouse Service Developer Guide.

Table 2 GUC parameters related to SQL diagnosis

GUC Parameter Name

Value Range

Default Value

Expected DMS Value

Function

enable_resource_track

Boolean

on

on (for reference only)

Specifies whether to enable real-time resource monitoring.

If this parameter is enabled without other GUC-related parameters correctly configured, real-time resource consumption cannot be recorded.

resource_track_cost

Integer, ranging from -1 to INT_MAX

0

0 (for reference only)

Specifies the minimum execution cost of statement resource monitoring for the current session. This parameter is valid only when enable_resource_track is on.

If this parameter is set to a small value, more statements will be recorded, causing record expansion and affecting cluster performance.

resource_track_level

Enumeration

query

query (for reference only)

Specifies the resource monitoring level for the current session. This parameter is valid only when enable_resource_track is on.

If the resource monitoring is set to operator-level, performance will be greatly affected.

resource_track_duration

Integer, ranging from 0 to INT_MAX, in seconds

60

0 (for reference only)

Specifies the minimum statement execution time that determines whether information about jobs of a statement recorded in the real-time view will be dumped to a historical view after the statement is executed. That is, only statements whose execution time exceeds the specified time are recorded in the historical view. This parameter is valid only when enable_resource_track is on.

If this parameter is set to a small value, the batch processing mechanism for dumping kernel statements becomes invalid, affecting the kernel performance.

topsql_retention_time

The value is an integer ranging from 0 to 3650. The unit is day.

30

14 (for reference only)

Specifies the aging time of pgxc_wlm_session_info data in the view.

If this parameter is set to 0, data will not be aged, which will cause storage expansion.

enable_resource_record

Boolean

on

on (for reference only)

Specifies whether to enable the archiving function for resource monitoring records. When this function is enabled, records in the history views (GS_WLM_SESSION_HISTORY and GS_WLM_OPERATOR_HISTORY) are archived to the info views (GS_WLM_SESSION_INFO and GS_WLM_OPERATOR_INFO) every 3 minutes. After the archiving, records in the history views are deleted.

When this parameter is enabled, you are advised to set topsql_retention_time properly to configure the aging time. Otherwise, data in the GS_WLM_SESSION_INFO or GS_WLM_OPERATOR_INFO table will expand.

FAQs

Symptom

The execution of SQL statements takes a long time, resulting in great resource consumption.

Troubleshooting

If the execution efficiency of SQL statements is low, optimization suggestions are provided after the kernel executes the SQL statements. You can query the execution history to retrieve optimization suggestions and further optimize SQL statements to improve query efficiency.

Procedure

  1. On the SQL Diagnosis page, select a time period that does not seem right.
  2. Search for SQL statements based on indicators such as the start time, end time, and running duration of the statement.
  3. Click View to view SQL optimization suggestions.
  4. Optimize the SQL statement based on suggestions.
Parent Topic: Viewing GaussDB(DWS) Cluster Monitoring Information on the Monitoring Panel (DMS)