Identifying Currently Running Slow Queries Using System Tables

SYSTEM.CONNECTIONS

NuoDB also provides a mechanism for realtime query analysis. SYSTEM.CONNECTIONS is another NuoDB Pseudo System Table. This will report all currently running queries on your database. When the queries shown above from QUERYSTATS were still executing, they would have been 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

Connections Columns

Column Description
SQLString SQL query string
Count Number of times the statement executed
Runtime Elapsed run time in microseconds of the transaction
User User running the statement
Schema Schema against which the statement is running
NumParam Number of parameters in the statement
Params Information about each parameter including location, type and value
ConnID Connection ID number
Open Number of open statements for the connection
Handle Internal connection ID
OpenResults Number of open result sets for the current connection
NodeID Internal ID for TE for this connection
ExecID Execution ID that can be used for KILL STATEMENT command
TransID The identifier for the transaction being run on the connection. This value can be used to query SYSTEM.TRANSACTIONS with "WHERE id = this_value".
TransRunTime Execution run time in microseconds of the transaction
AutoCommitFlags Indicator for the value of the Autocommit system property setting when the statement was executed. 1=ON, 0=OFF
IsolationLevel The isolation level defined for the connection when the statement was executed. See Transactions and Isolation Levels
ClientHost Specifies the IP address for the connecting client.
ClientProcessID Specifies the process ID (pid) for the connecting client.
ClientInfo Specifies client-supplied information for the connecting client.
AutoCommitSPMode  
RollBackMode