Oracle Basic Data Change principle analysis (redo and undo)--oracle Core Technology reading notes one

In Oracle We do some updates, how Oracle's bottom layer is flowing, this article is to explain.

      I. Principle

Suppose we update a row of data in an OLTP system that has updated many fragmented records. So the actual steps for this update are as follows:

1. Create a redo change vector that describes how to insert an undo record into the undo block (that is, describe the change to the undo block)

2. Create a redo change vector that describes the change of the data block (that is, the change of the data)

3. Merge the two redo change vectors into a log record and write to the redo log buffer (for future redo)

4. Insert an undo record into the undo block (for future fallback)

5. Change the data in the data block (this is where the data is actually changed)

Let's take a sample to show the process.

two. Practice

We first create a table, and then update the first block of data in the table, 3,4,5, and a record of the second block is interspersed after each update. That is, this update will update 6 records, and he will change the third column of each record------a field of type varchar2, changing it from xxxxxx (lowercase 6 characters) to YYYYYYYYYY (uppercase 10 characters).

1. cmd command line login with SYS user

2. Preparation (create several stored procedures to dump blocks, dump redo logs, etc.)

See these scripts: http://download.csdn.net/detail/liwenshui322/7912909

3. Preparatory work (mainly clearing the Recycle Bin delete information, set the block read cost, valuation calculation basis, etc.)

Start Setenvset timing Offexecute dbms_random.seed (0) drop table t1;beginbeginexecute Immediate ' purge recyclebin '; --Emptying the Recycle Bin exceptionwhen others then null;end;begindbms_stats.set_system_stats (' MBRC ', 8); -multi-block reads for 8 dbms_stats.set_system_stats (' Mreadtim ', 26); --average time to block reads is 26 milliseconds dbms_stats.set_system_stats (' Sreadtim ', 12); --Average time per block read is 30 milliseconds dbms_stats.set_system_stats (' cpuspeed ', 800); --CPU can perform 800,000,000 operations per second Exceptionwhen others then null;end;beginexecute immediate ' begin DBMS_STATS.DELETE_SYSTEM_ Stats end; '; --Delete System statistics Exceptionwhen Others then null;end;beginexecute immediate ' alter session set ' _optimizer_cost_model ' =io '; --based on Io to calculate the valuation exceptionwhen others then null;end;end;/

4. Creating tables and Indexes

CREATE TABLE T1asselect2 * rownum-1id,rownumn1,cast (' xxxxxx ' as VARCHAR2 (Ten)) V1,rpad (' 0 ', ' 0 ') Paddingfromall_ Objectswhererownum <= 60union allselect2 * rownumid,rownumn1,cast (' xxxxxx ' as VARCHAR2 (Ten)) V1,rpad (' 0 ', 100, ' 0 ') Paddingfromall_objectswhererownum <= 60;create index t1_i1 on t1 (ID);

5. Statistical information

Begindbms_stats.gather_table_stats (ownname = User,tabname = ' T1 ', method_opt  = ' For all columns size 1 ') ; end;/

6. View the blocks used by the table, and how many pieces of data each one has

Select Dbms_rowid.rowid_block_number (ROWID) Block_number, COUNT (*) Rows_per_blockfrom T1 GROUP by dbms_rowid.rowid_ Block_number (ROWID) Order byblock_number;

We'll see a total of two blocks, with 60 records for each block.

7. Dump Data blocks

alter system switch Logfile;execute dump_seg (' t1 ')

8. Do updates

update/*+ index (t1 t1_i1) */t1setv1 = ' yyyyyyyyyy ' Whereid between 5 and 9;

9. Dump the data block and undo block after the update block (after the CHECKPOINT statement executes, the next statement waits for 5,6s to execute, and the checkpoint only tells Oracle to write dirty data to disk, which takes a little time)

Pause Query The IMU structures now (@core_imu_01. sql)

Alter system checkpoint;--a checkpoint that writes data to disk

Execute dump_seg (' t1 ') Execute Dump_undo_block

10. Dump Redo Block

Rollback;commit;execute Dump_log

11. Locate the Dump information file location

Select Sid from V$mystat where rownum=1;--query results are passed in to the next Sqlselect d.value| | ' /'|| Lower (RTrim (i.instance, Chr (0))) | | _ora_ ' | | p.spid| | '. TRC ' Trace_file_name from    (select P.spid from V$session S, v$process p      where s.sid= ' 133 ' and p.addr = s.paddr) p,    (select t.instance from V$thread t,v$parameter v      where v.name = ' thread ' and (v.value = 0 or t.thread# = To_numb ER (v.value))) I,   (select value from v$parameter where name = ' user_dump_dest ') D;

12. Open File
Looking at a few key sections below, we look at the 5th piece of data in the first block, and we change the third column of this row of data from xxxxxx to YYYYYYYYYY.

Before update:

tab 0, row 4, @0x1d3ftl:117 fb:--h-fl--lb:0x0  cc:4col  0: [2]  C1 0acol  1: [2]  C1 06col  2: [ 6]  78col  3: [100] 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30

