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

Oracle Redo and Undo

"Oracle Core Essential Internals for DBAs and Developers"

In my opinion, the most important feature of Oracle is the emergence of change vector in Oracle 6, which describes the mechanism of block change and is the core of Redo and undo.

But in my opinion the single most important feature of Oracle is one that first appeared in version 6: the change vector, a mechanism for describing changes to data blocks, the heart of redo and undo.

-Basic Data Change basic data change

An interesting feature of Oracle is that it records data twice, the first time it writes the most recent data to data files (for efficiency, the latest data is recorded in memory and batch refreshed to data files), and the second time it is written to redo log files (used to describe how to rebuild data files)

One of the strangest features of an Oracle database is that it records your data twice. One copy of the data exists in a set of data files which hold something that is nearly the latest, up-to-date version of your data (although the newest version of some of the data will be in memory, waiting to be copied to disc); the other copy of the data exists as a set of instructions-the redo log files-telling you how to re-create the content of the data files from scratch.

1.1 The Approach method

When the database changes, you issue the command to change the data, Oracle does not immediately find the corresponding data in data file (if the data is in memory, go to data buffer cache to find the data), and then modify the corresponding data. Instead, the data is modified through the following four key steps:

1. Create a description of how to modify the data.

two。 Create a description of how to roll back the data.

3. Create a description of how to generate a fallback data description.

4. Modify the data.

Under the Oracle approach to data change, when you issue an instruction to change an item of data, Oracle doesn't just go to a data file (or the in-memory copy if the item happens to be buffered), find the item, and change it. Instead, Oracle works through four critical steps to make the change happen.

Stripped to the bare minimum of detail, these are

1. Create a description of how to change the data item.

2. Create a description of how to re-create the original data item if needed.

3. Create a description of how to create the description of how to re-create the original data item.

4. Change the data item.

The third step seems incomprehensible, so let's describe these four steps in another way:

1. Create the Redo change vector corresponding to the generated data block.

two。 Create a Undo record and generate an Undo Block in the Undo tablespace for data fallback.

3. Create the Redo change vector corresponding to the generated undo record.

4. Modify the data.

The tongue-twisting nature of the third step gives you some idea of how convoluted the mechanism is, but all will become clear. With the substitution of a few technical labels in these steps, here's another way of describing the actions of changing a data block:

1. Create a redo change vector describing the change to the data block.

2. Create an undo record for insertion into an undo block in the undo tablespace.

3. Create a redo change vector describing the change to the undo block.

4. Change the data block.

The specific technical details, execution order and Oracle version, the nature of the transaction, the status of each data block before the execution of the change command, and so on.

The exact sequence of steps and the various technicalities around the edges vary depending on the version of Oracle, the nature of the transaction, how much work has been done so far in the transaction, what the states of the various database blocks were before you executed the instruction, whether or not you're looking at the first change of a transaction, and so on.

1.2 An Example example:

Starting with the simplest example of data change, update a row in the middle of an OLTP transaction that has updated a set of scattered rows. In fact, in historical (and most common) cases, the order of steps is different from that I listed in the previous section.

I'm going to start with the simplest example of a data change, which you might expect to see as you updated a single row in the middle of an OLTP transaction that had already updated a scattered set of rows. In fact, the order of the steps in the historic (and most general) case is not the order I've listed in the preceding section.

These steps are actually done in the order of 3, 1, 2, 4, and the two redo change vectors are combined into a redo change record and copied to redo log (buffer) before modifying undo block and data block. This means that the more accurate version is:

The steps actually go in the order 3,1,2,4, and the two redo change vectors are combined into a single redo change record and copied into the redo log (buffer) before the undo block and data block are modified (in that order). This means a slightly more accurate version of my list of actions would be:

1. Create a Redo change vector corresponding to the generated undo record (describe the changes to the undo block).

two。 Create a Redo change vector corresponding to the generated data block (describe the changes to the redo block).

3. Create a Redo change vector that generates undo record and data block and merges it into a Redo record to write to log buffer (easy to redo, etc.).

4. Create Undo record to write to Undo block (to facilitate transaction fallback, etc.).

5. Change the data.

1. Create a redo change vector describing how to insert an undo record into an undo block.

2. Create a redo change vector for the data block change.

3. Combine the redo change vectors into a redo record and write it to the log buffer.

4. Insert the undo record into the undo block.

5. Change the data block.

Here is a small example from a system running Oracle 9.2.0.8 (in the previous version, it was easy to create the most general example of this mechanism).

Here's a little sample, taken from a system running Oracle 9.2.0.8 (the last version in which it's easy to create the most generic example of the mechanism).

We will execute a update statement that updates the five rows by jumping back and forth between two table blocks, and dumps various bits of information into the process trace file before and after the update.

We're going to execute an update statement that updates five rows by jumping back and forth between two table blocks, dumping various bits of information into our process trace file before and after the update.

I need to make my update a little complicated because I want the example to be as simple as possible while avoiding special situations.

I need to make my update a little bit complicated because I want the example to be as simple as possible while avoiding a few "special case" details.

The code I write updates the third, fourth, and fifth rows of data in the first block of the table, and updates a row in the second block (core_demo_02.sql) after each update, changing the third column of each record (a field of type varchar2) from xxxxxx (6 characters in lowercase) to YYYYYYYYYY (10 characters in uppercase).

The code I've written will update the third, fourth, and fifth rows in the first block of a table but will update a row in the second block of the table between each of these three updates (see core_demo_02.sql in the code library on www.apress.com), and it'll change the third column of each row-a varchar2 () column-from xxxxxx (lowercase, six characters) to YYYYYYYYYY (uppercase, ten characters).

Https://github.com/Apress/oracle-core-esntl-internals-for-dbas-devs

-use the core_demo_02.sql script as follows:

-core_demo_02.sql

-1 prepare

Start setenv

Set timing off

Execute dbms_random.seed (0)

Drop table t1

Begin

Execute immediate 'purge recyclebin'

Exception

When others then

Null

End

Begin

Dbms_stats.set_system_stats ('MBRC', 8)

Dbms_stats.set_system_stats ('MREADTIM', 26)

Dbms_stats.set_system_stats ('SREADTIM', 12)

Dbms_stats.set_system_stats ('CPUSPEED', 800)

Exception

When others then

Null

End

Begin

Execute immediate 'begin dbms_stats.delete_system_stats; end;'

Exception

When others then

Null

End

Begin

Execute immediate 'alter session set "_ optimizer_cost_model" = io'

Exception

When others then

Null

End

/

-2 create tables and indexes

Create table t1

As

Select

2 * rownum-1 id

Rownum n1

Cast ('xxxxxx' as varchar2 (10)) v1

Rpad ('0', 100,0') padding

From

All_objects

Where

Rownum 'T1'

Method_opt = > 'for all columns size 1')

End

-4 check the blocks occupied by the table, and how many pieces of data are there in each block? each block has 60 records.

Select

Dbms_rowid.rowid_block_number (rowid) block_number

Count (*) rows_per_block

From

T1

Group by

Dbms_rowid.rowid_block_number (rowid)

Order by

Block_number

BLOCK_NUMBER ROWS_PER_BLOCK

--

114153 60

114154 60

-5 dump data blocks

Alter system switch logfile

Execute dbms_lock.sleep (2)

Spool core_demo_02.lst

-dump_seg stored procedure needs to perform c_dump_seg.sql generation

-https://github.com/Apress/oracle-core-esntl-internals-for-dbas-devs/blob/master/ch_03/c_dump_seg.sql

Execute dump_seg ('T1')

-6. Update data

Update

/ * + index (T1 t1_i1) * /

T1

Set

V1 = 'YYYYYYYYYY'

Where

Id between 5 and 9

-7 dump the data block and undo block after the update block

Alter system checkpoint

Execute dump_seg ('T1')

-dump_undo_block stored procedure

-https://github.com/Apress/oracle-core-esntl-internals-for-dbas-devs/blob/master/ch_02/c_dump_undo_block.sql

Execute dump_undo_block

-8 dump redo block

Rollback

Commit

-dump_log stored procedure

-https://github.com/Apress/oracle-core-esntl-internals-for-dbas-devs/blob/master/ch_02/c_dump_log.sql

Execute dump_log

Spool off

[oracle@cjcos trace] $pwd

/ u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace

[oracle@cjcos trace] $vim cjcdb_ora_21778.trc

