Lutz Haake | Freelance Oracle Consultant

A powerful way to analyze your log switch frequency is offered by this script.

define granularity='DD'
define timespan_days=90

select * from
 (
 select
 a.thread#
 , to_number(to_char(a.first_time, '&&granularity')) dy
 , to_number(to_char(a.first_time, 'hh24')) hr
 , count(*) switches
 from v$log_history a
 where 1=1
 and a.first_time > sysdate - &&timespan_days
 group by a.thread#, to_number(to_char(a.first_time, '&&granularity')), to_number(to_char(a.first_time, 'hh24'))
 order by a.thread#, to_number(to_char(a.first_time, '&&granularity')), to_number(to_char(a.first_time, 'hh24'))
 )
 pivot (
 sum(switches)
 for (hr) 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)
 )
 ;

Modes of analysis

day of week: define granularity=‘D’

day of month: define granularity=‘DD’

day of year: define granularity=‘DDD’