We see that the length of the col2 is 6, then the 6 x (the hexadecimal ASCII code is 78).

After update:

tab 0, row 4, @0x2a7tl:121 fb:--h-fl--lb:0x2  cc:4col  0: [2]  C1 0acol  1: [2]  C1 06col  2: [10 ]  59col  3: [100] 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 3 0 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30

We can see that the col2 length becomes 10, is 10 (hexadecimal ASCII code of Y is 59), and we see that the line address from @0x1d3f to @0x2a7, indicating that the space of this line can not accommodate the new data, changed a place. (Check the row directory can also find this) at the same time, we can see that lb (lock byte) is changed from 0x0 to 0x2, indicating that this record is locked by the transaction identified by the second transaction slot in the block's list of transaction slots. The transaction slots can be seen at the block header.

Below, look at the 5th data in redo what is saved (how to ensure data redo), in the file search tabn:0 slot:4 (0x4) flag:0x2c Lock:2, we will find such a description

Change #6 typ:0 Cls:1 afn:1 dba:0x004161c9 obj:77125 scn:0x0000.002796b6 seq:2 op:11.5 enc:0 RBL:0KTB Redo op:0x02  ver:0x01  compat bit:4 (post-11) padding:0op:c  uba:0x00c0055a.0123.27kdo op code:u RP row Dependencies Disabled  xtype:xa flags:0x00000000  bdba:0x004161c9  hdba:0x004161c8itli:2  ISPAC : 0  maxfr:4863tabn:0 slot:4 (0x4) flag:0x2c lock:2 ckix:50ncol:4 nnew:1 size:4col  2: [ten]  59 59 59 59 59 59 59 59 59 59

This describes a change in the world, we see the 6th line op code: is urp (update row slice), line seventh we can see the updated block address BDBA and the address of the segment hdba.

Line eighth Itli:2 indicates that the transaction performing the update operation is using the second transaction slot, consistent with what is seen in the data block.

Line Nineth tabn:0 Slot:4 shows that we are updating the 5th record of the first table (a block of data that might store multiple tables).

The last two lines, we can see that this record has 4 columns (NCLO:4), modified a column (nnew:1), the length of the increase of 4 (size:4). and change the value of column 3rd to yyyyyyyyyy. (The modified data is saved to make it easy to redo)

Next, look at the 5th data in the Undo how to save (how to ensure data fallback), in the file search tabn:0 slot:4 (0x4) flag:0x2c, we will find the following paragraph description:

*-----------------------------* Rec #0x27  slt:0x04  objn:77125 (0x00012d45)  objd:77125  tblspc:0 ( 0x00000000) *       Layer: one  (Row)   opc:1   RCI 0x26   undo Type:  Regular undo last   Buffer split:< C11/>no Temp Object:  no tablespace undo:  No rdba:0x00000000*-----------------------------KDO undo Record:  KTB Redo op:0x02  ver:0x01  compat bit:4 (post-11) padding:0op:c  uba:0x00c0055a.0123.25kdo op code:urp Disabled row Dependencies  xtype:xa flags:0x00000000  bdba:0x004161c9  hdba:0x004161c8itli:2  ispac:0  maxfr:4863tabn:0 slot:4 (0x4) flag:0x2c lock:0 ckix:50ncol:4 nnew:1 Size: -4col  2: [6]  78 78 78 78) 78 78

Focus on the following six rows of data, in fact, with the data in front of the redo, that is, size=-4,col2 into 6 (x's hexadecimal ASCII code is 78). (Ensure the data can go back to the previous version)

Finally, we can find the description of the undo block change in the redo of the dump, the file inside the search tabn:0 slot:4 (0x4) flag:0x2c lock:0, we will locate the following paragraph description:

Change #11 typ:0 cls:36 afn:3 dba:0x00c0055a obj:4294967295 scn:0x0000.002796b6 seq:4 op:5.1 enc:0  Rbl:0ktudb redo:siz:92 spc:4078 flg:0x0022 seq:0x0123 rec:0x27            XID:  0x000a.004.00000467  Ktubu redo:slt: 4 rci:38 opc:11.1 objn:77125 objd:77125 tsn:0undo type:  Regular undo       Undo Type: Last  buffer split:  N o tablespace undo:  No              0x00000000kdo undo Record:ktb Redo op:0x02  ver:0x01  compat bit:4 (post-11) Paddi Ng:0op:c  uba:0x00c0055a.0123.25kdo Op code:urp row dependencies Disabled  xtype:xa flags:0x00000000  BD BA:0X004161C9  hdba:0x004161c8itli:2  ispac:0  maxfr:4863tabn:0 slot:4 (0x4) flag:0x2c lock:0 ckix:50 Ncol:4 nnew:1 Size: -4col  2: [6]  78 78 78 78 78 78

Line five, which is a description of the undo block change, we can see that the countdown line is very similar to the data in undo, because the undo block is recorded here.

Since then, we can basically see how Oracle describes the changes in the data before it actually changes the data.

Oracle Basic Data Change principle analysis (redo and undo)--oracle Core Technology reading notes one