REDO LOG WRITER PERFORMANCE:
Redo Writer Latches : Before a user process performs a block change it must
create the redo record in the log buffer. The allocation of space in the
redo log buffer is controlled by the allocation latch. The user process
must acquire this latch and may copy while holding it if the size of the
entry is less than the parameter "LOG_SMALL_ENTRY_MAX _SIZE". On
multi-processing systems, the copying into the buffer can be optimized by
acquiring one of the several redo_copy latches. If the size of the redo is
more than LOG_SMALL_ENTRY_MAX_SIZE than the user process must obtain a
redo copy latch and after obtaining redo copy latch the user process can
release the allocation latch and then copy the redo while holding the redo
copy latch.
On computers with multiple CPU's, multiple Redo Copy latches allow
multiple processes to copy entries to the redo log buffer concurrently.
The number of redo copy latches is determined by the init.ora parameter
LOG_SIMULTANEOUS_COPIES. If there is contention for the redo
copy latch then more latches should be added by increasing the value of
LOG_SIMULTANEOUS_COPIES.
The performance of the redo writer process along with and effectiveness
of init.ora parameters affecting the LGWR can be measured by looking at
several statistics. Some tuning issues were discussed for the Latch
option. The most important statistics are 'redo blocks written', 'redo
entries linearized', 'redo small copies' and 'redo writes'.
Large values associated with any of the above statistics are meaningless
unless compared with the corresponding Redo Latch parameters. Poorly
tuned redo parameters often result in latches being held for longer
periods and hence resulting in poor performance.
'redo blocks written' is useful in finding how much redo is generated for
an operation or time interval. It may however not have much tuning use.
Redo_log_space_wait_time : This indicates the time the user processes
had to wait to get space in the redo file.
Redo_log_space_request : Reflects the number of times a user process
waits for space in the redo log buffer. This value ideally should be 0. If
this value is non zero then the size of the log buffer should be increased
by increasing the value of LOG_BUFFER initialization parameter.
Redo_buffer_allocation_retries : This indicates the number of repeated
attempts to allocate space in the redo buffer. A value indicates that the
redo writer is falling behind possibly due to a log switch. Although log
switch is a normal event, frequent log switches indicate improper sizing
of the redo log files. In this output, the value is 5 which is low compared
to the number of redo entries 28481.
Redo_entries/Redo_size : This statistic is useful in sizing the redo log
files and planing the checkpointing frequency. Log switches also post
the archiver and file copy is done to archive the log file. Redo size tells
the number of bytes of redo generated and redo entries is the number of
redo records created.
These statistics should be analyzed for the given time interval.
For example:
Redo Size : N Bytes
Time interval : tend- tstart = t (minutes say)
redo generation per minute = ( N / t )
log switch, desired interval can be determined by considering the impact
of archiver file copy and checkpoints on the system. Log switches force
checkpoints to occur and also if archiving is enabled the archiver
performs a copy of the file. These two operations involve activity for the
background processes and can affect the database response if the sizing is
not done properly. Large redo log files reduce the number of log
switches but increase the archiver copy time degrading the user response
on single cpu systems. Frequent checkpointing may cause the users to
wait on buffers pinned by the DBWR.
A good time interval for log switch is where the cpu time used and the
I/O's on the devices involved is not large enough for users to be blocked
e.g. on a busy VAX 6420 a file copy of 30000 VMS blocks takes 30secs
and several thousand I/O's on a system with 30 users. Once the time
interval is known (say in mins), the log file size can be calculated by
simple multiplication with the redo generated per minute.
'redo writes' is the total number of redo writes to the redo buffer. It may
be useful to determine whether or not it is too large compared with 'redo
entries' statistics. in our example output, redo entries 28481 and redo
writes is 1907.
Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:930390548.22717.0.nnrp-13.9e984b29_at_news.demon.co.uk...
> Can you confirm that you are looking at the
> hit_ratio
> and not the
> nowait_hit_ratio
>
> The former is almost certainly irrelevant for the
> redo copy latch. You should find that the actual
> number of gets on this latch in 'willing to wait'
> mode is very small compared to the get in 'no wait'
> mode.
>
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Doug Cowles wrote in message <377406E8.EB2D13C8_at_bigfoot.com>...
> >Got .41 for redo copy latch hit ratio in BSTAT/ESTAT.
> >Any suggestions? (Or details on what happens with this latch?)
> >
> >- Dc.
> >
>
>
>
>
Received on Sun Jun 27 1999 - 02:47:48 CDT
