{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}
Comments