Oracle: Analyze database statistics based on v$log_history

analyze_v$log_history.sql

-- Source: https://www.dba-scripts.com/scripts/diagnostic-and-tuning/troubleshooting/database-activity-log_history/

-- You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the precision you want.
-- Oracle PL/SQL
select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY 
from (
		select to_char(trunc(first_time), 'Day') DAY, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
		from v$log_history 
		group by TRUNC(FIRST_TIME, 'DDD'), to_char(trunc(first_time), 'Day')
		order by 2
	 )
group by day;

-- You can find out which day of the week is the most active. You can also have a day to day analysis for the last month                  
select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY 
from (
 select TRUNC(FIRST_TIME, 'DDD') DAY, count(*) LOG_SWITCHES
 from v$log_history 
 where first_time between sysdate -30 and sysdate
 group by TRUNC(FIRST_TIME, 'DDD'), to_char(trunc(first_time), 'Day')
 order by 1
 )
group by day
order by 2;

                 
-- You can also drilldown to hours in the day:
select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
 from v$log_history 
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
 )
group by Hour
order by Hour;