The following is the dump information for the fifth line of the block before and after the update:

Here's a symbolic dump of the fifth row in the block before and after the update:

The length of the third column (col 2) before the update is 6 and the data is 78 (the hexadecimal ASCII code of x is 78)

Tab 0, row 4, @ 0x1d3f

Tl: 117 fb:-- Hmurf FLmuri-lb: 0x0 cc: 4

Col 0: [2] c1 0a

Col 1: [2] c1 06

Col 2: [6] 78 78 78

Col 3: [100]

30 30 30... 30 30 30 (for 100 characters)

After the update, the length of the third column (col 2) is 10, and the data is 59 (Y's hexadecimal ASCII code is 59)

Tab 0, row 4, @ 0x2a7

Tl: 121fb:-- Hmurf FLmuri-lb: 0x2 cc: 4

Col 0: [2] c1 0a

Col 1: [2] c1 06

Col 2: [10] 59 59 59

Col 3: [100]

30 30 30... 30 30 30 (for 100 characters)

We can see that the length of the third column (col2) has become 10, which is 10 59 (the hexadecimal ASCII code of Y is 59), and the row address has changed from @ 0x1d3f to @ 0x2a7, indicating that there is no room for the new data in this row, so the new address has been changed.

As you can see, the third column (col 2:) of the table has changed from a string of 78s (x) to a longer string of 59s (Y). Since the update increased the length of the row, Oracle had to copy it into the block's free space to make the change, which is why its starting byte position has moved from @ 0x1d3f to @ 0x2a7. It is still row 4 (the fifth row) in the block, though; if we were to check the block's row directory, we would see that the fifth entry has been updated to point to this new row location.

At the same time, we can see that the lb (lock byte) has changed from 0x0 to 0x2, indicating that the record is locked by the transaction identified by the second transaction slot in the list of transaction slots in that block. The transaction slot can be seen at the head of the block. We will discuss it in more depth in the third chapter.

I dumped the block before committing the change, which is why you can see that the lock byte (lb:) has changed from 0x0 to 0x2-the row is locked by a transaction identified by the second slot in the block's interested transaction list (ITL). We will be discussing ITLs in more depth in Chapter 3.

Let's look at different change vectors. First, from the current redo log file dump, we can examine the change vector and describe what we have done to the table:

So let's look at the various change vectors. First, from a symbolic dump of the current redo log file, we can examine the change vector describing what we did to the table:

TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee485a SEQ: 2 OP:11.5

KTB Redo

Op: 0x02 ver: 0x01

Op: C uba: 0x0080009a.09d4.0f

KDO Op code: URP row dependencies Disabled

Xtype: XA bdba: 0x02c0018a hdba: 0x02c00189

Itli: 2 ispac: 0 maxfr: 4863

Tabn: 0 slot: 4 (0x4) flag: 0x2c lock: 2 ckix: 16

Ncol: 4 nnew: 1 size: 4

Col 2: [10] 59 59 59

As you can see, the fifth line URP (update line block), and the sixth line tells us the block address (bdba) and the segment header block (hdba) of the object being updated.

I'll pick out just the most significant bits of this change vector. You can see that the Op code: in line 5 is URP (update row piece). Line 6 tells us the block address of the block we are updating (bdba:) and the segment header block for that object (hdba:).

In line 7, we see that the transaction that performs this update uses ITL entry 2 (itli:), which is an update to tabn: 0 slot: 4 (row 5 in the first table; keep in mind that blocks in the cluster can hold data from many tables, so each block must contain a list that identifies the table with rows in the block).

In line 7 we see that the transaction doing this update is using ITL entry 2 (itli:), which confirms

What we saw in the block dump: it's an update to tabn: 0 slot: 4 (fifth row in the first table; remember that blocks in a cluster can hold data from many tables, so each block has to include a list identifying the tables that have rows in the block).

Finally, in the last two rows, we see that the row has four columns (ncol:), where we change one column (nnew:), increase the row length (size:) by four bytes, and we change the second column to YYYYYYYYYY.

Finally, in the last two lines, we see that the row has four columns (ncol:), of which we are changing one (nnew:), increasing the row length (size:) by 4 bytes, and that we are changing column 2 to YYYYYYYYYY.

The next thing we need to see is how to undo our changes. This will occur in the form of a undo record undo record, dumped from the associated undo block block. In Chapter 3, I will introduce how to find the correct undo block. The following text shows the related records from the block dump:

The next thing we need to see is a description of how to put back the old data. This appears in the form of an undo record, dumped from the relevant undo block. The methods for finding the correct undo block will be covered in Chapter 3. The following text shows the relevant record from the symbolic block dump:

*--

* Rec # 0xf slt: 0x1a objn: 45810 (0x0000b2f2) objd: 45810 tblspc: 12 (0x0000000c)

* Layer: 11 (Row) opc: 1 rci 0x0e

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

Rdba: 0x00000000

*--

KDO undo record:

KTB Redo

Op: 0x02 ver: 0x01

Op: C uba: 0x0080009a.09d4.0d

KDO Op code: URP row dependencies Disabled

Xtype: XA bdba: 0x02c0018a hdba: 0x02c00189

Itli: 2 ispac: 0 maxfr: 4863

Tabn: 0 slot: 4 (0x4) flag: 0x2c lock: 0 ckix: 16

Ncol: 4 nnew: 1 size:-4

Col 2: [6] 78 78 78

Again, I'll ignore a few details and just point out that an important part of this undo record (for us), the row size we see is reduced by 4 bytes, and the second column is xxxxxx.

Again, I'm going to ignore a number of details and simply point out that the significant part of this undo record (for our purposes) appears in the last five lines and comes close to repeating the content of the redo change vector, except that we see the row size decreasing by 4 bytes as column 2 becomes xxxxxx.

But this is a undo record, written in a undo block, stored in the undo table space of one of the data files, and, as I pointed out earlier, Oracle holds two copies of everything, one in the data file and one in the redo log file. Because we have put something in the data file (even if it is in the undo tablespace), we need to create a description of what we are doing and write that description to the redo log file. We need another redo to change the vector, which goes like this:

But this is an undo record, written into an undo block and stored in the undo tablespace in one of the data files, and, as I pointed out earlier, Oracle keeps two copies of everything, one in the data files and one in the redo log files. Since we've put something into a data file (even though it's in the undo tablespace), we need to create a description of what we've done and write that description into the redo log file. We need another redo change vector, which looks like this:

TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 4 OP:5.1

Ktudb redo: siz: 92 spc: 6786 flg: 0x0022 seq: 0x09d4 rec: 0x0f

Xid: 0x000a.01a.0000255b

Ktubu redo: slt: 26 rci: 14 opc: 11.1 objn: 45810 objd: 45810 tsn: 12

Undo type: Regular undo Undo type: Last buffer split: No

Tablespace Undo: No

0x00000000

KDO undo record:

KTB Redo

Op: 0x02 ver: 0x01

Op: C uba: 0x0080009a.09d4.0d

KDO Op code: URP row dependencies Disabled

Xtype: XA bdba: 0x02c0018a hdba: 0x02c00189

Itli: 2 ispac: 0 maxfr: 4863

Tabn: 0 slot: 4 (0x4) flag: 0x2c lock: 0 ckix: 16

Ncol: 4 nnew: 1 size:-4

Col 2: [6] 78 78 78

The bottom half of the redo change vector looks a lot like an undo record, which is not surprising, since it is, after all, a description of what we want to put in the undo block.

The bottom half of the redo change vector looks remarkably like the undo record, which shouldn't be a surprise as it is, after all, a description of what we want to put into the undo block.

The upper part of the redo change vector tells us where the lower part goes and contains some information about the block it is going to write to.

The top half of the redo change vector tells us where the bottom half goes, and includes some information about the block header information of the block it's going into.

The most important detail, for our purposes, is the first line of DBA (block address), which identifies block 0x0080009a: if you know that Oracle blocks are in hexadecimal numbers, you will realize that this is data file 2 (the file number of undo tablespaces in the newly created database).

The most significant detail, for our purposes, is the DBA: (data block address) in line 1, which identifies block 0x0080009a: if you know your Oracle block numbers in hex, you'll recognize that this is block 154of data file 2 (the file number of the undo tablespace in a newly created database).

1.3 Debriefing Summary

