Diagnose OracleRedoLog performance problems

Among the many factors that can affect Oracle performance, RedoLog-related factors are, to some extent, the most important and most noteworthy. Because in An OLTP system

Among the many factors that can affect Oracle performance, Redo Log-related factors are, to some extent, the most important and most noteworthy. Because in An OLTP system

I. Performance adjustment objectives of Rodo Log:

Among the many factors that can affect Oracle performance, Redo Log-related factors are, to some extent, the most important and most noteworthy. In an OLTP system, Oracle uses a variety of technologies and excellent design to implement most operations in the memory to maximize performance. Therefore, many background processes in Oracle and most of the operations of user processes are memory operations, and these operations will try to lag disk I/O operations as far as possible through the delayed write technology. However, there are some exceptions in these operations, the most obvious of which is the Redo Log operation.

In Oracle, the Redo Log operations are mainly completed by the LGWR process. This process can be said to be the busiest process in all background processes of Oracle, and this process may require frequent I/O operations, this is because Oracle must ensure that online redo logs are reliably written to log files for data security considerations, so that data can be effectively restored in the event of a crash, however, the real data may be written to the data file at some time delay. This feature is unique in various background processes of Oracle. In addition, LGWR is globally unique, that is, an instance can only have one active LGWR process. Frequent I/O operations can easily lead to LGWR process competition. Due to the special position of LGWR in the structure design of Oracle instances, once the LGWR performance bottleneck occurs, the performance impact on the entire system will be extremely serious, data security is also a potential threat.

Therefore, as the daily database management of Oracle, we should pay close attention to this Part, identify problems as soon as possible, and make adjustments as soon as possible. The goal of the adjustment is to make the Log_Buffer size moderate (not too large or too small), to meet the user's process needs, whenever the system load increases significantly, you should consider adjusting its size. For example, because the number of fixed users in the current system has increased from 10 thousand to 30 thousand, you should pay attention to the size of Log_Buffer. In addition, the log file size must be moderate, and the number of log files in the log group is appropriate. This does not affect the performance of Log Files written by LGWR, and does not cause competition for writing between log files, disk competition cannot be triggered when logs are switched to archives.

Ii. Monitoring and troubleshooting:

During Redo Log monitoring, there are two main aspects: the waiting conditions of Log buffer space usage and the distribution of Log buffer data slots. Through monitoring these two aspects and using some troubleshooting methods, we can usually find a large number of problems.

(1) Waiting condition of log buffer space usage:

You can query v $ session_wait to monitor the waiting status of the log buffer space and query it using the following SQL statement:

Select sid, event, seconds_in_wait, state

From v $ session_wait

Where event = 'Log buffer space % ';

In the above query, you can analyze the problem by observing the value of seconds_in_wait. This value can display the following problems: wait caused by slow log switching, wait caused by slow LGWR writing, and wait caused by disk competition caused by log file writing.

These waits may occur due to the following problems:

1. There is disk competition when writing log files:

This is often seen in the case of log switching. This problem is caused by improper planning of the log file group, slow write speed on the disk where the log files are stored, or improper disk RADI, if you suspect that the village is in these circumstances, you can monitor it using the following statement:

Select event, total_waits, time_waited, average_wait

From v $ system_event

Where event like 'Log file switch completion % ';

You can analyze the problem by observing the total_waits, time_waited, and average_wait values. If these values are too high (note that the values are "too high" and the standards are different for different systems, you need to analyze them in detail ), the above problems exist. You can solve this problem by taking the following measures:

. Allocate members of the same log file group to different disks to reduce the competition caused by log writing, log switching, and log archiving;

. Store log files on a fast disk as much as possible;

The RADI type should be reasonably selected to strip the disk. Generally, do not use RADI5 as the radi type of the log file disk. Generally, RADI10 is recommended;

. You can increase the redo log file size to delay LOG switching. The following is a method to increase the LOG file size;

Assume that three small redo log files exist. The following is an example in UNIX:

Step 1: add three large redo logfiles to the database

SVRMGRL> alter database add logfile group 4

('/Opt/oradata/app/redo04.log ',

'/Ora_bak/oradata2/redolog/redo04.log') size 16 M reuse;

SVRMGRL> alter database add logfile group 5

('/Opt/oradata/app/redo05.log ',

'/Ora_bak/oradata2/redolog/redo05.log') size 16 M reuse;

SVRMGRL> alter database add logfile group 6

('/Opt/oradata/app/redo06.log ',

'/Ora_bak/oradata2/redolog/redo06.log') size 16 M reuse;

Step 2: manually create a log switch to make the new redo logfile take effect.

SVRMGRL> alter system switch logfile;

This operation can be performed once or several times to make the old redo logfile invalid state.

Step 3: delete the old redo logfile.

SVRMGRL> alter database drop logfile group 1;

SVRMGRL> alter database drop logfile group 2;

SVRMGRL> alter database drop logfile group 3;