Hands-On 06 (Monitor and size the Redo Log buffer)

As a DBA, you are responsible to monitor and resize the Redo Log buffer in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Monitoring the Redo Log Buffer memory size

Re-sizing the Redo Log Buffer memory size

Checking the Redo allocation entries ratio

Checking waiting sessions

Checking for an Online Full Redo Log file

Using the V$SESSION_WAIT view

-- Hands-On 06 (Monitor and size the Redo Log buffer)
-- Monitor and size the redo log buffer.
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
col username format a10
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to: monitor
-- and re-size the REDO LOG BUFFER, check the redo buffer
-- allocation entries, calculate the redo allocation buffer
-- entries ratio, check sessions waiting for log buffer
-- space, and check for a full Online Redo Log file.

-- Connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Important Notes:

-- The redo entries in the redo log files are used for 
-- database recovery.

-- The buffer is usually flushed by reaching: 
-- one third of the redo log buffer size,
-- frequent COMMITs, and 
-- every 3 seconds.

-- Notice that if you have a fast processor and a slow disk, 
-- the Server Process may fill the redo log buffer faster than
-- the Log Writer can write the redo entries to the online 
-- Redo Log file, and you may have to increase the size of 
-- the Redo Log file to avoid such a contention.

pause
pause

CLEAR SCR
-- Keep your eyes on the redo buffer allocation entries.

pause

SELECT name, value
FROM v$sysstat
WHERE name = 'redo buffer allocation entries'
/

-- If you have a positive number, that means that you may have 
-- a problem.

-- Be sure that you have compared the above positive number
-- with the Redo entries and it should not be more than 1%.

pause

CLEAR SCR
-- Query the redo allocation buffer entries ratio.
-- Be sure that your ratio is not more than 1%.

pause

SELECT a.value/b.value "redo buffer entries ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'redo buffer allocation entries'
AND b.name = 'redo entries'
/

-- If the number is greater than 1%, you should 
-- increase the size of the Redo Log buffer. I would 
-- also check the checkpoint and size of the online 
-- redo log file.

pause

CLEAR SCR
-- Check to see if any other sessions are waiting for
-- log buffer space.

pause

SELECT sid, event, seconds_in_wait, state
FROM v$session_wait
WHERE event = 'log buffer space'
/

-- If the Log Buffer space waits exist, consider increasing the 
-- size of the redo log. Also I would check the speed of the 
-- disk that the Online Redo Log files are in.

pause

CLEAR SCR
-- Now, check to see if that Online Redo Log file is full and 
-- the server is waiting for the next Redo Log file.

pause

SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests'
/

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause