Obtaining SQL Elapsed Time Report

A useful tool in debugging slow queries is nuosql. This allows you to turn on or off the reporting of elapsed time for each SQL statement being executed. Timing can be turned on with the command line option --timer [ on | off | full ] as in the following example:

$ nuosql test --user dba --password goalie --timer full
SQL> select * from hockey.scoring limit 3000;

 PLAYERID  YEAR  STINT  TEAMID  POSITION  GAMESPLAYED  GOALS  ASSISTS  PENALTYMINUTES
 --------- ----- ------ ------- --------- ------------ ------ -------- ---------------

 aaltoan01 1997    1      ANA      C            3         0       0            0
 aaltoan01 1998    1      ANA      C           73         3       5           24
...
 bladoto01 1972    1      PHI      D           78        11      31           26
 bladoto01 1973    1      PHI      D           70        12      22           37

Elapsed time 36ms
Server execution time 7ms
SQL>

The off mode is default. The on mode reports the elapsed time taken by the nuosql client. The full mode reports the elapsed time taken by the nuosql client and additionally for non-DDL SQL statements, reports the elapsed time spent by the server (TE) to process the command.

Timing can also be turned on and off with the nuosql SET command. See SET.

SET TIMING { ON | OFF | FULL }