RESOURCE_ACQUISITIONS

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.

If a request cascades to one or more resource pools beyond the original pool, this table contains multiple records for the same request—one record for each resource pool. The following values are specific to each resource pool:

  • Timestamp values: QUEUE_ENTRY_TIMESTAMP, ACQUISITION_TIMESTAMP, and RELEASE_TIMESTAMP
  • DURATION_MS
  • IS_EXECUTING

You can trace the history of cascade events by querying system table RESOURCE_POOL_MOVE.

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';