RESOURCE_ACQUISITIONS

Retains information about resources (memory, open file handles, threads) acquired by each running request.

Retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session.

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 each statement within a transaction. NULL indicates that no statement is currently being processed.
REQUEST_TYPE VARCHAR Type of request issued to a resource pool. End users always see this column set to Reserve, to indicate that the request is query-specific.
POOL_ID /
POOL_NAME		 INTEGER /
VARCHAR

Each resource pool that participated in handling this request:

  • POOL_ID: A unique numeric ID assigned by the Vertica catalog that uniquely identifies the resource pool.

  • POOL_NAME: Name of the resource pool.

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.

Column RESERVED_EXTRA_MEMORY_B 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 in this resource pool.
ACQUISITION_TIMESTAMP TIMESTAMPTZ Timestamp when the request was admitted to run.
RELEASE_TIMESTAMP TIMESTAMPTZ Time when Vertica released this resource acquisition.
DURATION_MS INTEGER Duration in milliseconds of request execution. If the request cascaded across multiple resource pools, DURATION_MS applies only to this resource pool.
IS_EXECUTING BOOLEAN

Set to true if the resource pool is still executing this request. A value of false can indicate one of the following:

  • The request was completed or denied.

  • The request cascaded to another resource pool.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Queue wait time

You can calculate how long a resource pool queues a given request before it begins execution by subtracting QUEUE_ENTRY_TIMESTAMP from ACQUISITION_TIMESTAMP. For example:

 => 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