/* View average # of log file switches per hour for a month */

logfile_switches.sql

/* View average # of log file switches per hour for a month */
select month "Month",
  case
    when month = to_char(sysdate, 'Month') then
      /* divide by hours since start of the month when looking at current month*/
      round(log_switches / (24 * ((to_number(to_char(sysdate, 'dd'))) - 1) + 
        (to_number(to_char(sysdate, 'hh'))) ), 1)
    else
      /* otherwise divide by total # hours in month */
      round(log_switches / (24 * to_number(to_char(last_day(to_date(month, 'Month')), 'dd'))), 1)
  end "Avg Log Switches per Hour"
from
  (select to_char(first_time,'Month') month,
    count(*) log_switches
  from v$log_history
  group by to_char(first_time,'Month'))
order by to_date(month, 'Month') desc;