RESOURCE_ACQUISITIONS

Retains information about resources (memory, open file handles, threads) acquired by each running request for each resource pool in the system.

Column Name   Data Type   Description
NODE_NAME VARCHAR

Node name for which information is listed.
TRANSACTION_ID INTEGER

Transaction identifier for this request.
STATEMENT_ID INTEGER

Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
REQUEST_TYPE VARCHAR

Type of request issued to a resource pool. Request type can be one of:

  • Reserve: related to queries
  • Acquire: [Internal] related to the optimizer and other internal services, such as the Database Designer
  • Acquire additional: [Internal] related to size adjustment of acquisitions obtained through the first two methods; unusual, outside the WOS
POOL_ID INTEGER

A unique numeric ID assigned by the Vertica catalog, which identifies the resource pool. The ID represents the initial pool, even if the request has been moved to a new pool due to cascade. For more information about cascade events, see RESOURCE_POOL_MOVE.
POOL_NAME VARCHAR

Name of the resource pool. The name represents the initial pool, even if the request has been moved to a new pool due to cascade. For more information about cascade events, see RESOURCE_POOL_MOVE.
THREAD_COUNT INTEGER

Number of threads in use by this request.
OPEN_FILE_HANDLE_COUNT INTEGER

Number of open file handles in use by this request.
MEMORY_INUSE_KB INTEGER

Total amount of memory in kilobytes acquired by this query.

The RESERVED_EXTRA_MEMORY_B column in system table QUERY_PROFILES shows how much unused memory (in bytes) remains that is reserved for a given query but is unassigned to a specific operator.

If operators for a query acquire all memory specified by MEMORY_INUSE_KB, the plan must request more memory from the Vertica resource manager.
QUEUE_ENTRY_TIMESTAMP TIMESTAMPTZ

Timestamp when the request was queued at the Resource Manager.
ACQUISITION_TIMESTAMP TIMESTAMPTZ

Timestamp when the request was admitted to run. See the Notes section below for the difference between these two timestamps.
RELEASE_TIMESTAMP TIMESTAMPTZ

Time when Vertica released this resource acquisition.
DURATION_MS INTEGER

Duration of the resource request in milliseconds.
IS_EXECUTING BOOLEAN

Denotes if the query holding the resource is still executing (t).

Privileges

No explicit privileges are required. You only see the records for tables that you have privileges to view.

Notes

When monitoring resource pools and resource usage by queries, the “queue wait” time is the difference between ACQUISITION_TIMESTAMP and QUEUE_ENTRY_TIMESTAMP. For example, to determine how long a query waits in the queue before it is admitted to run, you can get the difference between the ACQUISITION_TIMESTAMP and the QUEUE_ENTRY_TIMESTAMP using a query like the following: 

 => SELECT pool_name, queue_entry_timestamp, acquisition_timestamp,
    (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' 
    FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';

See Also