Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Resource Monitoring/ TopSQL Query Example
Updated on 2023-10-31 GMT+08:00
View PDF

TopSQL Query Example

In this section, TPC-DS sample data is used as an example to describe how to query Real-time Top SQL and Historical Top SQL.

Configuring Cluster Parameters

To query for historical or archived resource monitoring information about jobs of top SQLs, you need to set related GUC parameters first. The procedure is as follows:

  1. Log in to the GaussDB(DWS) management console.
  2. On the Cluster Management page, locate the required cluster and click the cluster name. The cluster details page is displayed.
  3. Click the Parameter Modifications tab to view the values of cluster parameters.
  4. Set an appropriate value for parameter resource_track_duration and click Save.

    If enable_resource_record is set to on, storage space expansion may occur and thereby slightly affects the performance. Therefore, set is to off if record archiving is unnecessary.

  5. Go back to the Cluster Management page, click the refresh button in the upper right corner, and wait until the cluster parameter settings are applied.

Example for Querying for Top SQLs

The TPC-DS sample data is used as an example.

  1. Open the SQL client tool and connect to your database.
  2. Run the EXPLAIN statement to query for the estimated cost of the SQL statement to be executed to determine whether resources of the SQL statement will be monitored.

    By default, only resources of a query whose execution cost is greater than the value of resource_track_cost are monitored and can be queried by users.

    For example, run the following statements to query for the estimated execution cost of the SQL statement:

    In the following query result, the value in the first row of the E-costs column is the estimated cost of the SQL statement.

    Figure 1 EXPLAIN result

    In this example, to demonstrate the resource monitoring function of top SQLs, you need to set resource_track_cost to a value smaller than the estimated cost in the EXPLAIN result, for example, 100. For details about the parameter setting, see resource_track_cost.

    After completing this example, you still need to reset resource_track_cost to its default value 100000 or a proper value. An overly small parameter value will compromise the database performance.

  3. Run SQL statements.

  4. During statement execution, query for the real-time memory peak information about the SQL statement on the current CN.

    The preceding command queries for the real-time peak information at the query-level. The peak information includes the maximum memory peak among all DNs per second, average memory peak among all DNs per second, and memory usage skew across DNs.

    For more examples of querying for the real-time resource monitoring information of top SQLs, see Real-time Top SQL.

  5. Wait until the SQL statement execution in 3 is complete, and then query for the historical resource monitoring information of the statement.

    The preceding command queries for the historical information at the query-level. The peak information includes the execution start time, execution duration (unit: ms), and execution status. The time unit is ms.

    For more examples of querying for the historical resource monitoring information of top SQLs, see Historical Top SQL.

  6. Wait for 3 minutes after the execution of the SQL statement in 3 is complete, query for the historical resource monitoring information of the statement in the info view.

    If enable_resource_record is set to on and the execution time of the SQL statement in 3 is no less than the value of resource_track_duration, historical information about the SQL statement will be archived to the gs_wlm_session_info view 3 minutes after the execution of the SQL statement is complete.

    The info view can be queried only when the postgres database is connected. Therefore, switch to the postgres database before running the following statement:

Parent topic: Resource Monitoring