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 ;
- 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