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

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...