Useful System Tables Related to Database Operation

The following NuoDB system tables are particularly helpful when monitoring database operation and troubleshooting problems. You can access these tables by executing the SQL SELECT command as you would for any other table. Your current schema must be set to SYSTEM or you must specify a fully-qualified name, as in SYSTEM.CONNECTIONS, when doing the query.

System Table Name Description

System Table Name	Description
CONNECTIONS	Information about all connections to the database that are open and may or may not be actively executing SQL. The `SYSTEM.CONNECTIONS` table provides real-time query analysis. It reports all current connections with associated running statements in the database. It provides a unique three part identifier made of the fields `CONNID`, `OPEN` and `HANDLE`, which you can specify with the `KILL` statement to kill a long running query. It also provides the `EXECID` field, which you can use with `KILL` statements. See examples, see Improving Query Performance. See also: CONNECTIONS System Table Description.
INDEXSTATISTICS	A view on what the optimizer uses in compiling the tables that changed. See INDEXSTATISTICS System Table Description.
LOCALCONNECTIONS	List of open connections for the Transaction Engine (TE) to which this SQL connection is connected. See LOCALCONNECTIONS System Table Description.
LOCALSTATEMENTS	List of SQL statements that are in the current statement cache. See LOCALSTATEMENTS System Table Description.
NODES	Lists the current processes/nodes (TEs and SMs) that make up the database. See NODES System Table Description.
QUERYSTATS	This table is commonly referred to as the slow query log because it stores the slowest queries. The `SYSTEM.CONNECTIONS` table provides historical query analysis.The number of queries stored is determined by the setting of the `MAX_QUERY_COUNT` field in the `SYSTEM.PROPERTIES` table.The length of execution time that qualifies a query to be stored is determined by the setting of the `MIN_QUERY_TIME` field, also in the `SYSTEM.PROPERTIES` table. A query that take longer than this length of time to complete is eligible to be recorded. Thus, the `QUERYSTATS` table records the most recent `MAX_QUERY_COUNT` queries that took longer than `MIN_QUERY_TIME`. You can use the `SET` command to modify the values of the `MAX_QUERY_COUNT` and `MIN_QUERY_TIME` properties. See Improving Query Performance for examples of using this table. For reference information, see QUERYSTATS System Table Description.

CONNECTIONS

Information about all connections to the database that are open and may or may not be actively executing SQL.

The SYSTEM.CONNECTIONS table provides real-time query analysis. It reports all current connections with associated running statements in the database. It provides a unique three part identifier made of the fields CONNID, OPEN and HANDLE, which you can specify with the KILL statement to kill a long running query. It also provides the EXECID field, which you can use with KILL statements.

See examples, see Improving Query Performance. See also: CONNECTIONS System Table Description.

INDEXSTATISTICS

A view on what the optimizer uses in compiling the tables that changed. See INDEXSTATISTICS System Table Description.

LOCALCONNECTIONS

List of open connections for the Transaction Engine (TE) to which this SQL connection is connected. See LOCALCONNECTIONS System Table Description.

LOCALSTATEMENTS

List of SQL statements that are in the current statement cache. See LOCALSTATEMENTS System Table Description.

NODES

Lists the current processes/nodes (TEs and SMs) that make up the database. See NODES System Table Description.

QUERYSTATS

This table is commonly referred to as the slow query log because it stores the slowest queries.

The SYSTEM.CONNECTIONS table provides historical query analysis.The number of queries stored is determined by the setting of the MAX_QUERY_COUNT field in the SYSTEM.PROPERTIES table.The length of execution time that qualifies a query to be stored is determined by the setting of the MIN_QUERY_TIME field, also in the SYSTEM.PROPERTIES table. A query that take longer than this length of time to complete is eligible to be recorded. Thus, the QUERYSTATS table records the most recent MAX_QUERY_COUNT queries that took longer than MIN_QUERY_TIME. You can use the SET command to modify the values of the MAX_QUERY_COUNT and MIN_QUERY_TIME properties.

See Improving Query Performance for examples of using this table. For reference information, see QUERYSTATS System Table Description.