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

In this issue, the editor will bring you what the incremental checkpoint of Oracle refers to. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

First, why should there be checkpoints

Modified blocks are collectively referred to as dirty blocks in oracle. All the dirty blocks are strung together by a linked list and are called checkpoint queues. In buffer cache, each block has a buffer header abbreviated as BH, and in BH there is a ckptq entry, which records pointers to one block and the next block on the checkpoint queue. If a block is not in the checkpoint queue, its ckptq entry is empty. All the dirty blocks are strung into a two-way linked list through the ckptq item oracle. This two-way linked list is the checkpoint queue.

1, only dirty blocks will be in the checkpoint queue, and the ckptq of non-dirty blocks is empty.

2, when the block is changed for the first time, the block is immediately added to the checkpoint queue. If the dirty block in the checkpoint queue is modified again, it will not change its position in the checkpoint queue.

3, the order of dirty blocks in the checkpoint queue: according to point 2, all dirty blocks are arranged in the order of the time when they were changed for the first time. To be more precise: arrange according to the lrba of blocks.

What is rba? Lrba? Hrba

Rba is to redo the block address, for example, the user issued a update command, updated block A, block An is now a dirty block, oracle will generate a redo record for him. The location of this redo record in the redo log file is rba (redo block address). After a while, if: block An is still dirty, at this time. The user issues another command to update block A, which generates another redo record. The rba corresponding to the first update command to redo the record is called lrba (low rba) of block A, and the rba corresponding to the second update command is called hrba (high rba). In fact, according to the lrba, it is arranged in the order in which the blocks were modified for the first time.

Let's talk about the way DBWR writes dirty blocks. after having a checkpoint queue, dirty blocks are arranged according to the time order in which they become dirty for the first time. DBWR will be triggered to refresh dirty blocks along the order of checkpoint queues. There are several parameters in oracle to determine the length of checkpoint queues. There is another CKPT process that monitors the length of the checkpoint queue. When the length of the checkpoint queue reaches a certain limit, CKPT will notify DBWR to write dirty blocks. CKPT will calculate a Target rba (target rba) according to the setting of parameters and the speed and busy degree of Target rba. DBWR will flush all dirty blocks before Target rba to disk along the checkpoint queue. When CKPT notifies DBWR Target rba, CKPT's task ends. He won't wait for DBWR to finish writing all the dirty blocks before Target rba. Tell DBWR to write dirty blocks, which is one of the tasks of CKPT. CKPT has another task, which is to check the write progress of DBWR every 3 seconds. The first block of the checkpoint queue is called the checkpoint location. CKPT writes dirty blocks along the checkpoint queue. CKPT checks where the checkpoint queue is written along the checkpoint queue every 3 seconds, and sets this location to the checkpoint location. In other words, the blocks before the checkpoint location are all blocks that have been flushed to disk by DBWR. This 3-second work to check the progress of DBWR is also an important task for CKPT. CKPT records the checkpoint location in the control file every 3 seconds, as well as other information such as heartbeat. CKPT works every 3 seconds and CKPT triggers DBWR periodically. Together, these two operations are called incremental checkpoints.

The following is the information that CKPT writes to the control file every 3 seconds

SQL > alter session set events' immediate trace name controlf level 8'

The session has changed.

The details are as follows:

*

CHECKPOINT PROGRESS RECORDS

*

(size = 8180, compat size = 8180, section max = 11, section in-use = 0

Last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 2, numrecs = 11)

THREAD # 1-status:0x2 flags:0x0 dirty:89

Low cache rba 0x2ad.908.0) [checkpoint location] on disk rba 0x2ad.d2f.0) [the last rba to redo the record]

On disk scn: 0x0000.00237745 03amp 02amp 15:03:44 2008 [the last scn for redoing the record]

Resetlogs scn: 0x0000.0008297b 08/27/2007 09:51:58

Heartbeat: 648318959 [heartbeat] mount id: 1201288562

...

...

Most of this information can be seen through x$kcccp.

SQL > select CPDRT,CPLRBA_SEQ | |'. | | CPLRBA_BNO | |'. | | CPLRBA_BOF "Low

RBA ", CPODR_SEQ | |'. | | CPODR_BNO | |'. | | CPODR_BOF" On disk RBA ", CPODS,CPODT,CPHBT from x$kcccp |

CPDRT Low RBA On disk RBA CPODS CPODT CPHBT

--

35 686.124.0 686.220.0 2325376 03/02/2008 15:18:54 648319278

Description:

The CPDRT column is the number of dirty blocks in the checkpoint queue.

The CPODS column is the scn of on disk rba

The CPODT column is the timestamp of on disk rba

The CPHBT column is the heartbeat

