Help Center/ Data Warehouse Service/ Management Guide/ DWS Cluster O&M/ Common O&M Commands of DWS
Updated on 2026-10-13 GMT+08:00
View PDF

Common O&M Commands of DWS

This section lists only common SQL commands used for DWS cluster O&M, covering viewing O&M status, recovering from emergencies, and analyzing services. These commands help O&M personnel quickly locate and rectify faults. The system objects to be queried can be changed based on the site requirements. For details about the returned fields, see the description of system catalogs, system views, and system functions in the product documentationDeveloper Guide.

Prerequisites

You have connected to a DWS cluster.

Viewing O&M Status

The following commands are used for routine O&M and inspection and allow you to quickly learn about the status, resource usage, and potential bottlenecks of a cluster. You are advised to run the commands periodically to create baseline data.

  • View the overall status of the current service. 
    1
    		 
    SELECT coorname, usename, client_addr, sysdate-query_start AS duration, state, enqueue,waiting, pid, query_id, substr(query,1,60) FROM pgxc_stat_activity WHERE usename != 'Ruby' AND usename != 'omm' AND state = 'active' ORDER BY duration DESC;

    Parameter description: Obtains all active sessions (excluding sessions of internal accounts Ruby and omm) from the pgxc_stat_activity view and sorts the sessions in descending order of execution duration (the duration field indicates the execution duration of the SQL statement) to quickly locate time-consuming SQL statements.

  • View the overall concurrency of the current service. 
    1
    		 
    SELECT usename,coorname,enqueue,state,count(*) FROM pgxc_stat_activity WHERE usename <> 'omm' AND usename <> 'Ruby' GROUP BY 1,2,3,4 ORDER BY 4,5 DESC LIMIT 30;

    Parameter description: Collects the number of sessions by username, CN name, queuing status, and session status to learn the concurrency distribution. A maximum of 30 rows of results are allowed.

  • View the overall waiting status in the current cluster. 
    1
    		 
    SELECT wait_status,wait_event,count(*) AS cnt FROM pgxc_thread_wait_status WHERE wait_status <> 'wait cmd' AND wait_status <> 'synchronize quit' AND wait_status <> 'none' AND wait_status <> 'wait stream task' GROUP BY 1,2 ORDER BY 3 DESC LIMIT 50;

    Parameter description: Obtains the thread waiting status of each node in the cluster from the pgxc_thread_wait_status view, excluding the waiting statuses of idle threads (wait cmd, synchronize quit, none, and wait stream task). A maximum of 50 rows of results are allowed.

  • View the service running information of the resource pool in the current cluster (resource management and control scenario). 
    1
    		 
    SELECT s.resource_pool AS rpname, count(1) AS session_cnt,sum(CASE WHEN a.state = 'active' THEN 1 ELSE 0 END) AS active_cnt,sum(CASE WHEN s.enqueue ='global' THEN 1 ELSE 0 END) AS global_wait,sum(CASE WHEN s.lane = 'fast' AND s.status = 'running' THEN 1 ELSE 0 END) AS fast_run,sum(CASE WHEN s.lane = 'fast' AND s.status = 'pending' AND s.enqueue NOT IN ('global','none') THEN 1 ELSE 0 END) AS fast_wait,sum(CASE WHEN s.lane = 'slow' AND s.status = 'running' THEN 1 ELSE 0 END) AS slow_run,sum(CASE WHEN s.lane = 'slow' AND s.status = 'pending' AND s.enqueue NOT IN ('global','none') THEN 1 ELSE 0 END) AS slow_wait,sum(CASE WHEN s.status = 'running' THEN s.statement_mem ELSE 0 END) AS est_mem FROM pg_catalog.pgxc_session_wlmstat s,pg_catalog.pgxc_stat_activity a WHERE s.threadid=a.pid(+) AND s.attribute != 'internal' AND s.resource_pool != 'root' GROUP BY 1;

    Parameter description: Collects the number of sessions, number of active sessions, number of global waiting sessions, number of running and waiting sessions in the fast and slow lanes, and estimated memory usage of each resource pool from pgxc_session_wlmstat and pgxc_stat_activity.

    • s.threadid=a.pid(+): outer join, which retains all wlmstat records
    • The lane field distinguishes the fast lane from the slow lane.
  • View the dynamic memory watermark of the current cluster. 
    1
    		 
    SELECT a.nodename,a.memorymbytes AS dynamic_used_memory,b.memorymbytes AS max_dynamic_memory, dynamic_used_memory/max_dynamic_memory*100 AS used_rate  FROM pgxc_total_memory_detail a JOIN pgxc_total_memory_detail b ON a.nodename=b.nodename  WHERE a.memorytype = 'dynamic_used_memory' AND b.memorytype = 'max_dynamic_memory' ORDER BY a.memorymbytes DESC;

    Parameter description: Obtains the used and maximum dynamic memory of each node from the pgxc_total_memory_detail view for calculating the usage, and sorts the nodes by usage in descending order to quickly locate the nodes with a high memory watermark.

    • dynamic_used_memory: used dynamic memory
    • max_dynamic_memory: maximum dynamic memory
  • View the memory usage of different type of threads. 
    1
    		 
    SELECT b.state, sum(totalsize) AS totalsize, sum(freesize) AS freesize, sum(usedsize) AS usedsize FROM pv_session_memory_detail a , pg_stat_activity b WHERE split_part(a.sessid,'.',2) = b.pid GROUP BY b.state ORDER BY totalsize DESC LIMIT 20;

    Parameter description: Obtains the memory usage by connecting to a CN or DN with a high dynamic memory watermark found in View the dynamic memory watermark of the current cluster. During the query, pv_session_memory_detail and pg_stat_activity are associated. The memory usage is collected by session status and sorted in descending order of the total memory. A maximum of 20 rows are returned.

  • View the memory used by each session of the current instance. 
    1
    		 
    SELECT split_part(pv_session_memory_detail.sessid,'.',2) pid,pg_size_pretty(sum(totalsize)) total_size,count(*) context_count FROM pv_session_memory_detail GROUP BY pid ORDER BY sum(totalsize) DESC;

    Parameter description: Obtains the memory usage by connecting to a CN or DN with a high dynamic memory watermark found in View the dynamic memory watermark of the current cluster. During the query, the total memory size and the number of contexts are collected by group based on the session PID. The pg_size_pretty function is used to convert the result into a readable format.

  • View the memory used by each SQL statement of the current instance. 
    1
    		 
    SELECT sessid, contextname, level,parent, pg_size_pretty(totalsize) AS total ,pg_size_pretty(freesize) AS freesize, pg_size_pretty(usedsize) AS usedsize, datname,query_id, query FROM pv_session_memory_detail a , pg_stat_activity b WHERE split_part(a.sessid,'.',2) = b.pid ORDER BY totalsize DESC LIMIT 100;

    Parameter description: Obtains the memory usage by connecting to a CN or DN with a high dynamic memory watermark found in View the dynamic memory watermark of the current cluster. During the query, pv_session_memory_detail and pg_stat_activity are associated to obtain the memory context details of each SQL statement, including the session ID, context name, level, and memory size. A maximum of 100 rows are allowed.

