Relearn log Buffer

Processing and using the redo log Buffer

Configure and use redo log Buffer
Server Processes making changes to data blocks in the buffer cache generate redo data
Into the log buffer. lgwr begins writing to copy entries from the redo log buffer
The online redo log if any of the following are true:

The service process modifies the data in the buffer cache, generates redo data, and writes the data to the log buffer.

If the following conditions are met, lgwr will start to write data from redo log buffer to online redo log.

■ The log buffer becomes one third full.
■ Lgwr is posted by a server process invention Ming a commit or rollback.
■ Dbwr posts lgwr to do so.

Logbuffer is 1/3 full.

Lgwr is submitted or rolled back by the service process.

Dbwr asked the lgwr process to do this.

When lgwr writes redo entries from the redo log buffer to a redo log file or disk,
User processes can then copy new entries over the entries in memory that have been
Written to disk. lgwr usually writes fast enough to ensure that space is available in
The buffer for new entries, even when access to the redo log is heavy.

When lgwr writes redo information from the redo log buffer to the redo log file or disk, the user process

You can write new data to the memory to overwrite the old data written to the disk in the original memory.

Generally, lgwr is fast written to ensure that there is always space in the memory to write new data. Even if the redo log is busy.
A larger buffer makes it more likely that there is space for new entries, and also gives
Lgwr the opportunity to efficiently write out redo records (too small a log Buffer on
System with large updates means that lgwr is continuously flushing redo to disk so
That the log buffer remains 2/3 empty ).

 

A larger log buffer can leave more space for Data Writing and give lgwr an efficient opportunity.

Write redo information (if the log buffer in a system is too small, this will mean that lgwr will keep redo

Information is written from the memory to the disk, so that the log buffer will keep 2/3 blank)
On systems with fast processors and relatively slow disks, the processors might be
Filling the rest of the buffer in the time it takes the redo log writer to move a portion
The buffer to disk. A larger log buffer can temporarily mask the effect of slower Disks
In this situation. Alternatively, you can do one of the following:

In some systems, there are fast processors and relatively slow disks, and these processes may be able to put information in redo lgwr

When writing data to a disk, fill up the remaining buffer. Therefore, a larger log buffer will temporarily mask

Disks are slow. In addition, you can choose one of the following operations:

■ Improve the Checkpointing or archiving process
Improve the Performance of log writer (perhaps by moving all online logs to fast
Raw devices)
Good usage of the redo log buffer is a simple matter:
■ Batching commit operations for Batch jobs, so that log writer is able to write redo
Log entries efficiently
■ Using nologging operations when you are loading large quantities of data
The size of the redo log buffer is determined by the initialization parameter LOG _
Buffer. The log buffer size cannot be modified after instance startup.
Figure 7-redo log Buffer

 

Improves the efficiency of checkpoint or archive Processes

Improve lgwr performance (for example, set all online logs to faster bare devices)

Make good use of redo log Buffer:

Batch jobs are submitted in batches, so that lgrw can efficiently write redo log entries.

Use nologging to load a large amount of data. Redo log buffer size

It is determined by the initialization parameter log_buffer.

However, the log buffer size cannot be modified after the instance is started.

 

Log buffer space
This event occurs when server processes are waiting for free space in the log buffer,
Because all the redo is generated faster than lgwr can write it out.

Log buffer space
This event will occur when the service process waits for free space in the log buffer. Because the redo log generation speed
It is slower to write than lgwr.

Actions
Modify the redo log buffer size. If the size of the log buffer is already reasonable, then
Ensure that the disks on which the online redo logs reside do not suffer from I/O
Contention. The log buffer space wait event cocould be indicative of either disk I/O
Contention on the disks where the redo logs reside, or of a too-small log buffer. Check
The I/O profile of the disks containing the redo logs to investigate whether the I/O
System is the bottleneck. If the I/O system is not a problem, then the redo log Buffer
Cocould be too small. increase the size of the redo log buffer until this event is no longer
Significant.

