How To Check and Change Redo Log Switch Frequency

Onur ARDAHANLI

5 years ago

If you want to learn What the Redo Log is, you should read the article named “Oracle Redo Log And Archive Log”. I will tell you finding and changing log switch frequency in this article.

If there is a performance problem in the database, you should look at the REDO LOG Switch frequency. If the log switch frequency is too much, it causes a significant performance problem in the database.

For example; if a very high IMPORT or DELETE operation is to be performed and the database FORCE LOGGING cannot be disabled, then it makes sense to add a new ONLINE REDO LOG as much as the size of the data to be IMPORTED, and then delete this ONLINE REDO LOG after the operation is finished.

Check Redo Log Switch Frequency

	SQL>
	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) DES

SQL>

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) DES

In addition, the Mbps values of the generated redo log can be calculated as follows.

select round(avg(mb)) mb_avg, round(avg(mb)*8) mbit_avg, round(avg(mb)*8/86400,2) mbps_avg, round(max(mb)*8/86400,2) mbps_max from (
select trunc(first_time) tdate, sum(blocks*block_size)/1024/1024 as mb from v$archived_log where first_time<trunc(sysdate)
and dest_id=1
group by trunc(first_time));

select round(avg(mb)) mb_avg, round(avg(mb)*8) mbit_avg, round(avg(mb)*8/86400,2) mbps_avg, round(max(mb)*8/86400,2) mbps_max from (

select trunc(first_time) tdate, sum(blocks*block_size)/1024/1024 as mb from v$archived_log where first_time<trunc(sysdate)

and dest_id=1

group by trunc(first_time));

The information of Redo Logs in the database can be found as follows;

SELECT l.thread#, lf.group#, l.bytes, l.members, lf.member, lf.status, lf.type
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
ORDER BY 1, 2, 5;

SELECT l.thread#, lf.group#, l.bytes, l.members, lf.member, lf.status, lf.type

FROM v$log l, v$logfile lf

WHERE l.group# = lf.group#

ORDER BY 1, 2, 5;

Change Redo Log Switch Frequency

The log switch operation takes place when Online Redo Logs are full. If you increase redo log size your log switch frequenct will be reduced. But you may want to perform the Log switch operation periodically. This can be done with the ARCHIVE_LAG_TARGET parameter. ARCHIVE_LAG_TARGET parameter forces log switch.

Let’s test it. We suppose that we have a data guard in this example.

Query the current value of the parameter

[Primary-1] SQL> show parameter archive_lag_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0

[Primary-1] SQL> show parameter archive_lag_target;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

Check current archive log sequence numbers

[Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           312          1
           167          2

[Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

-------------- ----------

312 1

167 2

[Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           312          1
           167          2

[Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

-------------- ----------

312 1

167 2

[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#, applied;

MAX(SEQUENCE#)    THREAD# APPLIED
-------------- ---------- --------
           312          1 YES
           167          2 YES

[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied;

MAX(SEQUENCE#) THREAD# APPLIED

-------------- ---------- --------

312 1 YES

167 2 YES

Change the ARCHIVE_LAG_TARGET Parameter

[Primary-1] SQL> alter system set archive_lag_target=60 scope=both sid='*';

System altered.

[Primary-1] SQL> alter system set archive_lag_target=60 scope=both sid='*';

System altered.

It is learned from ALERT LOG whether or not Log Switch operation is performed every minute. Or you can query with the script I shared at the beginning of the article.

When the value of the parameter is changed, the changes in ALERT LOG are as follows:

[Primary-1]—–>ALERT_LOG

Thu Feb 02 07:24:06 2017
ALTER SYSTEM SET archive_lag_target=60 SCOPE=BOTH;
Thu Feb 02 07:24:07 2017
Thread 1 advanced to log sequence 314 (LGWR switch)
Current log# 2 seq# 314 mem# 0: +DATA/primary/onlinelog/group_2.262.932222811
Thu Feb 02 07:24:07 2017
Deleted Oracle managed file +FRA/primary/archivelog/2017_01_23/thread_2_seq_61.529.934026075
Thu Feb 02 07:24:07 2017
Archived Log entry 5586 added for thread 1 sequence 313 ID 0x69a3f5e6 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 314 for destination LOG_ARCHIVE_DEST_2
Thu Feb 02 07:24:07 2017
LNS: Standby redo logfile selected for thread 1 sequence 314 for destination LOG_ARCHIVE_DEST_3

Thu Feb 02 07:24:06 2017

ALTER SYSTEM SET archive_lag_target=60 SCOPE=BOTH;

Thu Feb 02 07:24:07 2017

Thread 1 advanced to log sequence 314 (LGWR switch)

Current log# 2 seq# 314 mem# 0: +DATA/primary/onlinelog/group_2.262.932222811

Thu Feb 02 07:24:07 2017

Deleted Oracle managed file +FRA/primary/archivelog/2017_01_23/thread_2_seq_61.529.934026075

Thu Feb 02 07:24:07 2017

Archived Log entry 5586 added for thread 1 sequence 313 ID 0x69a3f5e6 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 314 for destination LOG_ARCHIVE_DEST_2

Thu Feb 02 07:24:07 2017

LNS: Standby redo logfile selected for thread 1 sequence 314 for destination LOG_ARCHIVE_DEST_3

[Primary-2]—–>ALERT_LOG

Thu Feb 02 07:24:14 2017
Thread 2 advanced to log sequence 169 (LGWR switch)
Current log# 3 seq# 169 mem# 0: +DATA/primary/onlinelog/group_3.266.932223145
Thu Feb 02 07:24:14 2017
Deleted Oracle managed file +FRA/primary/archivelog/2017_01_23/thread_1_seq_62.527.934026069
LNS: Standby redo logfile selected for thread 2 sequence 169 for destination LOG_ARCHIVE_DEST_2
Thu Feb 02 07:24:15 2017
Archived Log entry 5589 added for thread 2 sequence 168 ID 0x69a3f5e6 dest 1:
Thu Feb 02 07:24:15 2017
LNS: Standby redo logfile selected for thread 2 sequence 169 for destination LOG_ARCHIVE_DEST_3

Thu Feb 02 07:24:14 2017

Thread 2 advanced to log sequence 169 (LGWR switch)

Current log# 3 seq# 169 mem# 0: +DATA/primary/onlinelog/group_3.266.932223145

Thu Feb 02 07:24:14 2017

Deleted Oracle managed file +FRA/primary/archivelog/2017_01_23/thread_1_seq_62.527.934026069

LNS: Standby redo logfile selected for thread 2 sequence 169 for destination LOG_ARCHIVE_DEST_2

Thu Feb 02 07:24:15 2017

Archived Log entry 5589 added for thread 2 sequence 168 ID 0x69a3f5e6 dest 1:

Thu Feb 02 07:24:15 2017

LNS: Standby redo logfile selected for thread 2 sequence 169 for destination LOG_ARCHIVE_DEST_3

[Standby-1]—–>ALERT_LOG

Thu Feb 02 07:23:53 2017
Media Recovery Waiting for thread 1 sequence 314 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 314 Reading mem 0
Mem# 0: +DATA/standby/onlinelog/group_5.267.932283683
Mem# 1: +FRA/standby/onlinelog/group_5.260.932283683
Media Recovery Waiting for thread 2 sequence 169 (in transit)
Recovery of Online Redo Log: Thread 2 Group 8 Seq 169 Reading mem 0
Mem# 0: +DATA/standby/onlinelog/group_8.270.932283685
Mem# 1: +FRA/standby/onlinelog/group_8.263.932283685

Thu Feb 02 07:23:53 2017

Media Recovery Waiting for thread 1 sequence 314 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 314 Reading mem 0

Mem# 0: +DATA/standby/onlinelog/group_5.267.932283683

Mem# 1: +FRA/standby/onlinelog/group_5.260.932283683

Media Recovery Waiting for thread 2 sequence 169 (in transit)

Recovery of Online Redo Log: Thread 2 Group 8 Seq 169 Reading mem 0

Mem# 0: +DATA/standby/onlinelog/group_8.270.932283685

Mem# 1: +FRA/standby/onlinelog/group_8.263.932283685

[Standby-2]—–>ALERT_LOG

Thu Feb 02 07:24:08 2017
RFS[1]: Selected log 5 for thread 1 sequence 314 dbid 1769705496 branch 934378910
Thu Feb 02 07:24:08 2017
Archived Log entry 3069 added for thread 1 sequence 313 ID 0x69a3f5e6 dest 1:
Thu Feb 02 07:24:09 2017
Archived Log entry 3070 added for thread 2 sequence 168 ID 0x69a3f5e6 dest 1:
Thu Feb 02 07:24:09 2017
RFS[2]: Selected log 8 for thread 2 sequence 169 dbid 1769705496 branch 934378910

Thu Feb 02 07:24:08 2017

RFS[1]: Selected log 5 for thread 1 sequence 314 dbid 1769705496 branch 934378910

Thu Feb 02 07:24:08 2017

Archived Log entry 3069 added for thread 1 sequence 313 ID 0x69a3f5e6 dest 1:

Thu Feb 02 07:24:09 2017

Archived Log entry 3070 added for thread 2 sequence 168 ID 0x69a3f5e6 dest 1:

Thu Feb 02 07:24:09 2017

RFS[2]: Selected log 8 for thread 2 sequence 169 dbid 1769705496 branch 934378910

How we can force a log switch?

You can force log switch with the below command;

ALTER SYSTEM SWITCH LOGFILE;

1	ALTER SYSTEM SWITCH LOGFILE;