Find Redo Log Size / Switch Frequency / Location in Oracle

How to Optimize Redo Logs in Oracle Database

Redo logs are vital components of the Oracle database that record all the changes made to the data. They help in ensuring the database’s availability and recoverability in case of a failure. However, redo logs can also affect the database’s performance if they are not sized and switched properly. In this blog post, we will explain how to optimize redo logs in Oracle database by following some best practices.

What are the factors that influence redo log performance?

The main factors that influence redo log performance are:

The size of the redo logs: The size of the redo logs determines how often they are switched. If the redo logs are too small, they will switch frequently, causing system waits and overhead. If the redo logs are too large, they will take longer to write and archive, increasing the risk of data loss in case of a failure.
The frequency of log switches: The frequency of log switches indicates how often the Log Writer (LGWR) process switches from one redo log file to another. A log switch triggers a checkpoint, which flushes the dirty buffers from the buffer cache to the data files. Frequent log switches can cause excessive checkpoints, which can degrade the database’s performance and increase the recovery time.
The disk configuration of the redo logs: The disk configuration of the redo logs affects how fast they can be written and read. If the redo logs are stored on a slow or congested disk, they will cause bottlenecks and delays. It is recommended to store the redo logs on a dedicated and fast disk, preferably using RAID or ASM.

How to size and switch redo logs in Oracle database?

Determine the optimal sizing of the log_buffer parameter, which is the memory area that stores the redo entries before they are written to the redo logs. You can use the V$SYSSTAT view to check the redo buffer allocation retries and redo wastage statistics. Ideally, these values should be low or zero, indicating that the log_buffer is large enough to accommodate the redo entries.
Size online redo logs to control the frequency of log switches and minimize system waits. You can use the V$LOG_HISTORY view to check the average and maximum log switch intervals. Ideally, the log switch interval should be between 15 and 30 minutes, depending on the workload and the recovery requirements. You can use the following formula to estimate the optimal size of the redo logs:
$Redo log size = Peak redo rate per minute \times Log switch interval$
You can use the V$LOGMNR_STATS view to check the peak redo rate per minute.
Optimize the redo log disk to prevent bottlenecks. You can use the V$LOGFILE and V$LOG views to check the location and status of the redo log files. You should store the redo log files on a separate and fast disk, preferably using RAID 1 or RAID 10 for mirroring and striping. You should also avoid multiplexing the redo log files on the same disk or controller, as this can cause contention and reduce performance.

What are the different types of redo log files in Oracle database?

There are three types of redo log files in Oracle database:

Current: The current redo log file is the one that is being actively written by the LGWR process. There can be only one current redo log file at a time for each instance.
Active: The active redo log files are the ones that are needed for instance recovery in case of a failure. They have not been archived or backed up yet. There can be more than one active redo log file at a time for each instance.
Inactive: The inactive redo log files are the ones that are no longer needed for instance recovery. They have been archived or backed up and can be reused or overwritten. There can be more than one inactive redo log file at a time for each instance.

These types of redo log files are important for maintaining the database’s availability and consistency. You can use the V$LOG view to check the status and sequence number of the redo log files.

How to ensure the database’s reliability with redo log files?

To ensure the database’s reliability with redo log files, you should follow these best practices:

Have at least two redo log groups for each instance, each containing one or more redo log files. This ensures that there is always a redo log file available for writing while the other one is being archived or backed up.
Use the ALTER DATABASE ADD LOGFILE command to add new redo log groups or files. You can specify the size, location, and group number of the new redo log files.
Use the ALTER DATABASE DROP LOGFILE command to drop unused or obsolete redo log groups or files. You can only drop inactive redo log files that have been archived or backed up.
Use the ALTER DATABASE CLEAR LOGFILE command to clear corrupted or inaccessible redo log files. You can only clear inactive redo log files that have been archived or backed up.
Use the ALTER DATABASE SWITCH LOGFILE command to force a log switch manually. This can be useful for testing or troubleshooting purposes, but it should be used with caution as it can cause performance overhead and increase the recovery time.

Check Redo Log Size in Oracle

SQL> set lines 250
SQL> col FILE_NAME for a55
SQL> SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

GROUP# THREAD# SEQUENCE# ARC STATUS FILE_NAME    SIZE_MB
------------   -------------   ----------------   ---- ----------- --------------------------------
7   1   64557   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_7.819.1040821865   1024
8   1   64558   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_8.848.1040821881   1024
9   1   64559    NO   CURRENT +DATAC1/dmwmckpf/onlinelog/group_9.849.1040821889   1024
10   1   64556   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_10.850.1040821897   1024
11   2   80343   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_11.851.1040821905   1024
12   2   80344   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_12.852.1040821913   1024
13   2   80345   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_13.853.1040821921   1024
14   2   80346   NO   CURRENT +DATAC1/dmwmckpf/onlinelog/group_14.854.1040821929   1024

Find Redo Log Size / Switch Frequency / Location in Oracle

SQL> col member for a60
SQL> select GROUP#,TYPE,MEMBER from v$logfile;
GROUP# TYPE MEMBER
---------------------------------------------------
7   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_7.2277.1051493809
8   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_8.2278.1051493821
9   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_9.2279.1051493829
10   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_10.2280.1051493839
11   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_11.2281.1051493859
12   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_12.2282.1051493865
13   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_13.2283.1051493871
14   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_14.2284.1051493877

Find Redo Log File Size
SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024, MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------------------------------------------------------------------------------------------------
7   1   1302   1024   1   INACTIVE
8   1   1303   1024   1   INACTIVE
9   1   1304   1024   1   INACTIVE
10   1   1305   1024   1   CURRENT
11   2   1552   1024   1   INACTIVE
12   2   1553   1024   1   INACTIVE
13   2   1554   1024   1   INACTIVE
14   2   1555   1024   1   CURRENT

Check Redo Log Switch Frequency

Below are some of the queries to find out redo log switches. In this query we are using V$LOG_HISTORY to get the redo log frequency.

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
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;

set pages 999 lines 400
col Day form a10
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;

col c1 format a10 heading "Month"
col c2 format a25 heading "Archive Date"
col c3 format 999 heading "Switches"
compute AVG of C on A
compute AVG of C on REPORT
break on A skip 1 on REPORT skip 1
select
to_char(trunc(first_time), 'Month') c1,
to_char(trunc(first_time), 'Day : DD-Mon-YYYY') c2,
count(*) c3
from
v$log_history
where
trunc(first_time) > last_day(sysdate-100) +1
group by
trunc(first_time)
order by
trunc(first_time);

Query To Check Archive Log Size Generated per day

SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

Summary:
There should not be frequent log switches per hour. Ideally, the redo log switch frequency should be 4-5 log switches per hour.
If there are frequent log switches then check the size of redo logs and increase accordingly.
V$LOG_HISTORY is used to check the history of redo log generation in Oracle.
V$LOGFILE is used to check the file name and location of redo logs.
V$LOG and V$LOGFILE views can be combined to get detailed information like redo log size, redo log members, thread, groups, sequence, status, etc.

Find Redo Log Size / Switch Frequency / Location in Oracle

Check Redo Log Size in Oracle

Find Redo Log Size / Switch Frequency / Location in Oracle

Find Redo Log File Size

Check Redo Log Switch Frequency

Query To Check Archive Log Size Generated per day

Post a Comment

Contact Form