Oracle Redo log tuning

Question: How to tune the Oracle redo logs?

Answer: Oracle redo logs are archived when you turn-on archive-log mode, and the overhead relates to the work done by the LGWR and ARCH background processes as they write the redo information. 

The steps for tuning redo log performance are straightforward:

1 - Determine the optimal sizing of the log_buffer.
 

2 - Size online redo logs to control the frequency of log switches and minimize system waits.

3 - Optimize the redo log disk to prevent bottlenecks.  In high-update databases, no amount of disk tuning may relieve redo log bottlenecks, because Oracle must push all updates, for all disks, into a single redo location.

 Once you have optimized your redo and I/O sub-system, you have few options to relieve redo-induced contention.  This can be overcome by employing super-fast solid-state disk for your online redo log files, since SSD has far greater bandwidth than platter disk.  This will greatly improve DML throughput for high-update databases.  As SSD prices continue to fall, SSD becomes increasing affordable, and SSD will someday replace spindle platter disk for all Oracle data storage.

Size of the redo log members (files on disk) size, then use the query below:

-- Show Redo Logs info

set linesize 300

column REDOLOG_FILE_NAME format a50

SELECT

    a.GROUP#,

    a.THREAD#,

    a.SEQUENCE#,

    a.ARCHIVED,

    a.STATUS,

    b.MEMBER    AS REDOLOG_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# ASC;

The query output should look like:

GROUP   #THREAD#  SEQUENCE# ARC STATUS   REDOLOG_FILE_NAME      SIZE_MB

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

1       1       4 YES INACTIVE /ORACLE/oradata/orcl1/redo01.log   50

2       1       5 YES INACTIVE /ORACLE/oradata/orcl1/redo02.log   50

3       1       6 NO  CURRENT  /ORACLE/oradata/orcl1/redo03.log   50

 Tracking Redo Log usage

select * from (select a.username,a.sid,b.value 

from v$session a,v$sesstat b,v$statname c 

where a.sid=b.sid and b.statistic#=c.statistic# and c.name='redo size'

order by b.value desc) where rownum < 11;

Some of the scripts which can be useful. 

PROMPT REDO GENERATION:-

select value from v$sysstat 
where name = 'redo size' 
/ 

PROMPT Redo generated during my session since the session started: 

select value redo_size 
from v$mystat, v$statname 
where v$mystat.STATISTIC# = v$statname.STATISTIC# 
and name = 'redo size' 
/ 

PROMPT Redo generated by current user sessions: 

select v$session.sid, username, value redo_size 
from v$sesstat, v$statname, v$session 
where v$sesstat.STATISTIC# = v$statname.STATISTIC# 
and v$session.sid = v$sesstat.sid 
and name = 'redo size' 
and value > 0 
and username is not null 
order by value 
/ 
----------------------------------------------------------------------------------------------------

SELECT s.sid, s.serial#, s.username, s.program, 
i.block_changes 
FROM v$session s, v$sess_io i 
WHERE s.sid = i.sid 
ORDER BY 5 desc, 1, 2, 3, 4; 
/ 

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

SELECT s.sid, s.serial#, s.username, s.program, 
t.used_ublk, t.used_urec 
FROM v$session s, v$transaction t 
WHERE s.taddr = t.addr 
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

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

select b.inst_id, b.SID, b.serial# sid_serial, b.username, machine, b.osuser, b.status, a.redo_mb MB

from (select n.inst_id, sid, round(value/1024/1024) redo_mb from gv$statname n, gv$sesstat s

where n.inst_id=s.inst_id and n.statistic#=134 and s.statistic# = n.statistic# order by value desc) a, gv$session b

where b.inst_id=a.inst_id

  and a.sid = b.sid

and   rownum <= 10;

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

SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),

dbms_lob.substr(sql_text,4000,1),

dhss.instance_number,

dhss.sql_id,executions_delta,rows_processed_delta

FROM dba_hist_sqlstat dhss,

dba_hist_snapshot dhs,

dba_hist_sqltext dhst

WHERE upper(dhst.sql_text) LIKE ‘%segmentname%’

AND dhss.snap_id=dhs.snap_id

AND dhss.instance_Number=dhs.instance_number

AND begin_interval_time BETWEEN to_date(’2013_06_22 17',’YYYY_MM_DD HH24')

AND to_date(’2013_07_22 21',’YYYY_MM_DD HH24')

AND dhss.sql_id = dhst.sql_id;

The accounting for redo size by session is located in v$sesstat.  You can do a

Simple query for a RAC cluster to see the largest redo generating session:

select s.inst_id, s.sid, serial#, program, module, username, value redo_size

from gv$session s, gv$sesstat ss, v$statname sn where s.sid = ss.sid and

ss.statistic# = sn.statistic# and sn.name = 'redo size'

and s.inst_id = ss.inst_id

order by redo_size