Gvlogfreq (gvlogfreq.sql)

What This Script Does

This script provides a visual heat map of redo log switch frequency by hour, helping identify peak activity periods and potential log sizing issues. Essential for redo log tuning and capacity planning.

The Script

rem  gvlogfreq.sql
rem
ttitle 'Log Switch Frequency Report'
rem
set linesize 150
set pagesize 200
rem
col dt  format a10 heading 'Date'
col day format a4  heading 'Day'
col total format 999 heading 'Total'
col h0 format 999 heading '00'
col h1 format 999 heading '01'
col h2 format 999 heading '02'
col h3 format 999 heading '03'
col h4 format 999 heading '04'
col h5 format 999 heading '05'
col h6 format 999 heading '06'
col h7 format 999 heading '07'
col h8 format 999 heading '08'
col h9 format 999 heading '09'
col h10 format 999 heading '10'
col h11 format 999 heading '11'
col h12 format 999 heading '12'
col h13 format 999 heading '13'
col h14 format 999 heading '14'
col h15 format 999 heading '15'
col h16 format 999 heading '16'
col h17 format 999 heading '17'
col h18 format 999 heading '18'
col h19 format 999 heading '19'
col h20 format 999 heading '20'
col h21 format 999 heading '21'
col h22 format 999 heading '22'
col h23 format 999 heading '23'


select * from
(
SELECT  trunc(first_time)  Dt ,
        to_char(first_time, 'Dy')  Day ,
        count(1)  Total ,
        SUM(decode(to_char(first_time, 'hh24'),'00',1,0))  h0 ,
        SUM(decode(to_char(first_time, 'hh24'),'01',1,0))  h1 ,
        SUM(decode(to_char(first_time, 'hh24'),'02',1,0))  h2 ,
        SUM(decode(to_char(first_time, 'hh24'),'03',1,0))  h3 ,
        SUM(decode(to_char(first_time, 'hh24'),'04',1,0))  h4 ,
        SUM(decode(to_char(first_time, 'hh24'),'05',1,0))  h5 ,
        SUM(decode(to_char(first_time, 'hh24'),'06',1,0))  h6 ,
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0))  h7 ,
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0))  h8 ,
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0))  h9 ,
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0))  h10 ,
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0))  h11 ,
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0))  h12 ,
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0))  h13 ,
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0))  h14 ,
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0))  h15 ,
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0))  h16 ,
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0))  h17 ,
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0))  h18 ,
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0))  h19 ,
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0))  h20 ,
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0))  h21 ,
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0))  h22 ,
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0))  h23
FROM    V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
order by 1 desc
)
where rownum < nvl('&rownum_32_default', '32')
;

--number of rows in this view are dependent on the following init.parm = control_file_record_keep_time
--and the following db setting upon creation MAXLOGHISTORY (default is usually 1000)

Parameters

The script prompts for:

&Number - of days to display (optional, defaults to 32)

Usage

-- Basic usage
@gvlogfreq.sql

-- When prompted, enter:
-- rownum_32_default: Number of days to display (optional, defaults to 32)

Sample Output

Log Switch Frequency Report

Date       Day  Total  00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
---------- ---- ----- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2025-01-05 Sun     48   1   0   0   0   0   1   2   3   4   3   2   3   4   3   2   3   4   3   2   1   1   1   1   0
2025-01-04 Sat     72   2   1   0   0   0   2   3   4   5   4   3   4   5   4   3   4   5   4   3   2   2   2   2   2
2025-01-03 Fri    156   4   2   1   1   2   4   6   8  10   8   6   8  10   8   6   8  10   8   6   4   4   4   4   4
2025-01-02 Thu    145   3   2   1   1   2   3   5   7   9   7   5   7   9   7   5   7   9   7   5   3   3   3   3   3
2025-01-01 Wed     52   1   1   0   0   0   1   2   3   4   3   2   3   4   3   2   3   4   3   2   1   1   1   1   1
2024-12-31 Tue    168   4   3   2   2   3   4   6   8  11   9   7   9  11   9   7   9  11   9   7   4   4   4   4   4
2024-12-30 Mon    162   4   3   2   2   3   4   6   8  10   8   6   8  10   8   6   8  10   8   6   4   4   4   4   4