Emergency Recovery

Confirm with the customer before performing any emergency operations that could affect services. Do not proceed without the customer's approval.

  • Terminate a statement. 
    1
2
    		 
    EXECUTE DIRECT ON(cn_name) 'SELECT pg_cancel_backend(PID of the statement to be terminated)';
EXECUTE DIRECT ON(cn_name) 'SELECT pg_terminate_backend(PID of the statement to be terminated)';

    Parameter description:

  • Combine termination statements in batches, without executing them. 
    1
    		 
    SELECT 'execute direct on(' || coorname || ') ''select pg_terminate_backend(' || pid || ')'';',sysdate-query_start AS dur, substr(query,1,60) FROM pgxc_stat_activity WHERE usename != 'omm' AND usename != 'Ruby' AND state = 'active' ORDER BY dur DESC LIMIT 30;

    Parameter description: Queries all active sessions and combines them to generate an EXECUTE DIRECT termination statement. You can review and then execute the statement. The results are sorted by execution duration in descending order, and a maximum of 30 rows are allowed.

  • Clear idle connections. 
    1
2
    		 
    CLEAN CONNECTION TO ALL FOR DATABASE databasename;
SELECT * FROM pgxc_clean_free_conn();

    Parameter description:

    • databasename: Replace it with the database name.
    • The first command clears idle connections of a specified database.
    • The second command clears the cache of the pooler connection pool.
  • Fix the CCN count by connecting to the CCN. 
    1
2
    		 
    SELECT * FROM pg_stat_get_workload_struct_info();
SELECT gs_wlm_node_recover(true);

    Parameter description:

    • pg_stat_get_workload_struct_info(): obtains the workload management structure information of the current CCN.
    • gs_wlm_node_recover(true): triggers CCN count fixing.
  • Lock abnormal users. 
    1
2
    		 
    ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;

    Parameter description: Replace username with a database username. The locked user cannot log in to the database. When unlocked, the user can log in to the database again.

  • Add a service to the blacklist. 
    1
