Log switches
The transaction log of the Oracle database is a group of operating system files on the database server. An Oracle database must have at least two log groups. A log group consists of one or more identical operating system files that records the log entries of committed transactions. The log group being written to by LGWR process at anytime is the current group. Each log group has a static size and eventually fills with information. Once the LGWR process has completely filled the current log group, Oracle performs a log switch. During a log switch, Oracle closes the current redo log group, opens the next log group, and begins writing log entries to the new current log group.
At the time of a log switch, the LGWR process declares a checkpoint as well. When a checkpoint occurs, the LGWR process will tell the DBWR process to write all blocks in the dirty buffer write queue to disk. Therefore, having an excessive amount of log switches during the day, can degrade system performance. If your redo log groups are too small, LGWR will spend excessive periods of time switching from one log group to another as well as the DBWR process will be spending too much time writing dirty buffers to disk.
Therefore too much log switching is not good for database perfromance.
As a rule of tumb 6 log switches per hour is the maximum.
Increasing the size of redo log groups is one way to lower log switch frequency, please refer to Oracle documentation on how to lower log switch frequency.
To view how often log switches occur in your database, you can query the dynamic performance view V$LOG_HISTORY. This view records the time of each log switch in the database for the most recent n log switches, where n is the value of the database parameter MAXLOGHISTORY. MAXLOGHISTORY is a permanent database parameter and is set in the CREATE DATABASE statement. I typically set this value to 1000 on my production systems. You can change the value of MAXLOGHISTORY by either re-creating the database or simply re-creating the controlfile.
You can use the following query to determine your log switch frequency past 7 days (by hour):
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 8
Query to show the principal information about online redo log files.
select f.group#, l.sequence#, f.member, l.status, l.bytes/1048576 sz
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#, f.member;