• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Resource Load Management
  5. Resource Monitoring
  6. Real-Time Top SQLs

Real-Time Top SQLs

When a database user performs a query job, the system provides real-time resource monitoring views at the query- and operator-levels to query SQL queries in the Active state for the real-time Top SQLs whose execution cost is greater than resource_track_cost. The views record the resource usage (including memory, disk, CPU time, and I/O) and performance alarms during job execution. Based on the recorded information, you can evaluate whether the query has performance bottlenecks and whether it affects the cluster performance.

By default, query-level resource monitoring is enabled. To enable resource monitoring at both the query and operator levels, set resource_track_level to operator. The following jobs support real-time resource monitoring:

  • Jobs whose execution cost estimated by the optimizer is greater than or equal to resource_track_cost. You can run the EXPLAIN statement to query for the execution cost of a statement.
  • Jobs of the SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE AS, EXPLAIN ANALYZE, and EXPLAIN PERFORMANCE statements. A query-level view also supports the FETCH statement. A query-level view also supports the FETCH statement.

The real-time resource monitoring view is described in the following table. You can query such a view in the same way as querying database tables. Common users can only query views with the prefix gs.

Table 1 Real-time resource monitoring view

Monitoring Level

Monitored Node

View

View Description

Query

Current CN

GS_WLM_SESSION_STATISTICS

Queries for the real-time resources on the current CN.

All CNs

PGXC_WLM_SESSION_STATISTICS

Queries for the real-time resources on all CNs.

Operator

Current CN

GS_WLM_OPERATOR_STATISTICS

Queries for the real-time resources of job operators on the current CN.

All CNs

PGXC_WLM_OPERATOR_STATISTICS

Queries for the real-time resources of job operators on all CNs.

Typical Queries for Real-Time Top SQLs

The query for statements executed on the current CN is used as an example to describe some typical SQL statements used for querying for Top SQLs in terms of real-time resource usage and performance. To query for the SQL statements executed on all CNs, replace the prefix gs of the view name in the query statement with pgxc.
  • Query level. By default, the first column in each query statement provided in the example is query (that is, the SQL statement to be queried).
    • Query for the start time, end time, execution time, and execution status of the SQL statement. The time unit is ms.
      SELECT query,start_time,finish_time,duration,status FROM gs_wlm_session_history ORDER BY start_time DESC;
    • Query for the maximum memory peak of the SQL statement among all DNs, average memory usage during statement execution, and the memory usage skew ratio across DNs. The memory unit is MB.
      SELECT query,max_peak_memory,average_peak_memory,memory_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
    • Query for the spill of the SQL statement to each DN, maximum data spill volume among all DNs, average data spill volume among all DNs, and spill skew ratio across DNs. The data volume unit is MB.
      SELECT query,spill_info,max_spill_size,average_spill_size,spill_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
    • Query for the maximum execution time of the SQL statements among all DNs and the execution time skew ratio across DNs. The time unit is ms.
      SELECT query,max_dn_time,dntime_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
    • Query for the maximum CPU time of the SQL statement among all DNs, the total CPU time on all DNs, and the CPU time skew across DNs. The time unit is ms.
      SELECT query,max_cpu_time,total_cpu_time,cpu_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
    • Query for the maximum IOPS peak of the SQL statement among all DNs, the average IOPS peak among all DNs, and the I/O skew ratio across DNs. The I/Os are counted by ones for column storage and by 10 thousands for row storage.
      SELECT query,max_peak_iops,average_peak_iops,iops_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
  • Operator level. By default, the first column in each query statement provided in the example is plan_node_name (that is, the operator name matching plan_node_id).
    • Query for the start time for the operator to process data, total data processing duration (unit: ms) of the operator, and operator execution status.
      SELECT plan_node_name,start_time,duration,status FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
    • Query for the maximum memory peak of the operator among all DNs, average memory peak among all DNs, and memory usage skew ratio across DNs. The memory unit is MB.
      SELECT plan_node_name,max_peak_memory,average_peak_memory,memory_skew_percent FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
    • Query for the maximum data spill volume of the operator among all DNs, average data spill volume among all DNs, and spill skew ratio across DNs. The data volume unit is MB.
      SELECT plan_node_name,max_spill_size,average_spill_size,spill_skew_percent FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
    • Query for the maximum execution time of the operator among all DNs, total execution time on all DNs, and execution time skew ratio across DNs. The time unit is ms.
      SELECT plan_node_name,max_cpu_time,total_cpu_time,cpu_skew_percent FROM gs_wlm_operator_statistics ORDER BY start_time DESC;

Precautions

  • Statistics are not collected for jobs in a redistribution process.
  • Prefixes gs and pgxc indicate real-time views showing single CN information and those showing cluster information, respectively. Common users can query only the views with the prefix gs.
  • When you query views in Table 1, network latency may be caused by the views to obtain real-time resource usage.
  • If you only need to query for the real-time CPU information, use the GS_SESSION_CPU_STATISTICS view; if you only need to query for the real-time memory information, use the GS_SESSION_MEMORY_STATISTICS view. The GS_WLM_SESSION_STATISTICS view returns a query result containing both GS_SESSION_CPU_STATISTICS and GS_SESSION_MEMORY_STATISTICS.