What is the difference between the Memory Metrics in Query_Requests VS Resource_Acquisitions
We are performing analysis of memory usage of queries and are curious about the differences between the values reported in the 2 v_monitor columns below. The Definitions of these columns in the SQL Ref manual are nearly the same, yet query_requests.memory_acquired_mb is often null, or does not match value in resource_acquisitions. Is a more detailed definition of these 2 columns available?
v_monitor.resource_acquisitions column memory_inuse_kb
Amount of memory in kilobytes acquired by this request
v_monitor.query_requests column MEMORY_ACQUIRED_MB
Memory acquired by this query request in megabytes
Thanks for any Information you can provide
0
Comments
"memory_inuse_kb" shown in RESOURCE_ACQUISITIONS is the initial reserved memory for this transaction. This initial allocation depends upontotal memory given to the RESOURCE POOL & PLANNED CONCURRENCY of that pool. We can say it's always equal to RESOURCE POOL MEMORY SIZE/PLANNED CONCURRENCY
"memory_acquired_mb" in query_requests represent the same value in round off terms. So there should not be much difference in both of these.
Eg:
dbadmin=> select * FROM d33497.RESOURCE_ACQUISITIONS WHERE transaction_id=45035996284960481 and statement_id=1;
node_name | transaction_id | statement_id | request_type | pool_id | pool_name | thread_count | open_file_handle_count | memory_inuse_kb | queue_entry_timestamp | acquisition_timestamp | release_timestamp | duration_ms | is_executing
-----------------+-------------------+--------------+--------------+-------------------+------------------+--------------+------------------------+-----------------+-------------------------------+-------------------------------+-------------------------------+-------------+--------------
v_ossa_node0010 | 45035996284960481 | 1 | Reserve | 45035996428276146 | hp_vertica_pool1 | 12 | 10 | 512000 | 2015-01-02 14:39:16.097822+00 | 2015-01-02 14:39:17.487125+00 | 2015-01-02 14:39:18.938807+00 | 1451 | f
v_ossa_node0011 | 45035996284960481 | 1 | Reserve | 45035996428276146 | hp_vertica_pool1 | 9 | 7 | 512000 | 2015-01-02 14:39:16.096126+00 | 2015-01-02 14:39:17.485644+00 | 2015-01-02 14:39:18.937322+00 | 1452 | f
v_ossa_node0012 | 45035996284960481 | 1 | Reserve | 45035996428276146 | hp_vertica_pool1 | 9 | 7 | 512000 | 2015-01-02 14:39:16.096635+00 | 2015-01-02 14:39:17.486045+00 | 2015-01-02 14:39:18.937766+00 | 1451 | f
(3 rows)
dbadmin=> select * from d33497.query_requests WHERE transaction_id=45035996284960481 and statement_id=1;
node_name | user_name | session_id | request_id | transaction_id | statement_id | request_type | request | request_label | search_path | memory_acquired_mb | success | error_count | start_timestamp | end_timestamp | request_duration_ms | is_executing
-----------------+------------+-----------------------------------+------------+-------------------+--------------+--------------+-----------------------------------------------------------------------------+---------------+---------------------------------------------------+--------------------+---------+-------------+-------------------------------+------------------------------+---------------------+--------------
v_ossa_node0010 | hp_vertica | ossvert1.gre.hp.com-51343:0xe7b73 | 1 | 45035996284960481 | 1 | QUERY | profile select count(distinct x), * from foo1 group by x,z order by z desc; | | "$user", public, v_catalog, v_monitor, v_internal | 500 | t | | 2015-01-02 14:39:15.990521+00 | 2015-01-02 14:39:18.93935+00 | 2949 | f
(1 row)
Hope it helps.
Regards'
Abhishek