SQL QUERY TO FIND LOG SWITCH PER HOUR HISTORY IN ORACLE

Use this sql query to determine the number of log switches on an hourly basis. This would be more helpful, when you want to diagnose online redolog overhead due to undersized redo files. If there are multiple switches for an hour, then you would need to find the sql query which generate high redo and they can be insert,update or delete.

col Day form a3
col h0 format 99
col h1 format 99
col h2 format 99
col h3 format 99
col h4 format 99
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
select to_char(first_time,'DY') as DAY,
sum(case to_char(FIRST_TIME,'hh24') when '00' then 1 else 0 end ) as h0,
sum(case to_char(FIRST_TIME,'hh24') when '01' then 1 else 0 end ) as h1,
sum(case to_char(FIRST_TIME,'hh24') when '02' then 1 else 0 end) as h2,
sum(case to_char(FIRST_TIME,'hh24') when '03' then 1 else 0 end) as h3,
sum(case to_char(FIRST_TIME,'hh24') when '04' then 1 else 0 end) as h4,
sum(case to_char(FIRST_TIME,'hh24') when '05' then 1 else 0 end) as h5,
sum(case to_char(FIRST_TIME,'hh24') when '06' then 1 else 0 end) as h6,
sum(case to_char(FIRST_TIME,'hh24') when '07' then 1 else 0 end) as h7,
sum(case to_char(FIRST_TIME,'hh24') when '08' then 1 else 0 end) as h8,
sum(case to_char(FIRST_TIME,'hh24') when '09' then 1 else 0 end) as h9,
sum(case to_char(FIRST_TIME,'hh24') when '10' then 1 else 0 end) as h10,
sum(case to_char(FIRST_TIME,'hh24') when '11' then 1 else 0 end) as h11,
sum(case to_char(FIRST_TIME,'hh24') when '12' then 1 else 0 end) as h12,
sum(case to_char(FIRST_TIME,'hh24') when '13' then 1 else 0 end) as h13,
sum(case to_char(FIRST_TIME,'hh24') when '14' then 1 else 0 end) as h14,
sum(case to_char(FIRST_TIME,'hh24') when '15' then 1 else 0 end) as h15,
sum(case to_char(FIRST_TIME,'hh24') when '16' then 1 else 0 end) as h16,
sum(case to_char(FIRST_TIME,'hh24') when '17' then 1 else 0 end) as h17,
sum(case to_char(FIRST_TIME,'hh24') when '18' then 1 else 0 end) as h18,
sum(case to_char(FIRST_TIME,'hh24') when '19' then 1 else 0 end) as h19,
sum(case to_char(FIRST_TIME,'hh24') when '20' then 1 else 0 end) as h20,
sum(case to_char(FIRST_TIME,'hh24') when '21' then 1 else 0 end) as h21,
sum(case to_char(FIRST_TIME,'hh24') when '22' then 1 else 0 end) as h22,
sum(case to_char(FIRST_TIME,'hh24') when '23' then 1 else 0 end) as h23
from v$log_history
group by to_char(first_time,'DY')
order by case to_char(first_time,'DY')
when 'SUN' then 0
when 'MON' then 1
when 'TUE' then 2
when 'WED' then 3
when 'THU' then 4
when 'FRI' then 5
when 'SAT' then 6
end ASC;

Sample output:

kish@exdbx<>col Day form a3
col h0 format 99
col h1 format 99
col h2 format 99
col h3 format 99
col h4 format 99
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
select to_char(first_time,'DY') as DAY,
sum(case to_char(FIRST_TIME,'hh24') when '00' then 1 else 0 end ) as h0,
sum(case to_char(FIRST_TIME,'hh24') when '01' then 1 else 0 end ) as h1,
sum(case to_char(FIRST_TIME,'hh24') when '02' then 1 else 0 end) as h2,
sum(case to_char(FIRST_TIME,'hh24') when '03' then 1 else 0 end) as h3,
sum(case to_char(FIRST_TIME,'hh24') when '04' then 1 else 0 end) as h4,
sum(case to_char(FIRST_TIME,'hh24') when '05' then 1 else 0 end) as h5,
sum(case to_char(FIRST_TIME,'hh24') when '06' then 1 else 0 end) as h6,
sum(case to_char(FIRST_TIME,'hh24') when '07' then 1 else 0 end) as h7,
sum(case to_char(FIRST_TIME,'hh24') when '08' then 1 else 0 end) as h8,
sum(case to_char(FIRST_TIME,'hh24') when '09' then 1 else 0 end) as h9,
sum(case to_char(FIRST_TIME,'hh24') when '10' then 1 else 0 end) as h10,
sum(case to_char(FIRST_TIME,'hh24') when '11' then 1 else 0 end) as h11,
sum(case to_char(FIRST_TIME,'hh24') when '12' then 1 else 0 end) as h12,
sum(case to_char(FIRST_TIME,'hh24') when '13' then 1 else 0 end) as h13,
sum(case to_char(FIRST_TIME,'hh24') when '14' then 1 else 0 end) as h14,
sum(case to_char(FIRST_TIME,'hh24') when '15' then 1 else 0 end) as h15,
sum(case to_char(FIRST_TIME,'hh24') when '16' then 1 else 0 end) as h16,
sum(case to_char(FIRST_TIME,'hh24') when '17' then 1 else 0 end) as h17,
sum(case to_char(FIRST_TIME,'hh24') when '18' then 1 else 0 end) as h18,
sum(case to_char(FIRST_TIME,'hh24') when '19' then 1 else 0 end) as h19,
sum(case to_char(FIRST_TIME,'hh24') when '20' then 1 else 0 end) as h20,
sum(case to_char(FIRST_TIME,'hh24') when '21' then 1 else 0 end) as h21,
sum(case to_char(FIRST_TIME,'hh24') when '22' then 1 else 0 end) as h22,
sum(case to_char(FIRST_TIME,'hh24') when '23' then 1 else 0 end) as h23
from v$log_history
group by to_char(first_time,'DY')
order by case to_char(first_time,'DY')
when 'SUN' then 0
when 'MON' then 1
when 'TUE' then 2
when 'WED' then 3
when 'THU' then 4
when 'FRI' then 5
when 'SAT' then 6
end ASC;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>                                      kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exd                                      bx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish                                      @exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3                                          4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   2                                      0   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   3                                      6

DAY  H0  H1  H2  H3  H4   H5   H6   H7   H8   H9  H10  H11  H12  H13  H14  H15 H                                      16  H17  H18  H19  H20  H21  H22  H23
--- --- --- --- --- --- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -                                      --- ---- ---- ---- ---- ---- ---- ----
SUN   3   1   0   0   0    0    0    0    0    0    0    0    0    2    0    0                                         0    2    0     0    1    1    0    1
MON   5   0   0   0   0    0    0    0    0    0    0    0    0    3    0    0                                         0    0    2     0    0    1    9   13
TUE   3   0   0   0   0    0    0    0    0    0    0    0    0    1    0    1                                         0    4    0    18   17    1    9   17
WED   2   0   0   0   0    0    0    0    0    0    0    0    0    0    0    0                                         3    3    0     0    4    5    4    8
THU   0   0   0   0   0    0    0    0    0    0    0    0    0    0    2    2                                         0    0    1     7    5    6    5    5
FRI   9   0   0   0   0    0    0    0    0    0    0    0    0    0    3    0                                         1    1    0     3   11    0    5   13
SAT  48   0   4   0   0    0    0    0    0    0    0    0    0    0    0    0                                         0    0    0     0    3    5    9    0

7 rows selected.

Elapsed: 00:00:00.18

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s