2
3
    		 
    SELECT * FROM gs_append_blocklist(unique_sql_id);
SELECT * FROM gs_blocklist_query;
SELECT gs_remove_blocklist(unique_sql_id);

    Parameter description:

    • unique_sql_id: unique SQL statement ID, which can be obtained from the query_id field of pgxc_stat_activity or from the WLM view.
    • gs_append_blocklist: adds a specified SQL statement to the blacklist.
    • gs_blocklist_query: queries the existing blacklist.
    • gs_remove_blocklist: removes a specified SQL statement from the blacklist.

Service Analysis

The following commands are used to analyze the status of a specific SQL statement or table, including the wait events, running statistics, data skew, dirty page rate, and table definition. The information facilitates fault locating and performance optimization.

  • View the wait view of the SQL statement that is being executed. 
    1
    		 
    SELECT * FROM pgxc_thread_wait_status WHERE query_id = Actual query ID ORDER BY node_name,wait_status,wait_event;

    Parameter description: Views the wait events of a specified SQL statement on each node in the pgxc_thread_wait_status view. The events are sorted by node name, wait status, and wait event. You can obtain Actual query ID from the query_id field in View the overall status of the current service..

  • View the execution process of a running SQL statement. 
    1
    		 
    SELECT * FROM pgxc_wlm_session_statistics WHERE queryid = Actual query ID ;

    Parameter description: Views the execution process statistics of a specified SQL statement in the pgxc_wlm_session_statistics view. You can obtain the Actual query ID from the query_id field obtained in View the overall status of the current service.

  • View the execution information about historical SQL statements. 
    1
    		 
    SELECT * FROM pgxc_wlm_session_info WHERE queryid = Actual query ID;

    Parameter description: Views the historical information of a SQL statement that has been executed in the pgxc_wlm_session_info view, including the execution duration and memory usage. You can obtain the Actual query ID from the query_id field in View the overall status of the current service..

  • View the skew information of a single table. 
    1
    		 
    SELECT * FROM table_distribution('schema_name','table_name');

    Parameter description: Views the distribution of the data of a specified table on each DN using the table_distribution function. Replace schema_name and table_name with the actual schema name and table name, respectively.

  • View the dirty page rate of a single table. 
    1
    		 
    SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, CAST( (n_dead_tup / (n_live_tup + n_dead_tup + 0.0001) * 100) AS numeric(5,2)) AS dirty_page_rate FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.oid = 'schema_name.table_name'::regclass::oid;

    Parameter description: Associates pg_class and pg_namespace, collects the number of inserted, updated, and deleted tuples in a table, and calculates the dirty page rate (dirty_page_rate). If the dirty page rate exceeds 20%, you are advised to run VACUUM to clear the table. Replace schema_name.table_name with the actual table name.

  • View the table definition and index information. 
    1
    		 
    SELECT pg_get_tabledef('schema_name.table_name');

    Parameter description: Obtains the complete definition of a specified table using the pg_get_tabledef function, including column definitions, indexes, and constraints. Replace schema_name.table_name with the actual table name.

  • Obtain the table size. 
    1
    		 
    SELECT pg_size_pretty(pg_table_size('schema_name.table_name'));

    Parameter description: Obtains the physical size of a specified table using the pg_table_size function and converts the size to a readable format (such as KB, MB, or GB) using the pg_size_pretty function. Replace schema_name.table_name with the actual table name.

  • View the creation time, modification time, and last analysis time of a table. 
    1
    		 
    SELECT * FROM pg_object where object_oid='schema_name.table_name'::regclass;

    Parameter description: Queries the creation time, modification time, and last ANALYZE information collection time of a table from the pg_object view. Replace schema_name.table_name with the actual table name.

  • View details about dirty data. 
    1
2
3
4
5
6
7
    		 
    START TRANSACTION READ ONLY;
SET enable_show_any_tuples = true;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT ctid,xmin,xmax,pgxc_is_committed(xmin),pgxc_is_committed(xmax),oid,* FROM schema_name.table_name;
SELECT xmin,xmax,ctid, * FROM pgxc_node;
ROLLBACK;

    Parameter description: The preceding operations use read-only transactions to view table data. After the operations are complete, use ROLLBACK to perform a rollback without modifying data. Replace schema_name.table_name with the actual table name.

    • SET enable_show_any_tuples = true: All tuples (including dead tuples) are displayed.
    • xmin/xmax: transaction ID, which is used to determine the transaction status of a tuple
    • pgxc_is_committed(): specifies whether a transaction has been committed.

Documentation

Parent Topic: DWS Cluster O&M