Analyzing the Status of a Query Statement ​

Symptom ​

Some query statements are executed for an excessively long time in the system. You need to analyze the status of the query statements.

Procedure ​

1. Log in to the host as the OS user omm.

2. Run the following command to connect to the database:

   gsql -d postgres -p 8000

   postgres is the name of the database, and 8000 is the port number.

3. Set the parameter track_activities to on.

   SET track_activities = on;

   The database collects the running information about active queries only if the parameter is set to on.

4. View the running query statements. The pg_stat_activity view is used as an example here.

   SELECT datname, usename, state, query FROM pg_stat_activity; 
   datname  | usename | state  | query 
   ----------+---------+--------+-------
   postgres | omm     | idle   | 
   postgres | omm     | active | 
   (2 rows)

   If the state column is idle, the connection is idle and requires a user to enter a command. To identify only active query statements, run the following command:

   SELECT datname, usename, state, query FROM pg_stat_activity WHERE state != 'idle';

5. Analyze whether a query statement is in the active or blocked state. Run the following command to view a query statement in the block state:

   SELECT datname, usename, state, query FROM pg_stat_activity WHERE waiting = true;

   The query statement is displayed. It is requesting a lock resource that may be held by another session, and is waiting for the lock resource to be released by the session.