Long running queries

{question}

Can you help me to assess the health of my NuoDB database?

{question}

{answer}

 To find Long Running Queries:

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;

 

To find table with incorrect statistics:

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;

{answer}

Have more questions? Submit a request

Comments