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;

    Copyright 2020. All Rights Reserved