Shulou(Shulou.com)06/01 Report--

Tuning of other builds

The resolution of the log file sync problem is a systematic project, and in addition to the tuning described in the previous section, let's see if tuning is also needed for other builds. For example, commit itself, as a redo record, also needs to be copied into log buffer. If the log buffer is too small and has no space at this time, will it not affect the efficiency of submission to a certain extent? for example, before Lgwr refreshes the log, you need to make sure that all the allocated buffer has been copied. If you find that there are still processes holding redo copy latch, it means that there are still processes copying logs to log buffer. Then this time will also cause the submission to slow down.

Tuning of LOG BUFFER

After 10 gigabytes, the LOG BUFFER is generally large, usually 1 to more granules sizes. Unless you see more log buffer space waiting events, you do not need to resize the log buffer.

Tuning of redo-related latch

L redo copy latch: used only to track whether a process is copying data into the log buffer. Before lgwr can actually start writing, it must wait for the relevant process to finish copying, during which time lgwr will wait for LGWR wait for redo copy to wait. The number of processes that can be copied to log buffer at the same time is determined by _ log_simultaneous_copies. Keep the default value unless an obvious redo copy latch wait is observed.

L redo allocation latch: protection processes are used when allocating space in redo buffer to ensure that the space allocated to each other between processes does not overlap. Before version 9.2, because there was only one redo allocation latch, it was easy to cause the true use of this latch. After version 9.2, according to the number of host CPU, log buffer has been split into multiple sub-LOG BUFFER, and each sub-buffer has a corresponding redo allocation latch, which greatly alleviates redo allocatoin latch contention. Unless you see obvious redo allocation latch contention, there is no need to adjust the number of log buffer. After 10G, with the emergence of private redo and IMU, each private redo is protected by a private redo allocation latch, which further reduces redo allocation latch contention.

Redo writing latch: this latch protects a flag bit. After the process acquires the latch, it modifies the flag bit, such as changing 0 to 1, which means that the lgwr is writing. In this way, the subsequent submission process, after obtaining the latch, reads the flag bit to know whether the current LGWR is being written or not, avoiding a lot of unnecessary repeated notifications.

It is generally not necessary, unless their related wait has been brought to your attention, and various versions of ORACLE have been optimizing the acquisition and release of related latch, such as redo allocation latch, which has been done very efficiently.

New tuning methods

Before 10G, when a transaction commits, you must wait for Lgwr to brush the log before you can continue to do anything else, that is to say, you must meet the conditions for transaction persistence. Students who have studied other databases may be strange when learning ORACLE, because databases such as MYSQL and MONGODB support asynchronous refresh of logs. I think ORACLE launched this function so late. Most of the customers who still use ORACLE are in finance, securities and other industries, which have zero tolerance for data loss, so they have no need for this. It was not until 10GR1 Oracle quietly introduced a parameter: commit_logging, which allows transactions to refresh the log synchronously when they commit, but to trigger it when appropriate. This parameter can be combined in four ways:

Commit write [batch | immediate] [wait | nowait]

When the 10GR2 version was released, this parameter was split into two parameters, commit_logging,commit_write. I personally think that the parameters after 10GR2 split can more accurately express the intention of the parameters. Let's first focus on the parameter commit_write. Its parameter value can be wait/nowait, which means that when the foreground process commits a transaction, it does not notify LGWR to refresh the log. Wait is a notification, and the foreground process will wait for log file sync. Nowait does not notify, just wait for other operations to trigger lgwr to write logs (such as 3 seconds, 1m size, 1amp and 3full). If your business does not require high data consistency and does not require the D of ACID, in order to improve the number of transactions and performance, you can choose commit_write as the nowait mode. Before 10G, the D of ACID must be satisfied, that is, when the foreground process submits, it must wait for LOG FILE SYNC and wait for LGWR to refresh the log to disk. Let's take a look at the commit_logging parameter. The optional value of the parameter is batch/immediate, which is extremely easy to be misunderstood, making people think that batch means to control whether the transaction is packaged and committed in the way of group commit, while immediate means to let the transaction commit one by one and refresh the log buffer one at a time, but this is not the case in real time!

