Some Useful System Queries

1. To see all the queries currently running:

vsql=> select user_name, session_id, transaction_id, statement_id, request, request_duration_ms, start_timestamp from query_requests where is_executing='t'  order by start_timestamp desc;


"query_requests" is a system view built over few system tables, but this is only available in Vertica 6.0 and above. To get this output in older versions:

vsql=> select user_name, session_id, transaction_id, statement_id, query,query_duration_us, query_start from query_profiles where is_executing='t';




2. To get the run time for a particular query:

vsql=> select user_name, session_id, transaction_id, statement_id, request, request_duration_ms, start_timestamp from query_requests where transaction_id=< transaction id > and statement_id= < statement id >;

Or we can filter on particular request(s):

vsql=> select user_name, session_id, transaction_id, statement_id, request, request_duration_ms, start_timestamp from query_requests where request ilike '%my_table%' order by start_timestamp desc limit 10;



Corresponding queries for version 5.x:

vsql=> select ri.time as Start_Time, rc.time as End_Time,rc.time - ri.time as Query_Duration, ri.request as Query from dc_requests_issued ri, dc_requests_completed rc where ri.session_id = rc.session_id and ri.request_id = rc.request_id and ri.transaction_id=< transaction id > and ri.statement_id=< statement id >;

vsql=> select ri.time as Start_Time, rc.time as End_Time,rc.time - ri.time as Query_Duration, ri.request as Query from dc_requests_issued ri, dc_requests_completed rc where ri.session_id = rc.session_id and ri.request_id = rc.request_id and ri.request like '%my_table%' order by ri.time desc limit 10;



3. To see the resources acquired by running queries:

vsql=> select transaction_id, statement_id, thread_count, open_file_handle_count, memory_inuse_kb/1024 memory_inuse_mb from resource_acquisitions where is_executing='t' limit 10;



4. To see the run time of various execution engine operators:

vsql=> select operator_name, avg(counter_value) as Avg_Time_Per_Counter from dc_execution_engine_profiles where transaction_id= < transaction id > and statement_id= < statement id > and counter_name='execution time (us)' group by operator_name order by avg(counter_value) desc;

Once we have the run time values for all the operators, we can drill down a particular operator to see the individual counter values.

vsql=> select counter_name, avg(counter_value) as Avg_Counter_Value from dc_execution_engine_profiles where transaction_id= < transaction id > and statement_id= <statement id > and operator_name='Join' group by counter_name order by avg(counter_value) desc;



5. To see the delete row counts schema wise and projection wise:

vsql=> select schema_name, sum(total_row_count) AS Total_Row_Count, sum(deleted_row_count) AS Deleted_Row_Count FROM storage_containers group by schema_name order by sum(deleted_row_count) desc;

vsql=> select schema_name,projection_name,storage_type,sum(deleted_row_count) as Deleted_Row_Count  from delete_vectors group by schema_name,projection_name,storage_type order by rows desc;



6. To see all the locks in the database and the associated session.

vsql=> select object_name as Object, lock_mode as Lock_Mode, lock_scope as Lock_Scope, session_id, substring(current_statement, 1, 30) || '...' as Statement from v_monitor.sessions s join v_monitor.locks l on to_hex(s.transaction_id) = substring(l.transaction_description, 6, 14);



7. To find the ROS container count for projection:

vsql=> select projection_name, node_name, count(distinct storage_oid) as ROS_Container_Count from storage_containers where projection_name= < projection name > group by 1,2 order by 1,2;