Find last query executed by specific session in Vertica database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    Vertica enables you to quick check last query executed by each session.

    Below are two queries: basic query and query with queries details .

    Basic Query

    Query

    select session_id,
           node_name,
           user_name,
           last_statement,
           last_statement_duration_us
    from v_monitor.sessions
    order by user_name;
    

    Columns

    • session_id - process ID of chosen session
    • node_name - name of the node session is connected to
    • user_name - name of the user logged into this session
    • last_statement - most recent query
    • last_statement_duration_us - duration of the query in microseconds

    Rows

    • One row: represents one sessions
    • Scope of rows: represents all sessions with their last executed query
    • Ordered by: username

    Sample results

    sample results

    Query with details

    Query

    select s.session_id,
           s.node_name,
           s.user_name,
           q.request_type,
           q.request,
           success,
           start_timestamp,
           end_timestamp,
           request_duration_ms as duration_ms
    from v_monitor.sessions s
    left join v_monitor.query_requests q
              on s.session_id = q.session_id
    where s.session_id = 'session_id'   -- put sessiond id here
    order by q.start_timestamp desc;
    

    Columns

    • session_id - process ID of chosen session
    • node_name - name of the node this backend is connected to
    • user_name - name of the user logged into this backend
    • request_type - type of executed statement
      • QUERY
      • DDL
      • LOAD
      • UTILITY
      • TRANSACTION
      • PREPARE
      • EXECUTE
      • SET
      • SHOW
    • request - query statement
    • success - flag indicating whether query run successfully
    • start_timestamp - timestamp when query was started
    • end_timestamp - timestamp when query was ended
    • duration_ms - duration of query in miliseconds

    Rows

    • One row: represents one query
    • Scope of rows: represents last queries executed by specific session
    • Ordered by: query start time descending

    Sample results

    sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept