 |
|
Monitoring Redo Log
Status
Oracle Tips by Burleson Consulting
|
Remote DBAs should monitor redo log status to
determine which logs are in use and if there are any odd status codes
such as stale log indications or indications of corrupt redo logs. The
log files can have the following status values:
USED. Indicates status of a log that has
just been added (never used) or that a RESETLOGS command has been
issued.
CURRENT. Indicates a valid log that is in
use.
ACTIVE. Indicates a valid log file that is
not currently in use.
CLEARING. Indicates log is being re-created
as an empty log due to Remote DBA action.
CLEARING CURRENT. Means that current log
is being cleared of a closed thread. If a log stays in this status, it
could indicate there is some failure in the log switch.
INACTIVE. Means that log is no longer
needed for instance recovery but may be needed for media recovery.
The v$logfile table has a status
indicator that gives these additional codes:
INVALID. File is inaccessible.
STALE. File contents are incomplete (such
as when an instance is shut down with SHUTDOWN ABORT or due to a
system crash).
DELETED. File is no longer used.
The script in Source 11.27 provides
some basic information on log status. Listing 11.22 shows an example
of output from LOG_STAT.SQL script.
SOURCE 11.27 Example LOG_STAT.SQL script.
rem
rem Name: log_stat.sql
rem
rem FUNCTION: Provide a current status for
redo logs
rem
rem
COLUMN first_change# FORMAT 99999999 HEADING
Change#
COLUMN group# FORMAT 9,999 HEADING
Grp#
COLUMN thread# FORMAT 999 HEADING
Th#
COLUMN sequence# FORMAT 999,999 HEADING
Seq#
COLUMN members FORMAT 999 HEADING
Mem
COLUMN archived FORMAT a4 HEADING
Arc?
COLUMN first_time FORMAT a21 HEADING
'Switch|Time'
BREAK ON thread#
SET PAGES 60 LINES 131 feedback OFF
START title132 'Current Redo Log Status'
SPOOL rep_out\&db\log_stat
SELECT thread#,group#,sequence#,bytes,
members,archived,
status,first_change#,
TO_CHAR(first_time, 'DD-MM-YYYY
HH24:MI:SS') first_time
FROM
sys.v_$log
See
Code Depot for Full Scripts
SPOOL OFF
PAUSE Press Enter to continue
SET PAGES 22 LINES 80 feedback ON
CLEAR BREAKS
CLEAR COLUMNS
TTILE OFF
LISTING 11.22 Example output of script to
monitor redo log status.
Date:
06/15/97 Page: 1
Time: 01:39 PM Current Redo Log
Status SYSTEM
ORTEST1 database
Switch
Th# Grp# Seq# BYTES Mem Arc? STATUS
Change# Time
--- ---- ----- ------- -------- --------
------- ------------------
1 1 4,489 1048576 2 NO INACTIVE
719114 15-JUN-97 16:54:23
2 4,490 1048576 2
NO INACTIVE 719117 15-JUN-97 16:56:10
3 4,491 1048576 2 NO CURRENT
719120 15-JUN-97 17:02:22
Monitoring Redo Log Switches
In addition to the alert logs, the frequency
of log switches can also be monitored via the v$log_history and
v$archived_log DPTs. A script that uses these DPTs for this purpose is
shown in Source 11.28. Listing 11.23 shows an example of output from
an archive log switch script.
SOURCE 11.28 Script to monitor archive log
switches.
REM
REM NAME :log_hist.sql
REM PURPOSE :Provide info on logs for
last 24 hours since last
REM PURPOSE :log switch
REM USE : From SQLPLUS
REM Limitations : None
REM MRA 10/14/01 Updated for Oracle9i
REM
COLUMN thread# FORMAT 999
HEADING 'Thrd#'
COLUMN sequence# FORMAT 99999
HEADING 'Seq#'
COLUMN first_change#
HEADING 'SCN Low#'
COLUMN next_change#
HEADING 'SCN High#'
COLUMN archive_name FORMAT a50
HEADING 'Log File'
COLUMN first_time FORMAT a20
HEADING 'Switch Time'
COLUMN name FORMAT a30
HEADING 'Archive Log'
SET LINES 132 feedback OFF VERIFY OFF
START title132 "Log History Report"
SPOOL rep_out\&db\log_hist
REM
SELECT
X.recid,a.thread#,
a.sequence#,a.first_change#,
a.switch_change#,
TO_CHAR(a.first_time,'DD-MON-YYYY
HH24:MI:SS') first_time,
x.name
FROM
v$loghist a, v$archived_log x
WHERE
a.first_time>
(SELECT b.first_time-1
FROM v$loghist b WHERE b.switch_change# =
(SELECT MAX(c.switch_change#) FROM
v$loghist c)) AND
See
Code Depot for Full Scripts
SPOOL OFF
SET LINES 80 VERIFY ON feedback ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press Enter to continue
LISTING 11.23 Example of output from
archive log switch script.
Date:
10/14/01
Page: 1
Time: 04:10 PM Log
History Report Remote DBAUTIL
galinux1 database
RECID Thrd# Seq# SCN Low# SWITCH_CHANGE#
Switch Time Archive Log
--------- ----- ------ --------- -------------- --------------------
------------- 1 8 375520
409741 05-SEP-2001 08:18:06
Press Enter to continue
Monitoring Redo Statistics
There are no views in Oracle that allow the
user to look directly at a log file’s statistical data. Instead, we
must look at statistics based on redo log and log writer process
statistics. These statistics are in the views V$STATNAME, V$SESSION,
V$PROCESS, V$SESSTAT, V$LATCH, and V$LATCHNAME. An example of a report
that uses these views is shown in Source 11.29; an example of the
script’s output is shown in Listing 11.24.
SOURCE 11.29 Script to generate reports on
redo statistics.
REM
REM NAME : rdo_stat.sql
REM PURPOSE : Show REDO latch statistics
REM USE : from SQLPlus
REM Limitations : Must have access to v$_
views
REM
SET PAGES 56 LINES 78 VERIFY OFF feedback OFF
START title80 "Redo Latch Statistics"
SPOOL rep_out/&&db/rdo_stat
rem
COLUMN name FORMAT a30 HEADING
Name
COLUMN percent FORMAT 999.999 HEADING
Percent
COLUMN total HEADING
Total
rem
SELECT
l2.name,
immediate_gets+gets Total,
immediate_gets "Immediates",
misses+immediate_misses "Total Misses",
DECODE
(100.*(GREATEST(misses+immediate_misses,1)/
GREATEST(immediate_gets+gets,1)),100,0)
Percent
FROM
v$latch l1,
v$latchname l2
WHERE
l2.name like '%redo%'
and l1.latch#=l2.latch# ;
rem
PAUSE Press Enter to continue
rem
rem Name: Redo_stat.sql
rem
rem Function: Select redo statistics from v$sysstat
COLUMN name FORMAT a30 HEADING 'Redo|Statistic|Name'
COLUMN value FORMAT 999,999,999 HEADING 'Redo|Statistic|Value'
SET PAGES 80 LINES 60 feedback OFF VERIFY OFF
START title80 'Redo Log Statistics'
SPOOL rep_out/&&db/redo_stat
SELECT
name,
value
FROM
v$sysstat
WHERE
name LIKE '%redo%'
ORDER BY statistic#;
SPOOL OFF
SET LINES 24 feedback ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS
LISTING 11.24 Example of output from redo
report scripts.
Date:
10/14/01 Page: 1
Time: 04:14 PM Redo Latch
Statistics Remote DBAUTIL
galinux1 databa
Name Total
Immediates Total Misses Percent
--------------------------- ---------
---------- ------------ --------
redo allocation 172438
0 0
redo copy 6259
6231 0
redo writing 672470
0 0
Press Enter to continue
Date:
10/14/01 Page: 1
Time: 04:14 PM Redo Log
Statistics Remote DBAUTIL
galinux1 databa
Redo Redo
Statistic Statistic
Name Value
------------------------------ ------------
redo synch writes 250
redo synch time 72
redo entries 6,231
redo size 1,569,816
redo buffer allocation retries 0
redo wastage 1,200,696
redo writer latching time 0
redo writes 3,635
redo blocks written 5,586
redo write time 151
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
Of course, right about now you are
probably asking, what good all these numbers will do you. Let’s look
at what they mean and how you can use them. The first section of the
report in Source 11.29 should be self-explanatory. The redo logs use
two latches: REDO ALLOCATION and REDO COPY.
In general, if the PERCENT statistic
(actually, the ratio of total misses to total gets) is greater than 10
percent, contention is occurring, and the Remote DBA needs to examine the way
he or she is doing redo logs (more about this in a second). The
initial latch granted for redo is the REDO_ALLOCATION latch. The
REDO_COPY latch is granted to a user when the size of his or her entry
is greater than the _LOG_
SMALL_ENTRY_MAX_SIZE parameter in the
initialization file. If you see REDO_
ALLOCATION latch contention, decrease the
value of _LOG_SMALL_ENTRY_MAX_
SIZE. If there is more than one user that
requires the REDO_COPY latch, you get contention on single-CPU
systems. The number of REDO_COPY latches is limited to twice the
number of CPUs on the system. If you have a single CPU, only one is
allowed. It is normal to see high contention for this latch on
single-CPU systems, and there is nothing the Remote DBA can do to increase
the number of REDO_COPY latches. However, even on single-CPU systems,
you can force Oracle to prebuild redo entries, thereby reducing the
number of latches required. This is accomplished by setting the _LOG_ENTRY_
PREBUILD_THRESHOLD entry in the initialization file higher. On
multiple-CPU systems, increase the number of REDO_COPY latches to
twice the number of CPUs.
In the second half of the report,
statistics from the caches that affect redo operations are shown.
Let’s look at what these numbers tell us. The most important of the
listed statistics are redo blocks written, redo entries linearized,
redo small copies, and redo writes.
* redo blocks written is useful when two
entries are compared for a specified time period. This will indicate
how much redo is generated for the period between the two checks.
* redo small copies tells how many times the
entry was effectively written on a redo allocation latch. This
indicates that a redo copy latch was not required for this entry. This
statistic should be compared with the redo entries parameter. If there
is close to a one-to-one relationship, then your system is making
effective use of the redo allocation latch. If there is a large
difference, then the LOG_SMALL_ENTRY_MAX_SIZE INIT.ORA parameter
should be increased. If the LOG_SIMULTANEOUS_COPIES parameter is 0,
this value is ignored.
* redo writes is the total number of redo
writes to the redo buffer. If this value is too large compared to the
redo entries parameter value, then the Remote DBA should tune the INIT.ORA
parameters mentioned in the previous sections to force prebuilding of
the entries. If the entries are not prebuilt, the entry may require
several writes to the buffer before it is fully entered; if it is
prebuilt, it requires only one.
* redo log space wait is the statistic that
tells you if users are having to wait for space in the redo buffer. If
this value is nonzero, increase the size of the LOG_
BUFFER in the initialization file.
* redo buffer allocation retries is the
statistic that tells the Remote DBA the number of repeated attempts needed to
allocate space in the redo buffer. If this value is high in comparison
to redo entries, it indicates that the redo logs may be too small and
should be increased in size. Normally, this value should be much less
than the redo entries statistic. In the example, it has a value of 5
compared to the entry’s value of 1044; this is satisfactory.
* redo size tells the total number of redo
bytes generated since the database was started. Comparison of two
readings will give the amount generated over time. This value can then
be used to determine if the log switch interval is proper. Too many
log switches over a small amount of time can impair performance.
Use the following formula to look at
log switches over time:
(X / (dN / dt)) / interval of concern
where:
X is the value of LOG_CHECKPOINT_INTERVAL
or size of the redo log in system blocks.
dN is the change in the redo size over the
time interval.
dt is the time differential for the period
(usually minutes).
Once the number of log switches is
known, the Remote DBA can use this value to determine the size of redo logs
based on system I/O requirements. If you need to reduce the number of
log switches, increase the redo log size; of course, this may impact
system availability since it takes longer to write out a large redo
log buffer than a small one to disk. A balance must be struck between undersizing the redo logs and taking a database performance hit and
making the logs too large and taking an I/O hit.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
