 |
|
Oracle Monitoring Redo Statistics
|
Chapter 6 Oracle Tablespace and File
Internals Scripts
Monitoring Redo
Statistics
There are no
views in Oracle that allow the user to look directly at a log file’s
statistical data. It is necessary
instead to derive statistics from redo log
and log writer process statistics. These statistics can be found 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 below. An example of the script’s output
is also shown.
rdo_stat.sql
SET PAGES 56
LINES 78 VERIFY OFF FEEDBACK OFF
ttitle "Redo
Latch Statistics"
SPOOL
rdo_stat
COLUMN
name FORMAT a30 HEADING Name
COLUMN
percent FORMAT 999.999 HEADING Percent
COLUMN
total HEADING Total
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
SEE CODE DEPOT FOR FULL SCRIPT
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
ttitle 'Redo
Log Statistics'
SPOOL
redo_stat
SELECT
name,
value
FROM
v$sysstat
SEE CODE DEPOT FOR FULL SCRIPT
ORDER BY
statistic#;
SPOOL OFF
SET LINES 24
FEEDBACK ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS
Here is a sample listing:
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
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
These numbers
require a little interpretation. Let’s look at what they mean and
how they can be used. The first
section of the report should be self-explanatory.
The redo logs use two latches, REDO ALLOCATION and REDO COPY.
As a general rule, if the percent statistic
(actually, the ratio of total misses to total gets) is greater than
10 percent, contention is occurring, and the way the redo logs are
done should be examined (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 the entry is greater
than the log_small_entry_max_size parameter in the initialization file. If there is redo_allocation
latch contention, decrease the value of _log_small_entry_max_size.
A system with a single CPU will show contention
if there is more than one user that requires the redo_copy latch. The number of redo_copy latches is limited to twice the
number of CPUs on the system. If there is only one CPU, only one
latch is allowed. It is normal to see high contention for this latch
on single-CPU systems, but there is nothing the DBA can do to
increase the number of redo_copy latches.
However, even on single-CPU systems, Oracle can
be forced to prebuild redo entries, thereby reducing the number of
latches required. Raising the value of the _log_entry_prebuild_threshold entry in the initialization file
does this. On multiple-CPU systems, increase the number of redo_copy latches to twice the number of CPUs.
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link:
|
