|
"Whatever is
begun in anger ends in shame." - Benjamin Franklin
(1706-1790) |
Read
first then play the video:
PRF006(VIDEO)-Monitor
and size the Redo Log Buffer
Monitor and size the Redo
Log buffer
Introduction
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
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.
Waiting for REDO log buffer
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.
|
"Now, now my
good man, this is no time for making enemies." - Voltaire
(1694-1778) on his deathbed in response to a priest - asking
that he renounce Satan. |
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?
|