gp_log_command_timings

posted Jan 20, 2014, 1:52 PM by Sachchida Ojha
gpadmin=# \d gp_toolkit.gp_log_command_timings
      View "gp_toolkit.gp_log_command_timings"
   Column    |           Type           | Modifiers 
-------------+--------------------------+-----------
 logsession  | text                     | 
 logcmdcount | text                     | 
 logdatabase | text                     | 
 loguser     | text                     | 
 logpid      | text                     | 
 logtimemin  | timestamp with time zone | 
 logtimemax  | timestamp with time zone | 
 logduration | interval                 | 
View definition:
 SELECT __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logdatabase, __gp_log_master_ext.loguser, __gp_log_master_ext.logpid, min(__gp_log_master_ext.logtime) AS logtimemin, max(__gp_log_master_ext.logtime) AS logtimemax, max(__gp_log_master_ext.logtime) - min(__gp_log_master_ext.logtime) AS logduration
   FROM ONLY gp_toolkit.__gp_log_master_ext
  WHERE __gp_log_master_ext.logsession IS NOT NULL AND __gp_log_master_ext.logcmdcount IS NOT NULL AND __gp_log_master_ext.logdatabase IS NOT NULL
  GROUP BY __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logdatabase, __gp_log_master_ext.loguser, __gp_log_master_ext.logpid;

logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logdatabase=>The name of the database.
loguser=>The name of the database user.
logpid=>The process id (prefixed with "p").
logtimemin=>The time of the first log message for this command.
logtimemax=>The time of the last log message for this command.
logduration=>Statement duration from start to end time.