So, what progress have we made so far? When we change a data block, Oracle inserts an undo record in the undo block, telling us how to reverse the change. However, for each block change that occurs in the database, Oracle creates a redo change vector to describe how to make the change, and creates these vectors before making the change. Historically, it created the undo change vector before creating the "forward" change vector, so the sequence of events I described earlier (see figure 2-1) is as follows:

So where have we got to so far? When we change a data block, Oracle inserts an undo record into an undo block to tell us how to reverse that change. But for every change that happens to a block in the database, Oracle creates a redo change vector describing how to make that change, and it creates the vectors before it makes the changes. Historically, it created the undo change vector before it created the "forward" change vector, hence, the following sequence of events (see Figure 2-1) that I described earlier occurs:

Figure 2-1. A sequence of events that make minor updates in the middle of a transaction

1. Create a change vector for the undo record.

two。 Create a change vector for the data block.

3. Merge the change vector and write the redo record to the redo log (buffer).

4. Insert the undo record into the undo block.

5. Make changes to the data block.

Figure 2-1. Sequence of events for a small update in the middle of a transaction

1. Create the change vector for the undo record.

2. Create the change vector for the data block.

3. Combine the change vectors and write the redo record into the redo log (buffer).

4. Insert the undo record into the undo block.

5. Make the change to the data block.

When you look at the first two steps here, of course, there's no reason to believe I put them in the right order. Nothing I have described or abandoned means that these actions must have taken place in this order. But there is one small detail I can tell you now that I omitted the change vector for the dump, partly because things will be different after Oracle 10g, and partly because if you start thinking in the wrong order, the description of the activity will be easier to understand.

When you look at the first two steps here, of course, there's no reason to believe that I've got them in the right order. Nothing I've described or dumped shows that the actions must be happening in that order. But there is one little detail I can now show you that I omitted from the dumps of the change vectors, partly because things are different from 10g onwards and partly because the description of the activity is easier to comprehend if you first think about it in the wrong order.

So far, I have shown you our two change vectors as separate entities; if I show you the complete picture of these change vectors entering the redo log, you will see how they are combined into a redo record:

So far I've shown you our two change vectors only as individual entities; if I had shown you the complete picture of the way these change vectors went into the redo log, you would have seen how they were combined into a single redo record:

REDO RECORD-Thread:1 RBA: 0x00036f.00000005.008c LEN: 0x00f8 VLD: 0x01

SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01

CHANGE # 1 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 4 OP:5.1

...

CHANGE # 2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee485a SEQ: 2 OP:11.5

...

In the redo log, the change vector appears in pairs, and the unrecorded change vector appears before the corresponding forward change vector.

It is a common (though far from universal) pattern in the redo log that change vectors come in matching pairs, with the change vector for an undo record appearing before the change vector for the corresponding forward change.

When looking at the basics of the previous redo record, it is worth noting that the LEN:- in the first line is the length of the redo record: 0x00f8 = 248bytes.

While we're looking at the bare bones of the preceding redo record, it's worth noting the LEN: figure in the first line-this is the length of the redo record: 0x00f8 = 248bytes.

All we have to do is change xxxxxx to yyyyyyyy in one line, which will cost us 248bytes of log information.

All we did was change xxxxxx to YYYYYYYYYY in one row and it cost us 248 bytes of logging information.

In fact, given the end result, this seems to be a very expensive operation: we have to generate two redo change vectors and update two database blocks to make a small change, which seems to be four times the number of steps we need to perform. We hope that all this extra work will be reasonably rewarded.

In fact, it seems to have been a very expensive operation given the net result: we had to generate two redo change vectors and update two database blocks to make a tiny little change, which looks like four times as many steps as we need to do. Let's hope we get a decent payback for all that extra work.

1.4 Summary of Observations Summary

Before we proceed, we can summarize our observations as follows: in the data file, every change we make to our data matches the undo record created by Oracle (this is also a change to the data file); at the same time, Oracle puts a description of how to make changes and how to make our own changes in the redo log.

Before we continue, we can summarize our observations as follows: in the data files, every change we make to our own data is matched by Oracle with the creation of an undo record (which is also a change to a data file); at the same time Oracle puts into the redo log a description of how to make our change and how to make its own change.