The checkpoint location is a rba that points to a redo record that focuses on doing a log file. The corresponding information of the redo record before this location has been written into the data file, and the redo record after this location corresponds to the data block, which may still be in memory. If an instance crash occurs, you only need to find the checkpoint location in the log file, apply all the redo log files from here, and roll forward is completed. After the instance crashes, start the database again, and oracle reads the low cache rba in the control file, which is the checkpoint location. From here, the redo information is applied to the on disk rba. On disk rba is the last redo record of the redo log file on disk. If the rba of the redo record of a command is higher than on disk rba, it means that the redo record has not been written to the log file, and it is impossible to recover it in the event of a crash. On disk rba is the end point of the oracle roll forward operation. On disk means "on disk". Higher rba than this, all in log buffer, has not yet been written to the log file on disk. Therefore, it cannot be used for recovery. The following is an example of instance recovery:

The Table table has 2000 bytes per row, the block size is 8K, and each block can hold 3 rows of table. Issue 7 update commands as follows:

Update table set name=low (name) where id=1;-Block 1 RBA: 692.2998.10

Update table set name=low (name) where id=2;-Block 1 RBA: 692.3015.10

Update table set name=low (name) where id=4;-Block 2 RBA: 692.3024.10

Update table set name=low (name) where id=7;-Block 3 RBA: 692.3033.10

Update table set name=low (name) where id=3;-Block 1 RBA: 692.3102.10

Update table set name=low (name) where id=10;-Block 4 RBA: 692.3127.10

Update table set name=low (name) where id=13;-Block 5 RBA: 692.3136.10

After the above seven update commands, the status of each block is as follows:

Block 1

Block 2

Block 3

Block 4

Block 5

Lrba:692.2998.10

Hrba:692.3102.10

Lrba:692.3024.10

Hrba:692.3024.10

Lrba:692.3033.10

Hrba:692.3033.10

Lrba:692.3127.10

Hrba:692.3127.10

Lrba:692.3136.10

Hrba:692.3136.10

Lrba is the RBA when the block becomes dirty for the first time, and Hrba is the RBA that redoes the record when the information in the block is changed for the last time. Block 1 has been modified twice, Lrba and Hrba are different. Blocks 2 to 5 are modified only once, Lrba and Hrba are the same. The corresponding redo records are:

RBA:692.2998.10 (the redo record corresponding to the first update command)

Rollback segment header redo information rollback segment block redo information block 1 line 1 redo information

(specific information is omitted from the redo record below)

RBA:692.3015.10 (redo record corresponding to the second update command)

RBA:692.3024.10 (redo record corresponding to the third update command)

RBA:692.3033.10 (redo record corresponding to the fourth update command)

RBA:692.3102.10 (redo record corresponding to the fifth update command)

RBA:692.3127.10 (redo record corresponding to the sixth update command)

RBA:692.3136.10 (redo record corresponding to the seventh update command)

If blocks 1 and 2 are not dirty, blocks 3, 4 and 5 are still dirty, and all dirty blocks are listed as checkpoint queues in Lrba order, where the Lrba of the checkpoint queue header (in this case, block 3) is the checkpoint location, here is 692.3033.10. This value is recorded in the control file. If an instance crash occurs, Oracle will take 692.3033.10 from the control file and find block 3033 in the redo log No. 692. from here, all the redo logs will be applied until the end of the redo log file. The RBA that redoes the log file's most untailed redo record is also called On disk rba. From the checkpoint location, the redo record is applied to the On disk rba, which is the process of rolling forward.

The following parameters can be used to limit the length of the checkpoint queue:

1,fast_start_io_target

This parameter is used to indicate the total number of IO to be generated when Instance Recovery occurs in the database, which is estimated by the AVGIOTIM of v$filestat. For example, if a database needs to be recovered within 10 minutes after the occurrence of Instance Crash, assuming that the IO of OS is 500 per second, then the database will generate about 500106030000 IO when Instance Recovery occurs, that is, we will be able to set the fast_start_io_target to 30000.

2,fast_start_mttr_target

We can see from the above that fast_start_io_target is troublesome to estimate the location of checkpoints. in order to simplify this concept, oracle introduced a parameter called fast_start_mttr_target from 9i to indicate the time when Instance Recovery occurred in the database. In seconds, this parameter is easy for us to understand literally, where mttr is the abbreviation of mean time to recovery. As in the example above, we can set fast_start_mttr_target to 600. When fast_start_mttr_target is set, the parameter fast_start_io_target will no longer take effect, and the parameter fast_start_io_target has been abolished by oracle since 9 I.

3,log_checkpoint_timeout

This parameter is used to indicate the time interval between the location of the checkpoint and the end of the redo log, in seconds. By default, it is 1800 seconds. This parameter actually represents the longest time that a dirty block stays dirty. If it is set at 1800 seconds, no dirty block remains dirty after 1800 seconds. Set log_checkpoint_timeout to 1800 seconds.

