Resource Pool

Rocky · July 2021

Hi Team,

I'm trying to find if there is way we can get the details of Resource pool usage as below?

1) Number of queries run each hour in the pool over a 7 day span
2) Min, Max and average memory usage each of those hours per pool
3) Min, Max and average query duration each hour per pool

Thanks,
Rocky

mosheg · July 2021

Try this:

select
    time_slice(qr.start_timestamp, 1, 'hour','start') ,
    upper(regexp_substr(qr.request, '\w+')::char(8)) as qtype,
    qr.request_type ,
    ra.pool_name ,
    count(*),
    min(qr.request_duration_ms) as min_ms,
    max(qr.request_duration_ms) as max_ms,
    avg(qr.request_duration_ms)::int as avg_ms,
    min(qr.memory_acquired_mb) as min_mb,
    max(qr.memory_acquired_mb) as max_mb,
    avg(qr.memory_acquired_mb)::int as avg_mb
from
    v_monitor.query_requests qr
    left outer join v_monitor.resource_acquisitions ra
    on qr.statement_id = ra.statement_id and
       qr.transaction_id = ra.transaction_id
where
    qr.is_executing is false and
    (sysdate() - qr.start_timestamp) < '7 days'
group by
    1, 2, 3, 4
order by
    1, 2, 3
;

Rocky · July 2021

Thanks @mosheg

Most of the pool_name I a get NULL value. Any ideas?

mosheg · July 2021

To monitor only "QUERY" request types try this:

select
    time_slice(qr.start_timestamp, 1, 'hour','start') ,
    upper(regexp_substr(qr.request, '\w+')::char(8)) as qtype,
    qr.request_type ,
    ra.pool_name ,
    count(*),
    min(qr.request_duration_ms) as min_ms,
    max(qr.request_duration_ms) as max_ms,
    avg(qr.request_duration_ms)::int as avg_ms,
    min(qr.memory_acquired_mb) as min_mb,
    max(qr.memory_acquired_mb) as max_mb,
    avg(qr.memory_acquired_mb)::int as avg_mb
from
    v_monitor.query_requests qr
    left outer join v_monitor.resource_acquisitions ra
    on qr.statement_id = ra.statement_id and
       qr.transaction_id = ra.transaction_id
where
    qr.is_executing is false and
    qr.request_type = 'QUERY' and
    (sysdate() - qr.start_timestamp) < '7 days'
group by
    1, 2, 3, 4
order by
    1, 2, 3
;

Resource Pool

Answers

Leave a Comment