You may notice that because the data can be changed "in place", we can create "infinity" (that is. Changes to a single row of data, but you obviously cannot record an unlimited number of undo records without adding data files in the undo tablespace, and you cannot record an unlimited number of changes in the redo log without constantly adding more redo log files For the sake of simplicity, we will postpone the issue of unlimited changes and temporarily pretend that we can record as many undo and redo records as possible.

You might note that since data can be changed "in place," we could make an "infinite" (i.e.

Arbitrarily large) number of changes to our single row of data, but we clearly can't record an infinite number of undo records without growing the data files of the undo tablespace, nor can we record an infinite number of changes in the redo log without constantly adding more redo log files. For the sake of simplicity, we'll postpone the issue of infinite changes and simply pretend for the moment that we can record as many undo and redo records as we need.

Two: ACID

Although we will not discuss transactions in this chapter, it is worth mentioning the ACID principles of transactional systems and how Oracle implements undo and redo so that Oracle can meet these requirements. Table 2-1 lists the ACID.

Although we're not going to look at transactions in this chapter, it is, at this point, worth mentioning the ACID requirements of a transactional system and how Oracle's implementation of undo and redo gives Oracle the capability of meeting those requirements.

Table 2-1. The ACID Requirements

The following list details the items in Table 2-1:

The following list goes into more detail about each of the requirements in Table 2-1:

Atomicity: when we change the data, we create a undo record that describes how to roll back the modified data. This means that when we are in the course of a transaction, if another user tries to view any data we have modified, he can be instructed to use the undo record to view the old version of the data, thus making our work invisible until the moment we decide to publish (submit) it. We can make sure that each other's users can either see nothing or see everything.

Atomicity: As we make a change, we create an undo record that describes how to

Reverse the change. This means that when we are in the middle of a transaction

Another user trying to view any data we have modified can be instructed to use the

Undo records to see an older version of that data, thus making our work invisible

Until the moment we decide to publish (commit) it. We can ensure that the other

User either sees nothing of what we've done or sees everything.

Consistency: this requirement is actually about the database that defines the constraints of the legal state.

We can assume that the existence of the undo records revocation record means that other users are prevented from viewing the increments of the transaction, so they cannot see the transaction passing through the temporary inconsistent state to another consistent state. They see either the state before the start of the transaction or the state after the transaction is completed, and will not see the intermediate state of the transaction.

Consistency: This requirement is really about constraints defining the legal states

Of the database; but we could also argue that the presence of undo records means

That other users can be blocked from seeing the incremental application of our

Transaction and therefore cannot see the database moving from one legal state to

Another by way of a temporarily illegal state-what they see is either the old state

Or the new state and nothing in between. (The internal code, of course, can see all

The intermediate states-and take advantage of being able to see them-but the

End-user code never sees inconsistent data.)

Isolation: once again, we can see that the availability of undo records undo records prevents other users from seeing how we change the data until we decide that the transaction has completed and committed. In fact, we do better: the availability of undo records undo means that other users do not have to see the impact of our transaction for the entire duration of their transaction, even if we start and end our transaction between the beginning and end of their transaction. (this is not the default isolation level in Oracle, but it is the available isolation level; see "isolation level".) of course, we do encounter confusing situations when two users try to change the same data at the same time; perfect isolation is impossible in limited-time transactions.

Isolation: Yet again we can see that the availability of undo records stops other

Users from seeing how we are changing the data until the moment we decide that

Our transaction is complete and commit it. In fact, we do better than that: the

Availability of undo means that other users need not see the effects of our

Transactions for the entire duration of their transactions, even if we start and end

Our transaction between the start and end of their transaction. (This is not the

Default isolation level in Oracle, but it is an available isolation level; see the

"Isolation Levels" sidebar.) Of course, we do run into confusing situations when

Two users try to change the same data at the same time; perfect isolation is not

Possible in a world where transactions have to take a finite amount of time.

Persistence: this feature is attributed to the redo log redo log. How do I ensure that completed transactions still exist after a system failure? The most straightforward strategy is to continue to write any changes to disk when the disk changes or when the last step of the transaction is "completed". If there is no redo log, this may mean that a large number of random blocks are written when the data is changed. Imagine inserting 10 rows into an order_lines table with three indexes; this might require 31 randomly distributed disk writes to make persistent changes to 1 table block and 30 index blocks. But Oracle has a redo redo mechanism that does not need to write an entire block of data when a change occurs, but instead prepares a small description of the change, 31 of which may only be written to the end of the log file when you need to ensure a permanent record of the entire transaction (relatively speaking).

Durability: This is the requirement that highlights the benefit of the redo log. How

Do you ensure that a completed transaction will survive a system failure? The

Obvious strategy is to keep writing any changes to disc, either as they happen or as

The final step that "completes" the transaction. If you didn't have the redo log, this

Could mean writing a lot of random data blocks to disc as you change them.

Imagine inserting ten rows into an order_lines table with three indexes; this could

Require 31 randomly distributed disk writes to make changes to 1 table block and

30 index blocks durable. But Oracle has the redo mechanism. Instead of writing an

Entire data block as you change it, you prepare a small description of the change

And 31 small descriptions could end up as just one (relatively) small write to the

End of the log file when you need to make sure that you've got a permanent record

Of the entire transaction. (We'll discuss in Chapter 6 what happens to the 31

Changed data blocks, and the associated undo blocks, and how recovery might

Take place.)

2.1 ISOLATION LEVELS isolation level

Oracle provides three different isolation levels: read committed (default), read only, and serializable as a brief description of the differences. Consider the following scenario: table T1 has one piece of data, tables T2 and T1 have the same table structure, and we have two sessions executed in the following order:

Oracle offers three isolation levels: read committed (the default), read only, and serializable. As a brief sketch of the differences, consider the following scenario: table t1 holds one row, and table t2 is identical to t1 in structure. We have two sessions that go through the following steps in order:

1. Session 1: select from t1

2. Session 2: insert into T1 select * from T1

3. Session 2: commit

4. Session 1: select from t1

5. Session 1: insert into T2 select * from T1

If the isolation level of session 1 is read committed, one piece of data will be queried the first time, two pieces of data will be queried the second time, and two pieces of data will be inserted finally.

If the isolation level of session 1 is read only, you will get a piece of data in the first query and a piece of data in the second query. Insert the data Times error "ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction."

If the isolation level of session 1 is serializable, you will get a piece of data in the first query, a piece of data in the second query, and finally insert a piece of data.

If session 1 is operating at isolation level read committed, it will select one row on the first select, select two rows on the second select, and insert two rows.

If session 1 is operating at isolation level read only, it will select one row on the first select, select one row on the second select, and fail with Oracle error "ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction."

If session 1 is operating at isolation level serializable, it will select one row on the first select, select one row on the second select, and insert one row.

REDO and UNDO mechanisms not only meet the basic requirements of ACID, but also have advantages in terms of performance and recoverability.

The performance benefits of redo redo have been covered when discussing persistence, and if you want an example of the performance benefits of UNDO undo, consider isolation-if your users need to update data at the same time, how do you run a report that takes a few minutes to complete? In the absence of a UNDO undo mechanism, you must choose between allowing error results or locking change data. This is a choice you must make along with other database products. The UNDO undo mechanism allows for a very high degree of concurrency, because according to Oracle's marketing campaign, "readers don't block writing, and writing doesn't block readers."

In terms of recoverability (we will examine recoverability in more detail in Chapter 6), if we record a complete list of changes made to the database, in principle, we can start with a completely new database. Then simply reapply each change description to make the latest copy of the original database. In fact, of course, we don't (usually) start with a new database; instead, we back up the data files regularly so that we only need to replay a small portion of the total redo generated to update the replica database.

Not only are the mechanisms for undo and redo sufficient to implement the basic requirements of ACID, they also offer advantages in performance and recoverability.

The performance benefit of redo has already been covered in the comments on durability; if you want an example of the performance benefits of undo, think about isolation-how can you run a report that takes minutes to complete if you have users who need to update data at the same time? In the absence of something like the undo mechanism, you would have to choose between allowing wrong results and locking out everyone who wants to change the data. This is a choice that you have to make with some other database products. The undo mechanism allows for an extraordinary degree of concurrency because, per Oracle's marketing sound bite, "readers don't block writers, writers don't block readers."

In terms of recoverability (we will examine recoverability in more detail in Chapter 6), if we record all the changes we made to the database, we can start with a completely new database. Then simply reapply each change description to make the latest copy of the original database. Of course, we don't actually start with a new database; instead, we take regular backup copies of data files so that we only need to replay a small portion of the redo to keep the replica database up-to-date.

As far as recoverability is concerned (and we will examine recoverability in more detail in Chapter 6), if we record a complete list of changes we have made to the database, then we could, in principle, start with a brand-new database and simply reapply every single change description to reproduce an upto-date copy of the original database. Practically, of course, we don't (usually) start with a new database; instead we take regular backup copies of the data files so that we need only replay a small fraction of the total redo generated to bring the copy database up to date.

Three: the simplicity of Redo Simplicity-Redo

The way we deal with redo is simple: keep generating the stream of redo records and writing them to redo log as quickly as possible. Initially enters an area of shared memory called the redo log buffer redo log buffer. It is then written from redo log buffer to disk, or online redo log files. The number of online redo log files is limited, so we must constantly reuse them in a circular manner.

The way we handle redo is quite simple: we just keep generating a continuous stream of redo records and pumping them as fast as we can into the redo log, initially into an area of shared memory known as the redo log buffer. Eventually, of course, Oracle has to deal with writing the buffer to disk and, for operational reasons, actually writes the "continuous" stream to a small set of predefined files-the online redo log files. The number of online redo log files is limited, so we have to reuse them constantly in a round-robin fashion.

In order to keep the information in the online redo log files longer, most systems keep one or more copies of the online redo log files, that is, archive logs. However, in the case of redo, the write forget mechanism is used, and once redo record is written to redo log (buffer), we (usually) do not want the instance to reread it. This "write and forget" approach makes redoing a very simple mechanism.

To protect the information stored in the online redo log files over a longer time period, most systems are configured to make a copy, or possibly many copies, of each file as it becomes full before allowing Oracle to reuse it: the copies are referred to as the archived redo log files. As far as redo is concerned, though, it's essentially write it and forget it-once a redo record has gone into the redo log (buffer), we don't (normally) expect the instance to reread it. At the basic level, this "write and forget" approach makes redo a very simple mechanism.

Note:

Although we only want to write and forget about online redo log files, there are some special cases that need to read online redo log files, such as checking bad blocks of memory, recovering bad blocks from disk, etc., and some features that need to read online redo log files, such as Log Miner, Streams, asynchronous Change Data Capture, and so on. There are also some new features that need to be read from online redo log files in recent years, such as standby database, which we will discuss in Chapter 6.

Note

Although we don't usually expect to do anything with the online redo log files except write them and forget them, there is a special case where a session can read the online redo log files when it discovers the inmemory version of a block to be corrupt and attempts to recover from the disk copy of the block. Of course, some

Features, such as Log Miner, Streams, and asynchronous Change Data Capture, have been created in recent years to take advantage of the redo log files, and some of the newer mechanisms for dealing with Standby databases have become real-time and are bound into the process that writes the online redo. We will look at such features in Chapter 6.

Then, there is a complex problem, and there is a performance bottleneck in the process of writing redo record to redo log buffer. Prior to the 10g version, oracle wrote the redo record (including a pair of redo change vectors) resulting from data changes in each session to redo log buffer. But a single session can change a lot in a short period of time, or there may be multiple sessions operating concurrently, but there is only one redo log buffer to access.

There is, however, one complication. There is a critical bottleneck in redo generation, the moment when a redo record has to be copied into the redo log buffer. Prior to 10g, Oracle would insert a redo record (typically consisting of just one pair of redo change vectors) into the redo log buffer for each change a session made to user data. But a single session might make many changes in a very short period of time, and there could be many sessions operating concurrently-and there's only one redo log buffer that everyone wants to access.

To solve this problem, create a mechanism to control access to each part of shared memory, and Oracle uses redo allocation latch to protect the use of redo log buffer. When you need to use part of the space in the log buffer for the process, you need to apply for the redo allocation latch first. Once the latch is obtained, the relevant information can be written to the buffer. This avoids the risk of multiple processes rewriting the same block of log buffer. However, if a large number of processes apply for redo allocation latch, it will consume a lot of resources (mainly when CPU is used for latch spin), or leave the queue and enter sleep time after the first spin failure.

It's relatively easy to create a mechanism to control access to a piece of shared memory, and Oracle's use of the redo allocation latch to protect the redo log buffer is fairly well known. A process that needs some space in the log buffer tries to acquire (get) the redo allocation latch, and once it has exclusive ownership of that latch, it can reserve some space in the buffer for the information it wants to write into the buffer. This avoids the threat of having multiple processes overwrite the same piece of memory in the log buffer, but if there are lots of processes constantly competing for the redo allocation latch, then the level of competition could end up "invisibly" consuming lots of resources (typically CPU spent on latch spinning) or even lots of sleep time as sessions take themselves off the run queue after failing to get the latch on the first spin.

In the old version of Oracle, redo generated very little when the database was idle, and the "one change = one record = one allocation" strategy was sufficient for most systems, but as the system became bigger and busier and needed to handle larger concurrent requests (especially for OLTP systems), a more optimized strategy was needed, so a new mechanism combining private redo private redo and memory redo in-memory undo appeared in the new 10g strategy.

In older versions of Oracle, when the databases were less busy and the volume of redo generated was much lower, the "one change = one record = one allocation" strategy was good enough for most systems, but as systems became larger, the requirement for dealing with large numbers of concurrent allocations (particularly for OLTP systems) demanded a more scalable strategy. So a new mechanism combining private redo and in-memory undo appeared in 10g.

In fact, the process can traverse the entire transaction, generate all its change vectors change vectors, and store them in a pair of private redo log buffers private redo log buffers. When the transaction is completed, the process copies all redo of private storage to the public redo log buffer, and the traditional log buffer processing takes over. This means that a process gets the common redo allocation latch public redo allocation latch only once after the transaction ends, rather than once in each change.

In effect, a process can work its way through an entire transaction, generating all its change vectors and storing them in a pair of private redo log buffers. When the transaction completes, the process copies all the privately stored redo into the public redo log buffer, at which point the traditional log buffer processing takes over. This means that a process acquires the public redo allocation latch only once per transaction, rather than once per change.

Be careful

The strategy is optimized in Oracle9.2 to control multiple log buffers using the log_parallelism parameter, which can be ignored when the CPU is less than 16:00. At Oracle 10g, if CPU > = 2, then there are at least two public log buffers (redo threads).

Note

As a step toward improved scalability, Oracle 9.2 introduced the option for multiple log buffers with the log_parallelism parameter, but this option was kept fairly quiet and the general suggestion was that you didn't need to know about it unless you had at least 16 CPUs. In 10g you get at least two public log buffers (redo threads) if you have more than one CPU.

There are many details (and limitations) to mention, but before we discuss any complexity, let's notice how it gets information through the dynamic performance view. I used the script in core_demo_02. Delete the dump command and replace it with a call to take a snapshot of v$latch and v$sesstat (see core_demo_02b). Sql in the code base). I also modified the SQL to update 50 rows instead of 5 to show the workload differences more clearly. The following results are from 9i and 10g systems running the same tests, respectively. The first is the result of 9i:

There are a number of details (and restrictions) that need to be mentioned, but before we go into any of the complexities, let's just take a note of how this changes some of the instance activity reported in the dynamic performance views. I've taken the script in core_demo_02.sql, removed the dump commands, and replaced them with calls to take snapshots of v$latch and v$sesstat (see core_demo_02b.sql in the code library). I've also modified the SQL to update 50 rows instead of 5 rows so that differences in workload stand out more clearly. The following results come from a 9i and a 10g system, respectively, running the same test. First the 9i results:

In particular, in the 9i output, we have performed 51 redo copies and redo allocation latches, and created 51 redo entries. Compared with the result of 10g:

Note particularly in the 9i output that we have hit the redo copy and redo allocation latches 51 times each (with a couple of extra gets on the allocation latch from another process), and have created 51 redo entries. Compare this with the 10g results:

In the 10g version, we only applied for redo copy latch once for our session, and there was a little more activity on redo allocation latch, so we can see that the generated Redo size is slightly smaller than that of the 9i version. These results appear after submission. If we take the same snapshot before committing, we will not see any redo entries (0 redo size), the undo latch in memory will drop to 51, and the redo allocation latch will be 1 instead of 5.

In 10g, our session has hit the redo copy latch just once, and there has been just a little more activity on the redo allocation latch. We can also see that we have generated a single redo entry with a size that is slightly smaller than the total redo size from the 9i test. These results appear after the commit; if we took the same snapshot before the commit, we would see no redo entries (and a zero redo size), the gets on the In memory undo latch would drop to 51, and the gets on the redo allocation latch would be 1, rather than 5.

As a result, the threat of activity and competition at a key point has been significantly reduced. On the downside, we can see that 10g hit a new latch called memory undo latch In memory undo latch 53 times during testing, making it look as if we were just moving contention issues from one place to another. Let's write down that idea and think about it later.

So there's clearly a notable reduction in the activity and the threat of contention at a critical location. On the downside, we can see that 10g has, however, hit that new latch called the In memory undo latch 53 times in the course of our test, which makes it look as if we may simply have moved a contention problem from one place to another. We'll take a note of that idea for later examination.

We can understand what happened from different places in the database. We can examine the v$latch_children to understand why the change in latch activity is not a new threat. We can check the redo log file to see what a large redo entry looks like. We can find some dynamic performance objects (x$kcrfstrand and x$ktifp) that will help us gain insight into how various activities are linked together.

There are various places we can look in the database to understand what has happened. We can examine v$latch_children to understand why the change in latch activity isn't a new threat. We can examine the redo log file to see what the one large redo entry looks like. And we can find a couple of dynamic performance objects (x$kcrfstrand and x$ktifp) that will help us to gain an insight into the way in which various pieces of activity link together.

The structure is enhanced based on two sets of memory structures. One (called x$kcrfstrand, private redo) handles the "forward" change vector, and the other collection (called x$ktifp, the undo pool in-memory undo pool in memory) handles the undo change vector. The private private redo redo structure happens to contain information about "public" redo log buffer (s), so don't worry if you see two different information schemas when querying.

The enhanced infrastructure is based on two sets of memory structures. One set (called

X$kcrfstrand, the private redo) handles "forward" change vectors, and the other set (called x$ktifp, the in-memory undo pool) handles the undo change vectors. The private redo structure also happens to hold information about the traditional "public" redo log buffer (s), so don't be worried if you see two different patterns of information when you query it.

The number of pools in x$ktifp (in-memory undo) depends on the size of the saved transaction array (v$transaction), which is set by parameter transactions (but can be derived from parameter sessions or parameter processes). Essentially, the number of pools defaults to transactions / 10, and each pool is managed by its own "In memory undo latch".

The number of pools in x$ktifp (in-memory undo) is dependent on the size of the array that holds transaction details (v$transaction), which is set by parameter transactions (but may be derived from parameter sessions or parameter processes). Essentially, the number of pools defaults to transactions / 10 and each pool is covered by its own "In memory undo latch" latch.

For each entry in x$ktifp, there is a corresponding private redo entry private redo entry in x$kcrfstrand, and, as I mentioned earlier, there are additional entries for the traditional "public" redo thread "public" redo threads. The number of common redo threads public redo threads is determined by the cpu_count parameter, which seems to be the upper limit (1 + cpu_count / 16). Each entry in x$kcrfstrand is controlled by its own redo allocation latch, and each common redo thread is controlled by a redo copy latch redo copy latch per CPU (we will discuss the role of these latches in Chapter 6)

For each entry in x$ktifp there is a corresponding private redo entry in x$kcrfstrand, and, as I mentioned earlier, there are then a few extra entries which are for the traditional "public" redo threads. The number of public redo threads is dictated by the cpu_count parameter, and seems to be ceiling (1 + cpu_count / 16). Each entry in x$kcrfstrand is covered by its own redo allocation latch, and each public redo thread is additionally covered by one redo copy latch per CPU (we'll be examining the role of these latches in Chapter 6).

If we go back to the original test and update only five rows and two blocks in the table, Oracle still accesses the rows and cache blocks in the same order, but does not package the redo change vectors in pairs, writes them to the redo log buffer and modifies the block, but does the following:

If we go back to our original test, updating just five rows and two blocks in the table, Oracle would still go through the action of visiting the rows and cached blocks in the same order, but instead of packaging pairs of redo change vectors, writing them into the redo log buffer, and modifying the blocks, it would operate as follows:

1. Start the transaction by getting a pair of matching private memory structures, one from x$ktifp and one from x$kcrfstrand.

two。 Mark each affected block as having private redo (but do not change the block).

3. Writes each undo change vector undo change vector to the selected memory undo pool in-memory undo pool.

4. Writes each redo change vector redo change vector to the selected private redo thread private redo thread.

5. End the transaction by placing two single redo change records.

6. Copy the redo change record to the redo log and apply the changes to the block.

1. Start the transaction by acquiring a matching pair of the private memory structures, one from x$ktifp and one from x$kcrfstrand.

2. Flag each affected block as "has private redo" (but don't change the block).

3. Write each undo change vector into the selected in-memory undo pool.

4. Write each redo change vector into the selected private redo thread.

5. End the transaction by concatenating the two structures into a single redo change record.

6. Copy the redo change record into the redo log and apply the changes to the blocks.

If we look at the memory structure (see core_imu_01). Just before we committed the transaction from the original test, we saw the following:

If we look at the memory structures (see core_imu_01.sql in the code depot) just before we commit the transaction from the original test, we see the following:

This indicates that the private memory area of the session allows a "forward" changes of about 64KB, as does the "undo" change. For 64-bit systems, this is closer to each 128KB. The update to the five lines uses approximately 4KB from these two areas.

If I dump the redo log file after committing the changes, this is the redo record I got (at a minimum):

This show us that the private memory areas for a session allow roughly 64KB for "forward" changes, and the same again for "undo" changes. For a 64-bit system this would be closer to 128KB each. The update to five rows has used about 4KB from each of the two areas.

If I then dump the redo log file after committing my change, this (stripped to a bare minimum) is the one redo record that I get:

You'll notice that the length of the undo record (LEN:) is 0x594 = 1428, which matches the redo size statistics I saw when I ran this particular test. This is much smaller than the sum of 4352 and 3920 bytes reported in the memory structure, so it is clear that there are a lot of additional bytes that involve tracking private undo and redo-- possibly the starting overhead in the buffer.

You'll notice that the length of the undo record (LEN:) is 0x594 = 1428, which matched the value of the redo size statistic I saw when I ran this particular test. This is significantly smaller than the sum of the 4352 and 3920 bytes reported as used in the in-memory structures, so there are clearly lots of extra bytes involved in tracking the private undo and redo-perhaps as starting overhead in the buffers.

If you read through the titles of 12 individual change vectors and pay special attention to the OP: code, you will see that code 11.5 has five change vectors and code 5.1 has five change vectors. These are five forward change vectors forward change vectors, followed by five undo block change vectors undo block change vectors. The change vector # 2 (code 5.2) is the beginning of the transaction, while the change vector # 7 (code 5.4) is the so-called commit record, the end of the transaction. We will discuss these change vectors in more detail in Chapter 3, but it is worth mentioning here that although most change vectors are applied only to data blocks when the transaction commits, the change vector at the beginning of the transaction is an important special case. applied to the undo segment header block at the beginning of the transaction.

If you read through the headers of the 12 separate change vectors, taking note particularly of the OP: code, you'll see that we have five change vectors for code 11.5 followed by five for code 5.1. These are the five forward change vectors followed by the five undo block change vectors. Change vector # 2 (code 5.2) is the start of transaction, and change vector # 7 (code 5.4) is the so-called commit record, the end of transaction. We'll be looking at those change vectors more closely in Chapter 3, but it's worth mentioning at this point that while most of the change vectors are applied to data blocks only when the transaction commits, the change vector for the start of transaction is an important special case and is applied to the undo segment header block as the transaction starts.

Therefore, Oracle has a mechanism to increase the level of concurrency we can achieve by reducing the number of times a session requests space from the (public) redo log buffer and copies information to that buffer. To a certain extent. But you might think, we have to pay for this benefit-- of course, we did pay the price.

So Oracle has a mechanism for reducing the number of times a session demands space from, and copies information into, the (public) redo log buffer, and that improves the level of concurrency we can achieve. . . Up to a point. But you're probably thinking that we have to pay for this benefit somewhere- and, of course, we do.

Earlier, we saw that every change we made resulted in access to the memory unlocking latch. Does this mean that we have only diverted the threat of latch activity, not really mitigated it? Yes and no . Now we hit only one latch (undo latch in memory) instead of two (redo allocation redo allocation and redo copy redo copy), so we have at least halved latch activity, but more importantly, the undo latch in memory has multiple sublatches, each of which corresponds to an in-memory undo pool. Before the advent of the new mechanism, most systems ran with only one redo allocation latch, so although we now use undo latches in memory as often as we use redo allocation latches, but we are extending access to more latches.

Earlier on we saw that every change we made resulted in an access to the In memory undo latch. Does that mean we have just moved the threat of latch activity rather than actually relieving it? Yes and no. We now hit only one latch (In memory undo latch) instead of two (redo allocation and redo copy), so we have at least halved the latch activity, but, more significantly, there are multiple child latches for the In memory undo latches, one for each in-memory undo pool. Before the new mechanism appeared, most systems ran with just one redo allocation latch, so although we now hit an In memory undo latch just as many times as we used to hit the redo allocation latch, we are spreading the access across far

More latches.

It is also worth noting that the new mechanism also has two types of redo allocation latches: one that covers private redo threads and one that covers public redo threads, each with its own latch. This helps explain the bonus of the redo allocation latch statistics we saw earlier: our session uses a private redo allocation latch to get the private redo thread, and then the log writer (we'll see in Chapter 6) acquires the public redo allocation latch to write the log buffer to the file.

It's also worth noting that the new mechanism also has two types of redo allocation latch-one type covers the private redo threads, one type covers the public redo threads, and each thread has its own latch. This helps to explain the extra gets on the redo allocation latch statistic that we saw earlier: our session uses a private redo allocation latch to acquire a private redo thread, then on the commit it has to acquire a public redo allocation latch, and then the log writer (as we shall see in Chapter 6) acquires the public redo allocation latches (and my test system had two public redo threads) to write the log buffer to file.

Overall, the number of latch activities has decreased, and the distribution of latch activities has become more widespread, which is a good thing. But in a multi-user system, there are always other ideas to consider-using the old mechanism, the amount of redo of sessions copied to the log buffer and applied to the database block at any time is very small; with this new mechanism, the redo volume of replication and application may be relatively large, which means that more time is spent on database blocks, which may prevent other sessions from accessing these blocks when making changes. This may be one of the reasons why private redo threads are severely limited in size.

Overall, then, the amount of latch activity decreases and the focus of latch activity is spread a little more widely, which is a good thing. But in a multiuser system, there are always other points of view to consider-using the old mechanism, the amount of redo a session copied into the log buffer and applied to the database blocks at any one instant was very small; using the new mechanism, the amount of redo to copy and apply could be relatively large, which means it takes more time to apply to the database blocks, potentially blocking other sessions from accessing those blocks as the changes are made. This may be one reason why the private redo threads are strictly limited in size.

In addition, with the old mechanism, the second session will immediately see changes to the data; with the new mechanism, the second session will see only one block affected by some private redo, so the second session is now responsible for tracking the private redo and applying it to the block (if necessary), and then deciding what to do with the block next. (if you can't immediately see another session removing the primary key you need, consider the issue of referential integrity.) this leads to another problem, the code is longer and more complex, and we need to use more CPU resources to get read consistency.

Moreover, using the old mechanism, a second session reading a changed block would see the changes immediately; with the new mechanism, a second session can see only that a block is subject to some private redo, so the second session is now responsible for tracking down the private redo and applying it to the block (if necessary), and then deciding what to do next with the block. (Think about the problems of referential integrity if you can't immediately see that another session has, for example, deleted a primary key that you need.) This leads to longer code paths, and more complex code, but even if the resulting code for read consistency does use more CPU than it used to, there is always an argument for making several sessions use a little more CPU as a way of avoiding a single point of contention.

Attention, Note.

An important optimization principle is often overlooked. Sometimes, if this means that everyone is working in a different location, rather than constantly clashing at the same competitive point, then it's better for everyone to do a little more work-competition is a waste of resources.

There is an important principle of optimization that is often overlooked. Sometimes it is better for everyone to do a little more work if that means they are operating in separate locations rather than constantly colliding on the same contention point-competition wastes resources.

I don't know how many different events events can force the session to construct a new version of the block from the private redo private redo and undo undo, but I know several events that cause the session to abandon the new policy before committing.

I don't know how many different events there are that could force a session to construct new versions of blocks from private redo and undo, but I do know that there are several events that result in a session abandoning the new strategy before the commit.

Oracle must abandon this new mechanism when the private redo thread private redo thread or the in-memory undo pool in-memory undo pool is full. As we saw earlier, each private zone is limited to about 64KB (or 128KB if you are running a 64-bit Oracle). When an area is full, Oracle creates a redo record, copies it to the public redo thread, and then continues to use the public redo thread in the old way.

An obvious case where Oracle has to abandon the new mechanism is when either the private redo thread or the in-memory undo pool becomes full. As we saw earlier, each private area is limited to roughly 64KB (or 128KB if you're running a 64-bit copy of Oracle). When an area is full, Oracle creates a single redo record, copies it to the public redo thread, and then continues using the public redo thread in the old way.

But there are other events that have led to this shift prematurely. For example, your SQL might trigger a recursive statement. To quickly check for possible causes and the number of times each cause occurs, you can connect to the database with a SYS user and run the following SQL (example 10.2.0.3):

But there are other events that cause this switch prematurely. For example, your SQL might trigger a recursive statement. For a quick check on possible causes, and how many times each has occurred, you could connect as SYS and run the following SQL (sample taken from 10.2.0.3):

Select ktiffcat, ktiffflc from x$ktiff

Unfortunately, although there are various IMU-related statistics in the v$sysstat dynamic performance view (for example, IMU refreshes), they don't seem to relate well to the numbers in x$structure, although if you ignore a few of them, you might think very close to finding matching bits.

Unfortunately, although there are various statistics relating to IMU in the v$sysstat dynamic performance view (e.g., IMU flushes), they don't seem to correlate terribly well with the figures from the x $structure-although, if you ignore a couple of the numbers, you can get quite close to thinking you've found the matching bits.

Four: Undo Complexity-Undo complexity

Undo is more complex than Redo. In theory, any process could access any undo record undo record at any time to "hide" data items that should not be seen yet. To effectively meet this requirement, Oracle stores undo records in a special table space in the database, which is called the undo table space. The code must then maintain various pointers to the undo record so that the process knows where to find the undo record it needs. The advantage of saving undo information undo information in a database of "normal" data files is that these blocks are buffered, written, and recovered exactly like each block in the database, and the basic code for managing undo blocks is the same as the code that handles other types of blocks.

Undo is more complicated than redo. Most significantly, any process may, in principle, need to access any undo record at any time to "hide" an item of data that it is not yet supposed to see. To meet this requirement efficiently, Oracle keeps the undo records inside the database in a special tablespace known, unsurprisingly, as the undo tablespace; then the code has to maintain various pointers to the undo records so that a process knows where to find the undo records it needs. The advantage of keeping undo information inside the database in "ordinary" data files is that the blocks are subject to exactly the same buffering, writing, and recovery algorithms as every block in the database-the basic code to manage undo blocks is the same as the code to handle every other type of block.

There are three reasons why processes need to read undo records, so there are three ways in which pointer chains pass through undo tablespaces. We will discuss these three methods in detail in Chapter 3, but I will make some preliminary comments on the two most common methods currently.

There are three reasons why a process needs to read an undo record, and therefore three ways in which chains of pointers run through the undo tablespace. We will examine all three in detail in Chapter 3, but I will make some initial comments about the commonest two uses now.

Be careful

The linked list of the undo record undo records is used to handle read consistency, rollback changes, and derive a commit scn that is "lost" due to delayed block cleanup. The third topic will be postponed to the third chapter

Note

Linked lists of undo records are used to deal with read consistency, rolling back changes, and deriving commit SCNs that have been "lost" due to delayed block cleanout. The third topic will be postponed until Chapter 3.

4.1 Read Consistency read consistency

The first and most frequently invoked use of undo is read consistence, and I have made a brief comment on read consistence. The existence of UNDO allows the session to see the older version of the data if it has not yet seen the newer version.

The first, and most commonly invoked, use of undo is read consistency, and I have already commented briefly on read consistency. The existence of undo allows a session to see an older version of the data when it's not yet supposed to see a newer version.

The requirement for read consistency means that a block must contain a pointer to a undo record, which describes how to hide changes to the block. However, there may be a large number of changes to hide, and there is not enough space in a block to hold so many pointers. Therefore, Oracle allows a limited number of pointers per block (one pointer per concurrent transaction affecting the block), which are stored in ITL entries. When a process creates a undo record, it (usually) overwrites an existing pointer, saving the previous value as part of the undo record.

The requirement for read consistency means that a block must contain a pointer to the undo records that describe how to hide changes to the block. But there could be an arbitrarily large number of changes that need to be concealed, and insufficient space for that many pointers in a single block. So Oracle allows a limited number of pointers in each block (one for each concurrent transaction affecting the block), which are stored in the ITL entries. When a process creates an undo record, it (usually) overwrites one of the existing pointers, saving the previous value as part of the undo record.

After updating three rows of data in a block, take a look at the undo record that I showed earlier:

Take another look at the undo record I showed you earlier, after updating three rows in a single block:

This undo record shows that the table block in the fifth row I updated points to, and we can see the record 0xf in the undo block from the second row of the dump. As you can see from the bottom of the dump seven lines up, this record has op:C, which tells us that it is a continuation of a previous update of the same transaction. This lets Oracle know that the rest of the line uba:0x0080009a.09d4.0d is part of the information used to recreate the old version of the block: since xxxxxx (78s) is copied back to the second column of row 4, the value 0x0080009a.09d4.0d must be copied back to ITL entry 2.

The table block holding the fifth row I had updated was pointing to this undo record, and we can see from the second line of the dump that it is record 0xf in the undo block. Seven lines up from the bottom of the dump you see that this record has op: C, which tells us that it is the continuation of an earlier update by the same transaction. This lets Oracle know that the rest of the line uba: 0x0080009a.09d4.0d is part of the information that has to be used to re-create the older version of the block: as the xxxxxx (78s) are copied back to column 2 of row 4, the value 0x0080009a.09d4.0d has to be copied back to ITL entry 2.

Of course, once Oracle takes these steps to rebuild an old version of the block, it will find that the data has not gone far enough, but the pointer in ITL 2 now tells it where to find the next undo record to apply. In this way, a process can gradually go backwards over time; a pointer in each ITL entry tells Oracle where to apply an undo record, and each undo record contains information that makes the ITL entry backward in time and the data backward in time.

Of course, once Oracle has taken these steps to reconstruct an older version of the block, it will discover that it hasn't yet gone far enough, but the pointer in ITL 2 is now telling it where to find the next undo record to apply. In this way a process can gradually work its way backward through time; the pointer in each ITL entry tells Oracle where to find an undo record to apply, and each undo record includes the information to take the ITL entry backward in time as well as taking the data backward in time.

4.2 Rollback

Second, the main purpose of undo is to roll back changes, either using an explicit rollback (or rollback to a SavePoint), or Oracle issuing an implicit statement-level rollback because a step in a transaction fails.

Read consistency is about a single block and finds a linked list of all undo records for that block. Rollback is related to the history of the transaction, so we need a linked list that traverses all the undo records of the transaction in the correct order (which in this case means reverse).

The second, major use of undo is in rolling back changes, either with an explicit rollback (or rollback to savepoint) or because a step in a transaction has failed and Oracle has issued an implicit, statement-level rollback.

Read consistency is about a single block, and finding a linked list of all the undo records for that block. Rolling back is about the history of a transaction, so we need a linked list that runs through all the undo records for a transaction in the correct (which, in this case, means reverse) order.

Be careful

Here is a simple example of why a "reverse" link is needed to undo a record. " Suppose we update a row twice, changing the value of a single column from A to B, and then from B to C, resulting in two undo records. If we want to reverse the change, we must change C back to B before we can apply the undo record of "change B back to A"; in other words, we must apply the second undo record before the first undo record is applied.

Note

Here is a simple example demonstrating why we need to link the undo records "backward." Imagine we update a row twice, changing a single column value from A to B and then from B to C, giving us two undo records. If we want to reverse the change, we have to change the C back to B before we can apply an undo record that says "change a B to an A"; in other words, we have to apply the second undo record before we apply the first undo record.

Looking at the sample undo record again, we can see the signs of the linked list. Line 3 of the dump includes the rci 0x0e entry. This tells Oracle that the undo record created immediately before the undo record is number 14 (0x0e) in the same undo block. It is possible, of course, that the previous cancel record will be in a different undo, but only the record of the current undo undo block should be the first cancel record, in which case the rci entry will cancel the record by giving the four lines below the rdba: entry to the previous block address. If a block must be returned, the last record of the block is usually a required record, although technically, what you need is the record pointed to by irb: entry. However, if you have rolled back to savepoint, then irb: entry may not point to the last record.

Looking again at the sample undo record, we can see signs of the linked list. Line 3 of the dump includes the entry rci 0x0e. This tells Oracle that the undo record created immediately before this undo record was number 14 (0x0e) in the same undo block. It's possible, of course, that the previous undo record will be in a different undo block, but that should be the case only if the current undo record is the first undo record of the undo block, in which case the rci entry would be zero and the rdba: entry four lines below it would give the block address of the previous undo record. If you have to go back a block, then the last record of the block will usually be the required record, although technically what you need is the record pointed at by the irb: entry. However, the only case in which the irb: entry might not point to the last record is if you have done a rollback to savepoint.

There is an important difference between read consistency and rollback. For read consistency, we copied the block in memory and applied the undo record undo records to that block data block, and once we have finished it, we can quickly discard it; when we roll back, we get the current block current block and apply undo record to it. This has three important implications:

There's an important difference between read consistency and rolling back, of course. For read consistency we make a copy of the data block in memory and apply the undo records to that block, and it's a copy of the block that we can discard very rapidly once we've finished with it; when rolling back we acquire the current block and apply the undo record to that. This has three important effects:

1. Block data block is the current block current block, so it is the version of the block that must eventually be written to disk.

two。 Because it is the current block, redo will be generated when we change it (even if we "change it back to the original way").

3. Because Oracle has a crash recovery mechanism to clean up incidents as efficiently as possible, we need to make sure that undo records are marked as "undo applied" when using them, which results in more redo.

1. The data block is the current block, so it is the version of the block that must

Eventually be written to disc.

2. Because it is the current block, we will be generating redo as we change it (even

Though we are "changing it back to the way it used to be").

3. Because Oracle has crash-recovery mechanisms that clean up accidents as

Efficiently as possible, we need to ensure that the undo record is marked as "undo

Applied "as we use it, and doing that generates even more redo.

If the undo record has been used for rollback, the fourth line of the dump should look like this

If the undo record was one that had already been used for rolling back, line 4 of the dump would have looked like this:

Undo type: Regular undo User Undo Applied Last buffer split: No

In the original block dump, the flag for the user to undo the application is only 1 byte, not a 17-character string.

The rollback involves a lot of work, and the rollback takes roughly the same time as the original transaction, which may result in a similar number of redo. But you must keep in mind that rollback is an activity of changing blocks, so you must reacquire, modify, and write those blocks, and write a redo that describes how you change them. In addition, if the transaction is a large, long-running transaction, you may find that some of the blocks you changed have been written to disk and cleared from the cache-so before you can roll them back, they must be read from disk to memory!

In the raw block dump, the User Undo Applied flag is just 1 byte rather than a 17-character string.

Rolling back involves a lot of work, and a rollback can take roughly the same amount of time as the original transaction, possibly generating a similar amount of redo. But you have to remember that rolling back is an activity that changes data blocks, so you have to reacquire, modify, and write those blocks, and write the redo that describes how you've changed those blocks. Moreover, if the transaction was a large, long-running transaction, you may find that some of the blocks you've changed have been written to disc and flushed from the cache-so they'll have to be read from disc before you can roll them back!

Be careful

Some systems use Oracle tables to save "temporary" or "temporary" information. One of the strategies used is to insert data without committing it, so that read consistency makes it private to the session, and then rollback makes the data "disappear". The potentially high cost of rollback is just one of many flaws in this strategy. You can use a global temporary table instead of this strategy.

Note

Some systems use Oracle tables to hold "temporary" or "scratchpad" information. One of the strategies used with such tables is to insert data without committing it so that read consistency makes it private to the session, and then roll back to make the data "go away." There are many flaws in this strategy, the potentially high cost of rolling back being just one of them. The ability to eliminate the cost of rollback is one of the things that makes global temporary tables useful.

Of course, there are other overhead introduced through rollback. When a session creates an undo record, it fetches, pins, and fills one undo block at a time; when it rolls back, it fetches one record at a time from the undo block, releasing and refetching the block for each record. This means that more rollback buffer access is generated on the undo block than when the transaction was initially executed. Furthermore, every time Oracle gets an undo record, it checks to see if the tablespace to which it should be applied is still online (if not, Oracle transfers the undo record to the save undo segment in the system tablespace); this appears as get on the dictionary cache (especially the dc_tablespaces cache).

There are other overheads introduced by rolling back, of course. When a session creates undo records, it acquires, pins, and fills one undo block at a time; when it is rolling back it gets one record from an undo block at a time, releasing and reacquiring the block for each record. This means that you generate more buffer visits on undo blocks to roll back than you generated when initially executing the transaction. Moreover, every time Oracle acquires an undo record, it checks that the tablespace it should be applied to is still online (if it isn't, Oracle will transfer the undo record into a save undo segment in the system tablespace); this shows up as get on the dictionary cache (specifically the dc_tablespaces cache).

We can end the comment on the rollback with one last special small detail. If the session issues a rollback command, the step to complete the rollback is to commit. We will spend a little more time on this in the third chapter.

We can finish the comments on rolling back with one last quirky little detail. If your session issues a rollback command, the step that completes the rollback is a commit. We'll spend a little more time on that in Chapter 3.

Summary Summary:

In some ways, redo redo is a very simple concept: every change to a block in the data file is described by the redo change vector redo change vector, and these change vectors are immediately written to the redo log buffer redo log buffer (almost), and eventually to the redo log file redo log file.

In some ways redo is a very simple concept: every change to a block in a data file is described by a redo change vector, and these change vectors are written to the redo log buffer (almost) immediately, and are ultimately written into the redo log file.

When we make changes to the data, including index entries and structural metadata, we also create undo records in the undo tablespace that describe how to reverse these changes. Because the undo tablespace is just another set of data files, we created a redo change vector to describe the undo records we stored there.

As we make changes to data (which includes index entries and structural metadata), we also create undo records in the undo tablespace that describe how to reverse those changes. Since the undo tablespace is just another set of data files, we create redo change vectors to describe the undo records we store there.

In earlier versions of Oracle, the change vector change vectors was usually grouped together in pairs-a description of a forward change forward change and a description of an undo record undo record- merging this pair of record into a redo record and writing it to redo log buffer.

In earlier versions of Oracle, change vectors were usually combined in pairs-one describing the forward change, one describing the undo record-to create a single redo record that was written (initially) into the redo log buffer.

In newer versions of Oracle, the step of moving the change vector change vectors to the redo log buffer redo log buffer in the OLTP system is regarded as an important bottleneck, and a new mechanism is introduced to store all operations in a session in private redo log buffer and write to public redo log buffer together after the transaction is completed.

In later versions of Oracle, the step of moving change vectors into the redo log buffer was seen as an important bottleneck in OLTP systems, and a new mechanism was created to allow a session to accumulate all the changes for a transaction "in private" before creating one large redo record in the redo buffer.

The new mechanism strictly limits what the session does before flushing its change vector to the redo log buffer and switching to the old mechanism, and there are a variety of events that cause this switch to occur in advance.

The new mechanism is strictly limited in the amount of work a session will do before it flushes its change vectors to the redo log buffer and switches to the older mechanism, and there are various events that will make this switch happen prematurely.

When redo is a simple write-in-ignore stream operation, undo may reread frequently in the current activity of the database, and undo records must be linked together in different ways to achieve effective access. Reading consistency Read consistency requires a chain of undo records for a given block; rollback rolling back requires a chain of undo records for a given transaction. (there is also a third chain, which will be discussed in Chapter 3.)

While redo operates as a simple "write it and forget it" stream, undo may be frequently reread in the ongoing activity of the database, and undo records have to be linked together in different ways to allow for efficient access. Read consistency requires chains of undo records for a given block; rolling back requires a chain of undo records for a given transaction. (And there is a third chain, which will be addressed in Chapter 3.)

2020-02-02 Chen Juchao

-translated and sorted out from

"Oracle Core Essential Internals for DBAs and Developers"

Chapter 2:Redo and Undo

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.