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