Long completed queries or Slow query

{question}

How to find which queries had a long execution time in NuoDB?

{question}

{answer}

By example, the following 'set' statements will configure NuoDB to capture the top longest running 200 SQL statements that took longer than 2 seconds elapsed time into the system.querystats table. 

set system property min_query_time = 2;
set system property max_query_count = 200;

The following query will display the SQLs that match the filters set above ordered by most recently executed first.

select DATE_TO_STR(timestamp,'MM.dd.yyyy'' at ''HH:mm:ss') timestamp,
round(runtime/1000000,3) "Elapse Time", nrows "Rows Returned", nodeid "Node",
user, schema, substr(sqlstring,1,60) "SQL"
from system.querystats
order by timestamp desc;

The SQL output will display,


TIMESTAMP              Elapse Time   Rows Returned Node  USER  SCHEMA    SQL 
 --------------------- ------------ -------------- ----- ----- --------- ------------------------------------------------------------

10/23/2017 at 21:47:05        2.03           20211     2   DBA employees select e.emp_no, e.first_name || ' ' || e.last_name employee 
10/23/2017 at 21:40:25       2.324          134094     2   DBA employees select e.emp_no, e.first_name || ' ' || e.last_name employee 
10/23/2017 at 21:40:16       2.563          134094     2   DBA employees select e.emp_no, e.first_name || ' ' || e.last_name employee 
10/23/2017 at 21:40:09       2.082           20211     2   DBA employees select e.emp_no, e.first_name || ' ' || e.last_name employee 
10/23/2017 at 21:39:37        2.59          134094     2   DBA employees select e.emp_no, e.first_name || ' ' || e.last_name employee 
10/23/2017 at 21:39:28       2.426          134094     2   DBA employees select e.emp_no, e.first_name || ' ' || e.last_name employee

You can optionally add the EXPLAIN column to the SQL statement column list to display the SQL statement EXPLAIN plan output for further analysis.

To empty the the system.querystats table and start collection again, run: 

set system property max_query_count = 0;
set system property max_query_count = 200;

A database restart will also clear the system.querystats table.

More information and examples can be found here.

{answer}

Have more questions? Submit a request

Comments