Memory used by query

Use the following query to check currently run queries:
SELECT r.pool_name,
s.node_name AS initiator_node,
s.session_id,
r.transaction_id,
r.statement_id,
max(s.user_name) AS user_name,
max(substr(s.current_statement, 1, 100)) AS statement_running,
max(r.thread_count) AS threads,
max(r.open_file_handle_count) AS fhandlers,
max(r.memory_inuse_kb) AS max_KB_mem,
count(DISTINCT r.node_name) AS nodes_count,
min(r.queue_entry_timestamp) AS entry_time,
max(((r.acquisition_timestamp - r.queue_entry_timestamp))) AS waiting_queue,
max(((clock_timestamp() - r.queue_entry_timestamp))) AS running_time
FROM (v_internal.vs_resource_acquisitions r JOIN v_monitor.sessions s ON (((r.transaction_id = s.transaction_id) AND (r.statement_id = r.statement_id))))
WHERE (length(s.current_statement) > 0)
GROUP BY r.pool_name,
s.node_name,
s.session_id,
r.transaction_id,
r.statement_id
ORDER BY r.pool_name;

Use the following query to find the memory used by a query in one node.
Memory used will always be reflected in the pool of query initiation.
SELECT transaction_id,statement_id, max(memory_kb) from dc_resource_acquisitions
where transaction_id= and
statement_id= and
node_name=’node1’
group by transaction_id,statement_id;

Request types:
Reserve = Query gets initially based on Planned Concurrency
AcquireAdditional = Sums up previous value with additional memory requested (values are continuously increasing)
Acquire = memory allotted for optimizer planning

To check how much memory the query really needs, do:
create resource pool p_pool memorysize '1K' plannedconcurrency 4 maxconcurrency 4;
set session resource_pool to p_pool;
profile ... your query;
If you don't have the memory usage printed (which may happen)
select * from resource_acquisitions where transaction_id=... and statement_id=...;
The trick is that p_pool has very small memorysize.
If you run profile on general pool instead, it will use the query_budget instead on minimal memory needed.
Of course p_pool is only for profiling.

Thanks a lot Moshe!