Vertica Metadata & data dictionary related queries for Query Execution, Queries Issued, Query phases & delete vectors.

Vertica Metadata Queries (Part 2)

Like anyone other RDBMS, Vertica also provides a large number of metadata tables which can be used for various purpose. In this tutorial, we will discuss how to check query execution time (duration), list of queries executed and delete vectors (non purged data) accumulated. These are some of the use cases to get this info.

→ Queries Executed: Finding all the queries executed by the user.

SELECT time,user_name,transaction_id,request_type,request FROM dc_requests_issued 
WHERE request_type='QUERY' and time >sysdate-1/24;

→ Query Execution Time: Checking execution time of the queries.

• Execution time of the queries for a session.

  
  SELECT * FROM query_requests  WHERE request_type = 'QUERY' AND session_id='[session_id]';
  

• Execution time of the queries for a particular query.

  
  SELECT * FROM query_requests  WHERE request_type = 'QUERY' AND REQUEST LIKE '% [QUERY TEXT] %';
  

→ Duration of each query phase: To check the time taken for every query phase

SELECT execution_step, completion_time-time FROM dc_query_executions WHERE transaction_id=<> and statement_id=<>;

List of some of the query phase

select distinct execution_Step from dc_query_executions;

Output:
  InitPlan
  AbandonPlan
  SerializePlan
  ExecutePlan
  CompilePlan
  CompilePlan:EEpreexecute
  CompilePlan:ReserveResources
  Plan
  PopulateVirtualProjection
  PreparePlan
  PreparePlan:DeserializePlan
  PreparePlan:DistPlanner
  PreparePlan:EEcompile
  PreparePlan:LocalPlan
  PreparePlan:TakeTableLocks

→ Delete Vectors: Vertica don't delete the data immediately but mark those rows for deletion and those deleted rows are called 'Delete Vectors'. These delete vectors consumes resources, therefore a large number of these containers can impact performance, especially during recovery. These delete vectors can be seen using below query.

SELECT node_name, schema_name, projection_name, COUNT(*) num_dv, SUM(deleted_row_count) delete_row_cnt, MIN(start_epoch) min_epoch, MAX(start_epoch) max_epoch 
FROM delete_vectors GROUP BY 1,2,3 ORDER BY 4 DESC;