You are here: SQL Development > Improving Query Performance > Identifying Historical Slow Queries Using System Tables

Identifying Historical Slow Queries Using System Tables

SYSTEM.QUERYSTATS

To know which of your finished queries took an excessive amount of time, examine the logs in the SYSTEM.QUERYSTATS table. The SYSTEM.QUERYSTATS table is also referred to as the Slow Query Log. By default, NuoDB stores the ten slowest queries that took more than ten seconds to execute. This can be modified by changing MAX_QUERY_COUNT and MIN_QUERY_TIME values in SYSTEM.PROPERTIES. Use the SET command (see SET) to change the default settings.

To find your slowest queries, run a simple select from SYSTEM.QUERYSTATS. (We use SET OUTPUT VERTICAL here to make the output more readable.)

SET OUTPUT VERTICAL;
SELECT * FROM system.querystats;
==================================== Row #1 ====================================
SQLSTRING: select * from t where x=?;
COUNT: 1
RUNTIME: 13010424
USER: CLOUD
SCHEMA: USER
NUMPARAM: 1
PARAMS: 0/string/2
NODEID: 1
NROWS: 1
UNIQUEID: 4
TIMESTAMP: 2013-04-23 12:29:53.512637
EXPLAIN:
==================================== 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: 
NODEID: 2
NROWS: 1
UNIQUEID: 2
TIMESTAMP: 2013-04-23 12:52:54.830913
EXPLAIN:

This will give you the SQL string for the query, the number of times the query has executed, the execution time for the query in microseconds (exaggerated in this example for illustration purposes), information about the user, schema and any parameters as well as other information. TIMESTAMP is the time at which the query started. SYSTEM.QUERYSTATS is a Pseudo System Table.

Because SYSTEM.QUERYSTATS is a Pseudo System Table, when a NuoDB transaction engine (TE) shuts down, the slow queries that were executed against that TE will no longer be reported in the QUERYSTATS table.

QUERYSTATS Columns

Column Description
SQLString SQL query string
Count Number of times the query executed
Runtime Execution time in microseconds; this is the last execution time and not an average
User User who executed the query
Schema Schema against which the query was run
NumParam Number of parameters in the query
Params Information about each parameter including location, type and value
NodeID Internal ID for TE for this query
NRows Number of rows returned by the query
Timestamp When the query started
Explain Shows the execution plan of the executed statement See EXPLAIN.