find query by session_id

maks

Hello. Could you help me? I hava a vertica cluster (version 12) and I find high cpu queries in the last 6 hours with the following query

SELECT transaction_id,statement_id, cpu_cycles_us, duration_ms,session_id
FROM QUERY_CONSUMPTION
WHERE request_type='QUERY' AND
success AND
( NOW() - end_time) < '6 hour'
ORDER BY 3 DESC,4
LIMIT 10;

transaction_id   | statement_id | cpu_cycles_us | duration_ms |              session_id
-------------------+--------------+---------------+-------------+--------------------------------------
 45035996280482682 |            1 |     421230054 |       13720 | _statistics_node0001-279990:0x735a98
 45035996280493690 |            2 |       4927121 |        4329 | _statistics_node0001-279990:0x7387b0
 45035996280493690 |           10 |       2803748 |        4297 | _statistics_node0001-279990:0x7387b0
 45035996280493690 |            6 |       2753918 |        4707 | _statistics_node0001-279990:0x7387b0
 45035996280493690 |            1 |       2742957 |        4128 | _statistics_node0001-279990:0x7387b0
 45035996280493690 |            5 |       2733925 |        4485 | _statistics_node0001-279990:0x7387b0
 45035996280493690 |            3 |       2658386 |        4730 | _statistics_node0001-279990:0x7387b0
 45035996280493690 |            4 |       2618640 |        5745 | _statistics_node0001-279990:0x7387b0
 45035996280482181 |           12 |        758283 |         473 | _statistics_node0001-279990:0x73588c
 45035996280473610 |           12 |        756360 |         363 | _statistics_node0001-279990:0x7334ac
(10 rows)

Now I want to understand what kind of request is running, I take the first session_id from the output of the request above and paste it into the next request

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 = '_statistics_node0001-279990:0x735a98'   
order by q.start_timestamp desc;

 session_id | node_name | user_name | request_type | request | success | start_timestamp | end_timestamp | duration_ms
------------+-----------+-----------+--------------+---------+---------+-----------------+---------------+-------------
(0 rows)

But I am getting empty output. Why is that?

Bryan_H

v_monitor.sessions only lists open sessions. Closed sessions will be found in the history table "dc_session_starts". Also check "dc_session_ends" to find when and why the session closed.
Also check sessions table to see whether that is the correct session_id. I've not seen a node name starting with "", usually starts with "v".