Database Reference
In-Depth Information
FROM v$log a,
v$logfile b
WHERE a.group# = b.group#
ORDER BY a.group#, b.member;
GROUP# THREAD# GRP_STATUS MEMBER MEM_STATUS MBYTES
------ ------- ---------- ------------------------------ ---------- -------
1 1 CURRENT /u01/oraredo/O12C/redo01a.rdo 50
1 1 CURRENT /u02/oraredo/O12C/redo01b.rdo 50
2 1 INACTIVE /u01/oraredo/O12C/redo02a.rdo 50
2 1 INACTIVE /u02/oraredo/O12C/redo02b.rdo 50
3 1 INACTIVE /u01/oraredo/O12C/redo03a.rdo 50
3 1 INACTIVE /u02/oraredo/O12C/redo03b.rdo 50
When you're diagnosing online redo log issues, the
V$LOG
and
V$LOGFILE
views are particularly helpful. You can
query these views while the database is mounted or open.
Determining the Optimal Size of Online Redo Log Groups
Try to size the online redo logs so that they switch anywhere from two to six times per hour. The
V$LOG_HISTORY
contains a history of how frequently the online redo logs have switched. Execute this query to view the number of log
switches per hour:
select count(*)
,to_char(first_time,'YYYY:MM:DD:HH24')
from v$log_history
group by to_char(first_time,'YYYY:MM:DD:HH24')
order by 2;
COUNT(*) TO_CHAR(FIRST
---------- -------------
2 2014:09:24:04
80 2014:09:24:05
44 2014:09:24:06
10 2014:09:24:12
From the previous output, you can see that a great deal of log switch activity occurred from approximately
4:00 am to 6:00 am This could be due to a nightly batch job or users' in different time zones updating data. For
this database the size of the online redo logs should be increased. You should try to size the online redo logs to
accommodate peak transaction loads on the database.
The
V$LOG_HISTORY
derives its data from the control file. Each time there is a log switch, an entry is recorded
in this view that details information such as the time of the switch and the system change number (SCN). As stated,
a general rule of thumb is that you should size your online redo log files so that they switch approximately two to
six times per hour. You don't want them switching too often because there is overhead with the log switch. Oracle
initiates a checkpoint as part of a log switch. During a checkpoint the database writer background process writes
modified (also called dirty) blocks to disk, which is resource intensive.
Then again, you don't want online redo log files never to switch, because the current online redo log contains
transactions that you may need in the event of a recovery. If a disaster causes a media failure in your current online
redo log, you can lose those transactions that haven't been archived.