Immediate compared to batch, the change vector of commit (the transaction slot that modifies the rollback segment header) will be generated as the redo record of stand alone (separate), in the same way that 9i commit logs. In batch mode, the commit change vector is recorded by merging it into the change vector generated by the transaction. As a redo record, this batch mode depends on whether to use private redo and IMU. If the private redo and IMU are turned off, the setting of batch has no effect.

Let's dump log file insert into a values (1111) / commit; to explain the difference between batch/immediate methods:

DUMP LOG FILE: enable private redo and IMU, and set commit_logging to immediate,commit to generate logs as separate redo record, with a total of 2 redo record. The second redo record is generated by commit, as shown in the bold section (OP:5.4, which represents the modification of the UNDO segment header).

REDO RECORD-Thread:1 RBA: 0x00044d.00000002.0010 LEN: 0x0230 VLD: 0x05

SCN: 0x0000.041b921c SUBSCN: 1 06/25/2013 11:27:32

(LWN RBA: 0x00044d.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.041b921c)

CHANGE # 1 TYP:0 CLS:51 AFN:3 DBA:0x00c04c80 OBJ:4294967295 SCN:0x0000.041b91d1 SEQ:1 OP:5.2 ENC:0 RBL:0

Ktudh redo: slt: 0x0016 sqn: 0x00002bee flg: 0x0012 siz: 136 fbi: 0

Uba: 0x00d1a78d.0068.2c pxid: 0x0000.000.00000000

CHANGE # 2 TYP:2 CLS:1 AFN:9 DBA:0x024002c5 OBJ:15750 SCN:0x0000.041b916a SEQ:1 OP:11.2 ENC:0 RBL:0

. Omit

REDO RECORD-Thread:1 RBA: 0x00044d.00000004.0010 LEN: 0x00d0 VLD: 0x05

SCN: 0x0000.041b921e SUBSCN: 1 06/25/2013 11:27:34

(LWN RBA: 0x00044d.00000004.0010 LEN: 0001 NST: 0001 SCN: 0x0000.041b921d)

CHANGE # 1 TYP:0 CLS:51 AFN:3 DBA:0x00c04c80 OBJ:4294967295 SCN:0x0000.041b921c SEQ:1 OP:5.4 ENC:0 RBL:0

Ktucm redo: slt: 0x0016 sqn: 0x00002bee srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00d1a78d.0068.2c ext: 104 spc: 2050 fbi: 0

DUMP LOG FILE: enable private redo and imu, set commit_logging to batch,commit as a change vector merged into the transaction's redo record, generated for commit as a redo record,change # 3.

REDO RECORD-Thread:1 RBA: 0x00044d.00000002.0010 LEN: 0x0230 VLD: 0x05

SCN: 0x0000.041b921c SUBSCN: 1 06/25/2013 11:27:32

(LWN RBA: 0x00044d.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.041b921c)

CHANGE # 1 TYP:0 CLS:51 AFN:3 DBA:0x00c04c80 OBJ:4294967295 SCN:0x0000.041b91d1 SEQ:1 OP:5.2 ENC:0 RBL:0

Ktudh redo: slt: 0x0016 sqn: 0x00002bee flg: 0x0012 siz: 136 fbi: 0

Uba: 0x00d1a78d.0068.2c pxid: 0x0000.000.00000000

CHANGE # 2 TYP:2 CLS:1 AFN:9 DBA:0x024002c5 OBJ:15750 SCN:0x0000.041b916a SEQ:1 OP:11.2 ENC:0 RBL:0

CHANGE # 3 TYP:0 CLS:51 AFN:3 DBA:0x00c04c80 OBJ:4294967295 SCN:0x0000.041b921c SEQ:1 OP:5.4 ENC:0 RBL:0

Ktucm redo: slt: 0x0016 sqn: 0x00002bee srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00d1a78d.0068.2c ext: 104 spc: 2050 fbi: 0

I feel that the commit_logging parameter has little effect, which may help to reduce the abnormal time of ACID, and the size of log volume is slightly reduced in batch mode.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.