Display Last SQL Run Time: Quick Tip

Jim Knicely authored this post.

When the \timing meta-command in vsql is set to “on”, Vertica will return how long (in milliseconds) each SQL statement runs.

That’s great! But what if you forgot to turn it on prior to executing a long running query? Instead of turning it on then re-executing that long running query, simply check the CURRENT_SESSION system to get the run time of your previous SQL statement!

Example: dbadmin=> SELECT COUNT(*) FROM big_fact JOIN big_dim ON big_dim.c20 = big_fact.c20; COUNT ------------ 1073784457 (1 row) Oops! I forgot to turn on timing. That took a while to run, but how long??? This long… dbadmin=> SELECT last_statement, dbadmin-> last_statement_duration_us / 1000000.0 last_statement_duration_seconds dbadmin-> FROM current_session; last_statement | last_statement_duration_seconds ---------------------------------------------------------------------------+--------------------------------- SELECT COUNT(*) FROM big_fact JOIN big_dim ON big_dim.c20 = big_fact.c20; | 57.901827000000000 (1 row) Helpful links:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/CURRENT_SESSION.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands/timing.htm

Have fun!