What is the difference between the Memory Metrics in Query_Requests VS Resource_Acquisitions

greg_1

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

Abhishek_Rana

Hi,

"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