This view uses an external table to read the log files on the master and report the execution time of SQL commands executed in a database session. The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_command_timings;
logsession | logcmdcount | logdatabase | loguser | logpid | logtimemin | logtimemax | logduration 
------------+-------------+-------------+---------+--------+-------------------------------+-------------------------------+-----------------
con7 | cmd2702 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:44.045136-05 | 2014-01-18 02:16:44.045136-05 | 00:00:00
con7 | cmd989 | sachi | gpadmin | p10699 | 2013-12-12 12:17:59.834766-05 | 2013-12-12 12:17:59.834766-05 | 00:00:00
con7 | cmd413 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.856334-05 | 2014-01-18 02:16:42.856334-05 | 00:00:00
con7 | cmd2734 | gpadmin | gpadmin | p30158 | 2014-01-18 02:23:55.498894-05 | 2014-01-18 02:23:55.498894-05 | 00:00:00
con7 | cmd1546 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.459633-05 | 2014-01-18 02:16:43.459633-05 | 00:00:00
con12 | cmd31 | sachi | gpadmin | p10110 | 2013-11-29 08:40:27.041448-05 | 2013-11-29 08:40:27.07172-05 | 00:00:00.030272
con7 | cmd2742 | gpadmin | gpadmin | p30158 | 2014-01-19 19:32:48.948112-05 | 2014-01-19 19:32:48.948112-05 | 00:00:00
con7 | cmd1817 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.603791-05 | 2014-01-18 02:16:43.603791-05 | 00:00:00
con7 | cmd1136 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.249678-05 | 2014-01-18 02:16:43.249678-05 | 00:00:00
con7 | cmd985 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.179408-05 | 2014-01-18 02:16:43.179408-05 | 00:00:00
con7 | cmd3900 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.318356-05 | 2014-01-20 14:55:51.318356-05 | 00:00:00
con7 | cmd4539 | gpadmin | gpadmin | p30158 | 2014-01-20 14:58:13.253164-05 | 2014-01-20 14:58:13.253164-05 | 00:00:00
con9 | cmd99 | postgres | gpadmin | p18553 | 2013-11-27 12:54:23.82547-05 | 2013-11-27 12:54:23.82547-05 | 00:00:00
con7 | cmd4353 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:42.753756-05 | 2014-01-20 14:56:42.753756-05 | 00:00:00
con7 | cmd4104 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.832069-05 | 2014-01-20 14:56:14.832069-05 | 00:00:00
con7 | cmd469 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.88348-05 | 2014-01-18 02:16:42.88348-05 | 00:00:00
con8 | cmd4 | template1 | gpadmin | p18422 | 2013-11-27 12:54:22.644063-05 | 2013-11-27 12:54:22.644063-05 | 00:00:00
con7 | cmd2615 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.990813-05 | 2014-01-18 02:16:43.990813-05 | 00:00:00
con7 | cmd3716 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:49.594669-05 | 2014-01-20 14:55:49.594669-05 | 00:00:00
con7 | cmd182 | sachi | gpadmin | p10699 | 2013-12-12 11:32:29.719597-05 | 2013-12-12 11:32:29.719597-05 | 00:00:00
con7 | cmd4161 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.884239-05 | 2014-01-20 14:56:14.884239-05 | 00:00:00
con7 | cmd4108 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.839272-05 | 2014-01-20 14:56:14.839272-05 | 00:00:00
con7 | cmd3306 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:03.666139-05 | 2014-01-20 14:55:03.666139-05 | 00:00:00
con7 | cmd2040 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.702362-05 | 2014-01-18 02:16:43.702362-05 | 00:00:00
con7 | cmd665 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.003611-05 | 2014-01-18 02:16:43.003611-05 | 00:00:00
con11 | cmd2 | template1 | gpadmin | p3977 | 2013-11-28 11:19:39.516423-05 | 2013-11-28 11:19:39.865829-05 | 00:00:00.349406
con7 | cmd762 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.520531-05 | 2013-12-12 12:17:28.520531-05 | 00:00:00
con7 | cmd2563 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.963477-05 | 2014-01-18 02:16:43.963477-05 | 00:00:00
con7 | cmd1146 | sachi | gpadmin | p10699 | 2013-12-12 12:17:59.926756-05 | 2013-12-12 12:17:59.926756-05 | 00:00:00
con7 | cmd322 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.266479-05 | 2013-12-12 12:16:36.266479-05 | 00:00:00
con7 | cmd67 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.649847-05 | 2014-01-18 02:16:42.649847-05 | 00:00:00
con7 | cmd66 | sachi | gpadmin | p10699 | 2013-12-12 11:32:29.641545-05 | 2013-12-12 11:32:29.641545-05 | 00:00:00
con7 | cmd372 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.286566-05 | 2013-12-12 12:16:36.286566-05 | 00:00:00
con7 | cmd639 | sachi | gpadmin | p10699 | 2013-12-12 12:17:25.565363-05 | 2013-12-12 12:17:25.565363-05 | 00:00:00
con7 | cmd729 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.504424-05 | 2013-12-12 12:17:28.504424-05 | 00:00:00
con7 | cmd344 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.274324-05 | 2013-12-12 12:16:36.274324-05 | 00:00:00
con7 | cmd3269 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:03.65272-05 | 2014-01-20 14:55:03.65272-05 | 00:00:00
con7 | cmd5 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.534074-05 | 2014-01-18 02:16:42.534074-05 | 00:00:00
con7 | cmd775 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.524123-05 | 2013-12-12 12:17:28.524123-05 | 00:00:00
con11 | cmd20 | template1 | gpadmin | p3977 | 2013-11-28 11:19:40.355456-05 | 2013-11-28 11:19:40.384454-05 | 00:00:00.028998
con7 | cmd1741 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.56654-05 | 2014-01-18 02:16:43.56654-05 | 00:00:00
con7 | cmd707 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.022648-05 | 2014-01-18 02:16:43.022648-05 | 00:00:00
con7 | cmd4448 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:42.789864-05 | 2014-01-20 14:56:42.789864-05 | 00:00:00
con12 | cmd1 | sachi | gpadmin | p10110 | 2013-11-29 08:40:26.132038-05 | 2013-11-29 08:40:26.132038-05 | 00:00:00
con7 | cmd1228 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.294588-05 | 2014-01-18 02:16:43.294588-05 | 00:00:00
....
con7 | cmd711 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.49396-05 | 2013-12-12 12:17:28.49396-05 | 00:00:00
con7 | cmd322 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.814124-05 | 2014-01-18 02:16:42.814124-05 | 00:00:00
con7 | cmd3627 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:49.538092-05 | 2014-01-20 14:55:49.538092-05 | 00:00:00
con7 | cmd3895 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.315797-05 | 2014-01-20 14:55:51.315797-05 | 00:00:00
con7 | cmd1026 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.195032-05 | 2014-01-18 02:16:43.195032-05 | 00:00:00
con7 | cmd4012 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.353741-05 | 2014-01-20 14:55:51.353741-05 | 00:00:00
con7 | cmd875 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.111861-05 | 2014-01-18 02:16:43.111861-05 | 00:00:00
con7 | cmd1961 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.665132-05 | 2014-01-18 02:16:43.665132-05 | 00:00:00
con7 | cmd348 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.275108-05 | 2013-12-12 12:16:36.275108-05 | 00:00:00
con7 | cmd446 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.314844-05 | 2013-12-12 12:16:36.314844-05 | 00:00:00
con7 | cmd2611 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.99029-05 | 2014-01-18 02:16:43.99029-05 | 00:00:00
con7 | cmd267 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.790117-05 | 2014-01-18 02:16:42.790117-05 | 00:00:00
con7 | cmd3564 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:07.075346-05 | 2014-01-20 14:55:07.075346-05 | 00:00:00
con9 | cmd41 | postgres | gpadmin | p18553 | 2013-11-27 12:54:23.616744-05 | 2013-11-27 12:54:23.616744-05 | 00:00:00
con7 | cmd2842 | gpadmin | gpadmin | p30158 | 2014-01-19 20:37:23.957804-05 | 2014-01-19 20:37:23.957804-05 | 00:00:00
con7 | cmd561 | sachi | gpadmin | p10699 | 2013-12-12 12:17:25.533968-05 | 2013-12-12 12:17:25.533968-05 | 00:00:00
(3232 rows)
Comments