SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

Using below query you can find how many redo log switched per hours in oracle database .it will show you how much database is busy .


Login as sys user and run below query .

Query:
============

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;


Example :

SQL> 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
  2    3    4   from v$log_history
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
  5    6    7   )
group by Hour
  8    9  order by Hour;

HO LOG_SWITCHES
-- ------------
00           56
01          439
02          412
03          120
04          306
05          419
06            7
07            8
08          137
09          182
10          113
11           17
12            2
14            1
17          119
18          301
19          117
21            7
22            3

19 rows selected.

SQL>