Resource Pool

Rocky

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

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

Thanks @mosheg

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

mosheg

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
;