LOCALQUERYPERFORMANCEMETRICS System Table Description
Description
The LOCALQUERYPERFORMANCEMETRICS
pseudo table shows metrics for all previous 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 |
---|---|
|
The maximum size of the table or the maximum number of unique normalized statement. |
|
The number of top statements logged during each logging cycle. |
|
The frequency at which the metrics are logged as the table is cleared. |
|
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 |
Example
SELECT statement, count, avgexectime, avgcompiletime FROM system.localqueryperformancemetrics;
STATEMENT COUNT AVGEXECTIME AVGCOMPILETIME
---------------------------------------- ------ ------------ ---------------
CREATE TABLE T1 (F1 INTEGER, F2 STRING) 2 12915 229
INSERT INTO T1 VALUES (?, ?) 23 212 787
SELECT COUNT(*) FROM T1 WHERE (F1) > (?) 3 559 893
UPDATE T1 SET F1 = ? WHERE (F2) = (?) 2 409 1263
SELECT * FROM T1 WHERE (F2) = (?) 3 266 1059
DROP TABLE T1 1 10826 160
CREATE INDEX IDX1 ON T1 (F1) 1 8065 222
SELECT F1 FROM T1 WHERE (F2) = (?) 2 402 943