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
, andRELEASE_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:
|
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 If operators for a query acquire all memory specified by |
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, |
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:
|
Privileges
No explicit privileges are required. You only see the 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';