Resource Pool

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
;

Thanks @mosheg

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

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
;