Find used resources plus the used pool details of a single Vertica statement

This is used when you need to do some performance tuning or when you need to find more details about you running queries.

  • This will be our example query used to monitor it's resource usage.
  • As you can see a label will be used.

    select /*+ label (test_x)*/ * from query_requests order by start_timestamp desc limit 0;
  • Run query to find resource usage by the query labaled 'text_x'.
  • --next run the bellow script giving it the label name for the where clause.
    select request_label,
           RESOURCE_ACQUISITIONS.pool_name as "Pool Used",
           RESOURCE_POOLS.memorysize as "Max Mem Available",
           concat(to_char(sum(memory_inuse_kb) / 1048576, '999.999'), ' M') as "Mem Used",
           sum(duration_ms) as "Exexc Time in Milisec"
      from RESOURCE_ACQUISITIONS
     inner join QUERY_REQUESTS on RESOURCE_ACQUISITIONS.transaction_id =
                                  QUERY_REQUESTS.transaction_id
     inner join RESOURCE_POOLS on RESOURCE_POOLS.name =
                                  RESOURCE_ACQUISITIONS.pool_name
     where QUERY_REQUESTS.request_label = 'test_x'
     group by 1, 2, 3;