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

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 by ID.

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 Supported Transaction 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.

RollBackMode

Specifies rollback behavior for transactions executed inside a stored procedure, a UDF, or a trigger.