Vertica Script to Monitor and Manage running Sessions

This is i think the script i most use in my day to day activities. I use it to monitor the sessions that are running on my server and also to keep an eye on what is going on. You will require elevated rights to run this script.

SELECT
    node_name ,
    user_name ,
    'SELECT CLOSE_SESSION(''' || session_id || ''');' AS CloseSession ,
    statement_start ,
    (GETDATE() - statement_start)::INTERVAL          AS current_statement_duration ,
    REGEXP_REPLACE(current_statement,'[\r\n\t]',' ') AS current_statement ,
    session_id ,
    transaction_id ,
    statement_id ,
    client_hostname ,
    client_os ,
    login_timestamp ,
    runtime_priority ,
    ssl_state ,
    authentication_method ,
    transaction_start ,
    GETDATE() AS Today
FROM
    v_monitor.sessions
ORDER BY
    current_statement_duration DESC ;
 The script will generate an output as the one below.
  • it will provide you with details of the running sessions such as Request, session_id and more others, but the most important for me the close_session output as this allows me kill what ever session i need.
dbadmin= \x
Expanded display is on.
dbadmin= SELECT
dbadmin-     node_name ,
dbadmin-     user_name ,
dbadmin-     'SELECT CLOSE_SESSION(''' || session_id || ''');' AS CloseSession ,
dbadmin-     statement_start ,
dbadmin-     (GETDATE() - statement_start)::INTERVAL          AS current_statement_duration ,
dbadmin-     REGEXP_REPLACE(current_statement,'[\r\n\t]',' ') AS current_statement ,
dbadmin-     session_id ,
dbadmin-     transaction_id ,
dbadmin-     statement_id ,
dbadmin-     client_hostname ,
dbadmin-     client_os ,
dbadmin-     login_timestamp ,
dbadmin-     runtime_priority ,
dbadmin-     ssl_state ,
dbadmin-     authentication_method ,
dbadmin-     transaction_start ,
dbadmin-     GETDATE() AS Today
dbadmin- FROM
dbadmin-     v_monitor.sessions
dbadmin- ORDER BY
dbadmin-     current_statement_duration DESC ;
-[ RECORD 1 ]
node_name                  | v_node0006
user_name                  | user_abc
CloseSession               | SELECT CLOSE_SESSION('v_node0006-382185:0x44625b');
statement_start            | 2016-07-12 13:09:27.781493+10
current_statement_duration | 01:29:12.139535
current_statement          |
session_id                 | v_node0006-382185:0x44625b
transaction_id             | -1
statement_id               |
client_hostname            | 172.31.224.242:51621
client_os                  |
login_timestamp            | 2016-07-12 13:09:25.66298+10
runtime_priority           |
ssl_state                  | None
authentication_method      | Password
transaction_start          | 2016-07-12 13:09:27.777583+10
Today                      | 2016-07-12 14:38:39.921028
Be careful with this script and make sure before running it in production you test it in dev and get used to its output.