Skip to main content

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

  • September 25, 2014
  • 1 reply
  • 9 views
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

    1 reply

    Abhishek_Rana
    Forum|alt.badge.img
    • Abhishek_Rana
    • Participating Frequently
    • January 6, 2015
    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
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement