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:-
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;
/
----------------------------------------------------------------------------------------------------------
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;
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
