Performance

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:

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.
SQL> CONNECT system/manager AS SYSDBA
 

REDO log entries
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.
 

View REDO log buffer allocation entries
Keep your eyes on the redo buffer allocation entries.
SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'redo buffer allocation entries'
/
Note that 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%.
 

REDO log buffer entries HIT Ratio
Query the redo allocation buffer entries ratio. Be sure that your ratio is not more than 1%.
SQL> 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. You should also check the checkpoint and size of the online redo log file.
 

Check to see if any other sessions are waiting for log buffer space.
SQL> 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 you should check the speed of the disk that the Online Redo Log files are in.
 

REDO log space requests
Now, check to see if that Online Redo Log file is full and the server is waiting for the next Redo Log file.
SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests'
/
 

To resize the Online Log Buffer do the following:

SQL> ALTER SYSTEM SET log_buffer=###M scope=SPFILE; -- ### is a integer number that you want to assign to your log buffer.

Questions:

Q: How do you monitor the redo log buffer memory size?

Q: How do you re-size the redo log buffer memory size?

Q: How do you monitor the redo allocation entries ratio?

Q: How do you monitor a waiting session in the redo log buffer?

Q: How do you monitor your online full redo log file?

Q: Describe the V$SESSION_WAIT view?

Q: Describe the redo log entries.

Q: What is the redo log files used in the Oracle database?

Q: How often does the redo log buffer flush in to the Online redo log files?

Q: When do you have to increase a size of a redo log buffer?

Q: What is an acceptable range for the redo log buffer entries ratio?

Q: How do you monitor a waiting session to obtain a log buffer space?

Q: What should you do if the log buffer space waits exist in the Red Log buffer memory?

Q: How do you monitor if a server is waiting for the next redo log file?