LOCALQUERYPERFORMANCEMETRICS System Table Description

Description

The LOCALQUERYPERFORMANCEMETRICS pseudo table shows metrics for all previous cached statements executed on this client’s connection to a given Transaction Engine (TE) within the current time period. The metrics are available only immediately after a statement is executed.

All statements are normalized by ignoring query parameters. The top metrics, which consist of statements that take the longest to execute, are written to the sql-statement-metrics logging category with the WARN logging level and logged to nuoadmin.log by default. The table is cleared periodically and does not persist indefinitely. By default, this occurs at the start of every hour.

There are three configurable Database Options to manage this table:

Database Option Description

max-stmt-metrics-size

The maximum size of the table or the maximum number of unique normalized statement.
Default value is 10,000.

stmt-metrics-log-count

The number of top statements logged during each logging cycle.
Default value is 10.

stmt-metrics-log-frequency

The frequency at which the metrics are logged as the table is cleared.
Default value is 1h (hour).

Fields

Field Type Description

STATEMENT

string

Normalized SQL query string

COUNT

bigint

Number of times the statement was executed

MINEXECTIME

bigint

Minimum execution time in microseconds

MAXEXECTIME

bigint

Maximum execution time in microseconds

AVGEXECTIME

bigint

Average execution time in microseconds

TOTALEXECTIME

bigint

Total execution time in microseconds

MINCOMPILETIME

bigint

Minimum compile time in microseconds

MAXCOMPILETIME

bigint

Maximum compile time in microseconds

AVGCOMPILETIME

bigint

Average compile time in microseconds

TOTALCOMPILETIME

bigint

Total compile time in microseconds

RECORDSFETCHEDATMINTIME

bigInt

The total number of records fetched from tables and indices at minimum execution time

RECORDSFETCHEDATMAXTIME

bigInt

The total number of records fetched from tables and indices at maximum execution time

A null compile time indicates that a query was not compiled in the current time period but was retrieved from the compiled query cache.

Example

SELECT statement, count, avgexectime, avgcompiletime FROM system.localqueryperformancemetrics;
                STATEMENT                 COUNT  AVGEXECTIME  AVGCOMPILETIME
 ---------------------------------------- ------ ------------ ---------------

 SELECT COUNT(*) FROM T1 WHERE (F1) > (?)    3        559           893
 SELECT * FROM T1 WHERE (F2) = (?)           3        266          1059
 SELECT F1 FROM T1 WHERE (F2) = (?)          2        402           943