Identifying and Killing Long-Running Queries

Symptom

There is a running query you need to kill.

Identify the Query to be Killed

To identify a query that needs to be killed, locate the EXECID of the query from the SYSTEM.CONNECTIONS table. For example:

SQL> SELECT * FROM SYSTEM.CONNECTIONS;


         EXECID         NODEID  CONNID  USER  OPEN  HANDLE  COUNT  RUNTIME         STATEMENT

 ---------------------- ------- ------- ----- ----- ------- ------ -------- -------------------------

 1715547198872168169473    4       93    TEST     1      1       1  18141410  select * from bigtable;

In this example, you should kill “select * from bigtable” because it seems to be hanging or is taking too long.

You can use the KILL STATEMENT command and you can reference this statement in two ways, either with the EXECID or with the tuple of CONNID, HANDLE, and COUNT.

Solution

To kill a running query:

Open a second nuosql window. This assumes that the first nuosql window is locked by the query you want to kill.
Run SELECT * FROM system.connection;
Locate the EXECID value for the query you want to kill.

Invoke KILL STATEMENT. For example, either of the following:

SQL> KILL STATEMENT 1715547198872168169473l
SQL> KILL STATEMENT connid 93 handle 1 count 1;