Long running queries

{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, 
       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;
Example output:
 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}

Have more questions? Submit a request

Comments