Example for Querying for Top SQLs

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SET CURRENT_SCHEMA = tpcds;
EXPLAIN WITH customer_total_return AS
( SELECT sr_customer_sk as ctr_customer_sk,
sr_store_sk as ctr_store_sk, 
sum(SR_FEE) as ctr_total_return 
FROM store_returns, date_dim
WHERE sr_returned_date_sk = d_date_sk AND d_year =2000
GROUP BY sr_customer_sk, sr_store_sk )
SELECT  c_customer_id
FROM customer_total_return ctr1, store, customer
WHERE ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 
FROM customer_total_return ctr2
WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk) 
AND s_store_sk = ctr1.ctr_store_sk
AND s_state = 'TN'
AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
limit 100;

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, set the value of resource_track_cost to 100, which should be lower than the estimated cost in the EXPLAIN query result. For more details, 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.

1

SELECT query,max_peak_memory,average_peak_memory,memory_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;

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.

1

SELECT query,start_time,finish_time,duration,status FROM gs_wlm_session_history ORDER BY start_time desc;

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.

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:

1

SELECT query,start_time,finish_time,duration,status FROM gs_wlm_session_info ORDER BY start_time desc;