Compared to fast _ start_mttr_target, it is also a time, but its time value represents the time it takes to complete the restore operation, that is, the time it takes to apply all logs from the last checkpoint to the end of the log. This parameter indicates the elapsed time from the last checkpoint to the end of the log. In the standard version, the minimum value of this parameter is 900.

4,log_checkpoint_interval

This parameter represents the location of the checkpoint and the number of blocks at the end of the redo log. Expressed as OS.

590% OF SMALLEST REDO LOG

The Oracle internal also actually sets the first 90% of the end of the redo log as the checkpoint location, which is not a parameter, but an event specified within oracle that triggers an incremental checkpoint. Strictly speaking, the above conditions do not control when checkpoints occur, but how many blocks there can be in the checkpoint queue. Among the first four parameters, oracle recommends using fast_start_mttr_target instead of the first fast_start_io_target.fast_start_mttr_target,log_checkpoint_timeout,log_checkpoint_interval and 90% OF SMALLEST REDO LOG can be used at the same time. Consider such a situation, if the above parameters that trigger incremental checkpoints are set, and at some point, these parameters are triggered together, but they may specify different Target RBA locations, oracle will consider the location closest to the end of the log as the checkpoint location, as shown in the following figure:

In this case, the location of the log_checkpoint_interval will be defined as the Target RBA of the next incremental checkpoint. After 9i, for checkpoint frequency, it is recommended that only fast_start_mttr_target. As needed, you can also set the maximum time for a dirty block to stay dirty by setting log_checkpoint_timeout, while the other two parameters

Fast_start_io_target,log_checkpoint_interval recommends that you no longer use it.

Oracle write dirty blocks are not necessarily written from the checkpoint queue. In the v$sysstat view, there are two items of information about physical writing. Physical writes and physical writes non checkpoint. In other words, oracle divides the writing of dirty blocks into two categories. One is to write through a checkpoint, and the other is to write without a checkpoint. I call it checkpoint irrelevant writing. For example, when a tablespace is offline, all dirty blocks belonging to the tablespace are written to the data file, but no checkpoints occur. There are other situations where checkpoint-independent writing will occur, which I will introduce in future experiments. A small experiment proves that checkpoints are independent of writing: in order to avoid the influence of checkpoints on the experiment, the frequency of checkpoints is set lower.

The command is as follows:

Alter system set fast_start_mttr_target=0

Alter system set log_checkpoint_timeout=3600

Step 1: observe the value of the current physical write before the experiment:

SQL > select * from v$sysstat where name='physical writes non checkpoint'

STATISTIC# NAME CLASS VALUE STAT_ID

67 physical writes non checkpoint 8 3738 2602029796

Step 2: start a transaction at random

SQL > update jj_10 set name='aa' where id=20

1 row has been updated.

Step 3: take the table in step 2 offline:

SQL > alter tablespace jj_ts_1 offline

The tablespace has changed.

Step 4: check the data view at this time:

SQL > select * from v$sysstat where name='physical writes non checkpoint'

STATISTIC# NAME CLASS VALUE STAT_ID

67 physical writes non checkpoint 8 3759 2602029796

After comparison, it was found that checkpoint-independent writes increased from 3738 to 3759. To observe the write through the checkpoint queue, set the checkpoint frequency a little higher:

Alter system set log_checkpoint_timeout=10

Step 1:

SQL > select * from v$sysstat where name='physical writes' or name='physical writes non

Checkpoint'

STATISTIC# NAME CLASS VALUE STAT_ID

62 physical writes 8 5822 1190468109

67 physical writes non checkpoint 8 3829 2602029796

The result obtained by subtracting physical writes non checkpoint from physical writes will be similar to the write through the checkpoint queue. Why is it similar to it? Because there will be a lot of writes within oracle, such as writing control files, will also be recorded in physical writes data.

Step 2: issue update command

SQL > update jj_10 set name='aa' where id=20

1 row has been updated.

Step 3: observe whether the block is not dirty.

SQL > select dirty,status from v$bh where file#=7 and block#=406 and status='xcur'

D STATUS

--

N xcur

Step 4: check the data view immediately after the block becomes undirty.

SQL > select * from v$sysstat where name='physical writes' or name='physical writes non

Checkpoint'

STATISTIC# NAME CLASS VALUE STAT_ID

62 physical writes 8 5851 1190468109

67 physical writes non checkpoint 8 3832 2602029796

You can see that the checkpoint irrelevant write has been overwritten by 3 bytes, which has nothing to do with our update declaration. Our update declaration has been updated by dozens of bytes. These 3 bytes should belong to some write operations within oracle, our update declaration, resulting in dirty blocks, is written through the checkpoint queue. Physical writes is a lot more.

The above is what the incremental checkpoint of Oracle shared by the editor refers to. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.