Identifying Currently Running Slow Queries Using System Tables

SYSTEM.CONNECTIONS

NuoDB also provides a mechanism for real-time query analysis. SYSTEM.CONNECTIONS is another NuoDB pseudo system table. This will report all currently running queries on your database. When the queries shown from QUERYSTATS are still executing, they are reported in the CONNECTIONS table as follows:

SET OUTPUT VERTICAL;
SELECT sqlstring, count, runtime, user, schema, numparam, params, connid,
    open, handle, nodeid, execid FROM system.connections;
==================================== Row #1 ====================================
SQLSTRING: select * from t where x=?;
COUNT: 1
RUNTIME: 13010424
USER: CLOUD
SCHEMA: USER
NUMPARAM: 1
PARAMS: 0/string/2
CONNID: 2
OPEN: 1
HANDLE: 3
NODEID: 1
EXECID: 55340232229718589441
==================================== Row #2 ====================================
SQLSTRING: select s from t2 join t3 on t2.s=t3.s;
COUNT: 1
RUNTIME: 14013823
USER: CLOUD
SCHEMA: USER
NUMPARAM: 0
PARAMS:
CONNID: 3
OPEN: 1
HANDLE: 1
NODEID: 2
EXECID: 18446744086594453505

This gives similar information as the SYSTEM.QUERYSTATS table. It includes CONNID, OPEN, and HANDLE which in combination uniquely identifies each SQL statement and may be used as parameters to the KILL STATEMENT. EXECID is an identifier created from CONNID, OPEN and HANDLE, which also provides a unique way to identify your SQL statement and may be copied and pasted as an argument to the KILL STATEMENT.

All the Pseudo System Tables may be accessed by one or more columns, just like any other table. A helpful, simple query for analyzing currently running queries is:

SELECT sqlstring, runtime FROM system.connections;

              SQLSTRING                RUNTIME
-------------------------------------- --------
select * from t where x=?;             13010424
select s from t2 join t3 on t2.s=t3.s; 14013823

For more information, see CONNECTIONS System Table Description.