I have a script:
select
regexp_replace(b.username, '[0-9]', '') username, a.wait_class, count(*)
from
v$active_session_history a, dba_users b
where
a.user_id=b.user_id and
a.sample_time>sysdate - &minutes/1440
group by
regexp_replace(b.username, '[0-9]', ''), a.wait_class
order by
3 desc
and its output is something like:
USERNAME WAIT_CLASS COUNT(*)
UMESS Commit 139
VITPOINT User I/O 126
VITPOINT <NULL> 69
How do I break it down into HOURLY segments, so that the output looks like:
USERNAME START TIME WAIT_CLASS COUNT(*)
UMESS 10:00:00 Commit 66
UMESS 11:00:00 Commit 73
VITPOINT 10:00:00 User I/O 62
VITPOINT 11:00:00 User I/O 64
etc
I would like the interval to be an & variable entered in minutes. So if 60 minutes is the input, then the interval is hourly, for example.
thank you
That has been a nice challenge. I used a number generator to generate a sequence between the max and min sample time. Use the &bucket_minutes variable to provide your interval in minutes.
with cte_basedata as (
select regexp_replace(u.username, '[0-9]', '') username, ash.wait_class, ash.sample_time
from v$active_session_history ash
join dba_users u on (ash.user_id = u.user_id)
where ash.sample_time>sysdate - &minutes/1440
), cte_ng as (
select (sysdate - (level-1) / 24 / 60 * &bucket_minutes) bucket_end, (sysdate - (level) / 24 / 60 * &bucket_minutes) bucket_start
from dual
connect by level <= (select extract (day from (max(sample_time) - min(sample_time)) * 24 * 60) / &bucket_minutes + 1 from cte_basedata)
), cte_data as (
select d.*, cte_ng.bucket_start
from cte_basedata d
left outer join cte_ng on (d.sample_time between cte_ng.bucket_start and cte_ng.bucket_end)
)
select username, wait_class, bucket_start, count(*) from cte_data
group by username, wait_class, bucket_start