Action:
Modify the redo log buffer size. If the log buffer size is reasonable, make sure that the online log is in
Disk I/O is not tight. The log buffer space wait event may be exposed due to the high I/O pressure on the disk where the redo log is located,
Or the log buffer is too small. If the IO system is not a problem, the redo log buffer may be too small. Then we need
Increasing the size of redo log buffer does not affect the event (log buffer space wait event.

Log File Switch
There are two wait events commonly encountered:
■ Log File switch (archiving needed)
■ Log File switch (checkpoint incomplete)
In both of the events, the lgwr is unable to switch into the next online redo log, and
All the commit requests wait for this event.

Log Switching
We often encounter the following waiting events:
Log File switch (archive required)
Log File switchover (incomplete checkpoint)
In the above two events, lgwr can switch to the next online redo log, and all commits
All events are waiting for this event

 

Actions
For the log file switch (archiving needed) event, examine why the archiver is
Unable to archive the logs in a timely fashion. It cocould be due to the following:

Operation:
For Log File switch (archiving required) events, check the reason why the archiving process cannot archive logs. For a timely reason,
It may be caused by the following reasons:

 

■ Archive destination is running out of free space.
■ Archiver is not able to read redo logs fast enough (contention with the lgwr ).
■ Archiver is not able to write fast enough (contention on the archive destination, or
Not enough arch processes). If you have ruled out other possibilities (such
Slow disks or a full archive destination) consider increasing the number of arcn
Processes. The default is 2.

The Archiving destination has no free space;
The archiving process is not fast enough to read redo logs (caused by heavy lgwr pressure)
The archive process write speed is not fast enough (due to pressure on the archiving destination, or insufficient archiving process)
If you send other possibilities (such as slow disks or full archiving paths), consider adding
The number of archiving processes. (The default number of archiving processes is 2)

 

■ If you have mandatory remote shipped archive logs, check whether this process is
Slowing down because of network delays or the write is not completing because
Errors.
If you force remote shipping of archived logs, check if the process slows down because of network latency or
The write process cannot be completed due to errors.

Depending on the nature of bottleneck, you might need to redistribute I/O or add
More space to the archive destination to alleviate the problem. For the log file
Switch (checkpoint incomplete) event:
Due to the bottleneck, you may need to split up Io or add more space for archiving purposes to ease this problem,
For log switching (incomplete archiving) Events:

■ Check if dbwr is slow, possibly due to an overloaded or slow I/O system. Check
The dbwr write times, check the I/O system, and distribute I/O if necessary. See
Chapter 8, "I/O configuration and design ".

Check whether dbwr is slow, possibly because of overload or slow Io. Check the number of dbwr writes,
Checks the I/O system and disperses Io if necessary. See Chapter 1: "I/O configuration and design"

■ Check if there are too few, or too small redo logs. If you have a few redo logs or
Small redo logs (for example two x 100 k logs), and your system produces enough
Redo to cycle through all of the logs before dbwr has been able to complete
Checkpoint, then increase the size or number of redo logs. See "Sizing redo log
Files "on page 4-3

Check whether the redo log is too small or too small. If you only have few or few logs
(For example, 2 kb logs), and your system generates enough redo logs to switch between all logs,
However, dbwr does not have enough capability to complete the checkpoint operation, and then increases the size and number of online logs.

Log File sync
When a user session commits (or rolls back), the session's redo information must be
Flushed to the redo logfile by lgwr. The server process implements the commit or
Rollback waits under this event for the write to the redo log to complete.

Log Switching
When a user session is submitted (or rolled back), the session redo information must be written into the redo log by lgwr.
After the log is written, the commit or rollback operation ends.

Actions
If this event's Waits constitute a significant wait on the system or a significant amount
Of Time waited by a user experiencing response time issues or on a system, then
Examine the average time waited.

Operation
The wait of this event constitutes a large proportion of the system wait events or takes a long time,
Therefore, we need to check the average waiting time.

If the average time waited is low, but the number of waits are high, then
Application might be committing after every insert, rather than batching commits.

If the average waiting time is slow but the waiting frequency is high, the application may
The insert operation is submitted later, instead of batch submission.

Applications can reduce the wait by committing after 50 rows, rather than every row.

Applications can reduce wait events by submitting each 50 rows. Instead of submitting each row once.

If the average time waited is high, then examine the session waits for the log writer
And see what it is spending most of its time doing and waiting for. If the waits are
Because of slow I/O, then try the following:

If the average waiting time is long, check the session waiting for lgwr and check the maximum time range of the session.
What are you doing? If the wait is because I/O is too slow, check the following situation.

 

■ Reduce other I/O activity on the disks containing the redo logs, or use dedicated
Disks
■ Alternate redo logs on different disks to minimize the effect of the archiver on
Log writer.
■ Move the redo logs to faster disks or a faster I/O subsystem (for example, switch
From RAID 5 to raid 1 ).
■ Consider using raw devices (or simulated raw devices provided by disk vendors)
To speed up the writes.
■ Depending on the type of application, it might be possible to batch commits
Committing every n rows, rather than every row, so that fewer Log File syncs are
Needed.

Reduce the IO load on the disk that contains redo logs, or use a separate disk.
Use redo logs that can be stored on different disks to reduce the impact of the archiving process on lgwr.
Move redo logs to faster disks or faster Io subsystems (for example, from RAID 5 to raid1)
Consider using bare devices (or simulated bare devices provided by the disk manufacturer) to accelerate write operations.
Based on the application type, we may be able to submit data in batches. For example, each n rows are submitted once instead of each row,
In this way, we only need less log file synchronization.

 

Log buffer statistics
The statistic redo Buffer Allocation retries reflects the number of times a user
Process waits for space in the redo log buffer. This statistic can be queried through
Dynamic Performance view v $ sysstat.

Log buffer statistics
The redo Buffer Allocation retries statistics reflect the number of times the user process waits for the idle space of the redo log buffer.
This statistics can be queried through the dynamic view v $ sysstat.

Use the following query to monitor these statistics over a period of time while your
Application is running:
Select name, Value
From v $ sysstat
Where name = 'redo Buffer Allocation retries ';

When your application is running, use the following query to check statistics,
Select name, Value
From v $ sysstat
Where name = 'redo Buffer Allocation retries ';

The value of redo Buffer Allocation retries shoshould be near zero over
Interval. If this value increments consistently, then processes have had to wait
Space in the redo log buffer.
During the interval, the value of the number of redo buffer space allocations should be 0. If
The number is constantly increasing, so the process must wait for the space in the redo log buffer.

The wait can be caused by the log buffer being too small
Or by Checkpointing. increase the size of the redo log buffer, if necessary, by changing
The value of the initialization parameter log_buffer.
This wait event may be because the log buffer is too small or because of the Checkpointing event. We recommend that you add
Redo log buffer size. If necessary, modify the initialization parameter log_buffer.

The value of this parameter is expressed in bytes. Alternatively, improve the Checkpointing or archiving process.
Another data source is to check whether the log buffer space wait event is not
Significant factor in the wait time for the instance; if not, the log buffer size is most
Likely adequate.
The value is measured in bytes. Alternatively, we can improve the checkpoint or archiving process capabilities.
Another data source is to check whether the log buffer space wait event has an influence factor in the instance wait event.
If not, the log buffer size should be suitable.

Log_buffer
Property description
Parameter type Integer
Default Value 512 KB or 128 kb * cpu_count, whichever is greater
Modifiable No
Range of values Operating System-dependent
Basic No

Log_buffer specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. redo log entries contain a record of the changes that have been made to the database block buffers. the lgwr process writes redo log entries from the log buffer to a redo log file.

In general, larger values for log_buffer reduce redo log file I/O, fig if transactions are long or numerous. In a busy system, a value 65536 or higher is reasonable.

 

|

Redo log Cache

When the database processes user transactions, it also records the transaction information in the log cache, and writes the transaction information to the disk at a certain time, which is the role of redoing the log cache. Under the following conditions, the Database Log read/write process lgwr writes the redo log cache content to the online redo log:
Full Log cache 1/3
The user executes the commit and rollback operations.
Lgwr execution before dbwr execution

In this way, the data in the log cache will remain 2/3 idle in an ideal state. Once lgwr writes a disk, subsequent log information can overwrite the disk space.
Assuming that the log data is generated faster than the disk write speed at a certain moment, the remaining 2/3 space in the log cache will be used. Therefore, a large log space is maintained.
This will facilitate the writing of log data to the cache and reduce the frequency of writing disks due to the log being full of 1/3.

Because the log write disk will also occur when the user submits the log, in a busy system, log write operations constitute the entire database I/O operation is extremely important.
Components. Optimizing write operations for online redo logs is an important aspect of optimizing the entire database application system. The following methods are generally used:

Adjusting the Checkpoint Process in Oracle
Improve the archiving mode and optimize the archiving process in the archive mode in Oracle
Use raid0 + 1/raid1 + 0 storage devices at the system (Aix) level
Use a striped logical volume at the system (Aix) level (for example, # mklv-s128k)
At the system (Aix) level, use the logical volume stored in the middle of the disk (for example, # mklv-a c) to store logs.

Optimize the lgwr process and place the redo log file in a disk area with fast write capability, for example, a bare device that uses the Raid 1 + 0 mode on the disk group.
When programming, you can reduce the number of submitted operations. During batch processing, the transaction is also submitted in batch. But pay attention to its side effects-this is possible
It causes severe rollback segment pressure and "too old snapshot" errors.

You can also consider using SQL statements with the nologging method, but also pay attention to its side effects-to assess whether the System Backup recovery solution supports this.
The size of the redo log cache is determined by the initialization parameter LOG buffer. This parameter is a static parameter and needs to be restarted. Modify log Cache
The procedure is as follows:

SQL> alter system set log_buffer = 10000000 scope = spfile;

What are the general settings of log cache? Oracle uses the following formula to set the initial state. We can perform system statistics when the application load is normal.
And data analysis to determine whether to optimize.
Max (0.5 m, (128 K * Number of CPUs ))

Therefore, for a 4-CPU system, we can set the log cache to KB; for an 8-CPU system, 1 MB is more appropriate.

The statistical parameter "redo Buffer Allocation retry" (log cache allocation retries) reflects the transactions performed by the user process waiting for redo log cache allocation
The number of spaces. Obviously, a relatively high statistical value may mean that the log cache should increase. This statistical data is queried through the dynamic performance view v $ sysstat.
Statement to query the log cache allocation retry statistics within a period.

SQL> select name, Value
2 from V $ sysstat
3 where name = 'redo Buffer Allocation retries ';

NAME value
--------------------------------------------------------------------------
Redo Buffer Allocation retries 0

The absolute value of the returned data is of little significance. However, we can query the statistical data of the typical interval and check the increase to see whether there are log retries.
Continue to rise.

 

If so, adjust the initialization parameter log_buffer and also consider the necessity of adjusting the checkpoint. Generally, after the checkpoint is sent, the log data cannot be written to the disk in time,
Slow log space acquisition. You can either reduce the number of checkpoint occurrences and try to put the log file on a relatively fast disk. Pay attention to the archiving speed,
Whether it can keep up with the log generation speed.