{question}
Can you help me identify long-running SQL queries and incorrect statistics?
{question}
{answer}
To find long-running queries, query the system.connections, system.transactions, and system.nodes table:
SELECT c.USER,
c.SCHEMA,
c.clienthost,
c.clientinfo,
Substr(c.sqlstring, 0, 60) AS "SQL Statement (partial)",
c.runtime / 1000000 AS "runtime (seconds)",
c.transruntime / 1000000 AS "transruntime(seconds)",
c.transid AS "TransId",
n.hostname,
CASE
WHEN t.blockedby = -1 THEN NULL
ELSE t.blockedby
END AS "BlockedBy TransId"
FROM system.connections c
INNER JOIN (SELECT DISTINCT id,
nodeid,
sourcenode,
blockedby
FROM system.transactions) t
ON t.id = c.transid
AND t.nodeid = c.nodeid
AND t.sourcenode = c.nodeid
INNER JOIN system.nodes n
ON n.id = c.nodeid
WHERE ( c.runtime / 1000000 ) >= {# of Seconds considered a long query}
ORDER BY runtime DESC;
Example output:
USER SCHEMA CLIENTHOST CLIENTINFO SQL Statement (partial) runtime (seconds) transruntime(seconds) TransId HOSTNAME BlockedBy TransId ----- ------- ----------- ----------- ---------------------------- ------------------ ---------------------- -------- --------- ------------------ DBA USER 172.22.0.2 nuosql select distinct(f1) from t1; 20 20 4197506 test-te-1 <null>
To find a table with incorrect statistics, query the system.indexstatistics table:
SELECT i.SCHEMA,Example output:
i.tablename,
i.indexname,
i.statstype,
i.keycount,
i.valid,
i.maxkeysize,
i.minkey,
i.maxkey
FROM system.indexstatistics i
WHERE i.valid = 0
AND i.SCHEMA <> 'SYSTEM'
ORDER BY i.SCHEMA,
i.tablename,
i.indexname;
SCHEMA TABLENAME INDEXNAME STATSTYPE KEYCOUNT VALID MAXKEYSIZE MINKEY MAXKEY ------- ---------- ---------- ---------- --------- ------ ----------- ------- ------- USER T1 I1 Unknown <null> FALSE 1024 'NULL' 'NULL'
The above output shows invalid statistics for one index. Those statistics can be manually calculated based on all available record versions by using ANALYZE {TABLE | INDEX}.
ANALYZE INDEX USER.I1;
For more information, please check ANALYZE and INDEXSTATISTICS system table.
{answer}
Comments