Tuning Instance   «Prev  Next»
Lesson 4 View redo log activity for specific tasks
Objective Investigate redo log activity for Oracle tasks.

View Redo Log Activity for Specific Tasks

Redo log stats

Viewing redo log statistics for individual tasks can be very useful in determining if redo log problems are slowing-down transactions. While there are complex SQL scripts that can be used to display redo activity for tasks, the easiest way to view redo activity for tasks is to utilize Oracle's Enterprise Manager (OEM) Top Session Monitor.
Oracle Enterprise Manager Top Session Monitor allows the DBA to view top processes sorted by any number of redo log statistics. Oracle sessions can be sorted in order of any number of redo log activities.

Redo log space requests

Redo log space requests are indicative that the log buffer is too small. Whenever Oracle must request additional space in the log buffer, Oracle may have to wait for the space to become available. Hence, you want to pay careful attention to this parameter.

Redo log space wait time

Redo log space wait time is another indicator that the log buffer is too small. High redo log space wait times indicate that Oracle is spending too much time acquiring redo log buffer storage. While the redo log space requests and redo log space wait time are extremely important, it is important to know all the tuning metrics.

Mouse over the following image below to observe how we can use OEM top sessions to view tasks, sorted by redo log activity:
Oracle redo blocks written 
select * from v$sysstart
where name
in(
  'redo blocks written',
  'redo buffer allocation retries',
  'redo log space wait time')
;
STATISTICS# NAME
  1. The foremost statistic is the redo blocks written value.This is most useful for finding how much redo is generated for an operation or time interval
  2. This indicates the time the user processes had to wait to get space in the redo file.
  3. 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.

While these sorting statistics may be useful for some tuning, we always need to remember that Oracle will manage redo log activity, and we have very little control other than changing the values of the redo log init.ora parameters.The next lesson looks at the redo log init.ora parameters.