Oracle: number of redo log switches per hour
Queries to count redo log switches per hour and per day using V$LOG_HISTORY and GV$LOG_HISTORY.
This article gives several Oracle queries that count redo log switches per hour and per day, based on V$LOG_HISTORY and GV$LOG_HISTORY. They help check whether the size and number of redo log groups fit the database workload.
Redo log groups: number and size
select group#,MEMBERS,BYTES/1024/1024 "SIZE MB" from v$log;
Switches per hour over the last 24 hours
select to_char(first_time,'YYYY-MM-DD HH24'), count(*)
from v$log_history
where first_time between (sysdate - 1) and sysdate
group by to_char(first_time,'YYYY-MM-DD HH24')
order by to_char(first_time,'YYYY-MM-DD HH24');
Switches per hour and per day
SQL*Plus script with paged output. The WHERE thread#=&EnterThreadId line is commented out: enable it to filter on one thread (RAC instance).
-- switchLogPerHour.sql
-- Show the Number of Redo Log Switches Per Hour
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT to_char(first_time, 'yyyy - mm - dd') aday,
to_char(first_time, 'hh24') hour,
count(*) total
FROM v$log_history
--WHERE thread#=&EnterThreadId
GROUP BY to_char(first_time, 'yyyy - mm - dd'),
to_char(first_time, 'hh24')
ORDER BY to_char(first_time, 'yyyy - mm - dd'),
to_char(first_time, 'hh24') asc
/
Hourly switch map (RAC, last 7 days)
One row per day and per instance, one column per hour (h0 to h23), with the total and the hourly average. This version uses GV$LOG_HISTORY.
set pages 999 lines 400
col h0 format 999
col h1 format 999
col h2 format 999
col h3 format 999
col h4 format 999
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 TRUNC (first_time) "Date", inst_id, 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",
ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate -7
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;
Hourly switch map (last 14 days)
Same layout using V$LOG_HISTORY (current instance). The condition trunc (first_time) >= (trunc(sysdate) - 14) sets the number of days: 0 for today only, 1 for today and yesterday.
set linesize 300
set pagesize 5000
set trimspool on
column 00 format 99 heading "00:00"
column 01 format 99 heading "1am"
column 02 format 99 heading "2am"
column 03 format 99 heading "3am"
column 04 format 99 heading "4am"
column 05 format 99 heading "5am"
column 06 format 99 heading "6am"
column 07 format 99 heading "7am"
column 08 format 99 heading "8am"
column 09 format 99 heading "9am"
column 10 format 99 heading "10am"
column 11 format 99 heading "11am"
column 12 format 99 heading "12:00"
column 13 format 99 heading "1pm"
column 14 format 99 heading "2pm"
column 15 format 99 heading "3pm"
column 16 format 99 heading "4pm"
column 17 format 99 heading "5pm"
column 18 format 99 heading "6pm"
column 19 format 99 heading "7pm"
column 20 format 99 heading "8pm"
column 21 format 99 heading "9pm"
column 22 format 99 heading "10pm"
column 23 format 99 heading "11pm"
column 24 format 99 heading "12pm"
column "Day" format a3
prompt
prompt Redo Log Switches
prompt
SELECT trunc (first_time) "Date",
to_char (trunc (first_time),'Dy') "Day",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23"
from v$log_history
where trunc (first_time) >= (trunc(sysdate) - 14) -- last X days. 0 = today only. 1 = today and yesterday
group by trunc (first_time)
order by trunc (first_time) DESc;
Notes
- The
h0toh23(or00to23) columns are the hours from 00:00 to 23:00; the values are the number of switches in that hour. V$LOG_HISTORYkeeps a limited history (it depends, among other things, on theCONTROL_FILE_RECORD_KEEP_TIMEparameter), so the period actually covered may be shorter than requested.