Oracle Database -- Hourly Histogram for Redo Log Switches Rate

 Problem

You need to identify the rate of redo log switches by every hour

Solution

Use the script below:

select * from (
select
trunc(first_time) this_Date,
extract(hour from cast(trunc(first_time,'hh24') as timestamp)) hour,
count(*) n
from v$log_history
group by
trunc(first_time),
extract(hour from cast(trunc(first_time,'hh24') as timestamp))
order by 1 desc, 2 desc
)
pivot (
max(n) for hour in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)
)
order by this_Date desc;