新东方培训-4

昨天参加了新东方oracle系列培训第四次课，主讲老师是oracle的高级顾问kamus。

这节课的主要内容是undo和redo。

 

系统地介绍了redo和undo的概念、他们之间如何协同工作、nologging、块清除、commit和rollback的区别、一致读等内容，现总结如下：

 

1、  redo

 

redo记录了数据库中各种操作引起的数据变化，包括：

Include all changes made by DML statements

INSERT

UPDATE

DELETE

SELECT FOR UPDATE

Do not include DML statement text

Include all changes made to dictionary objects by DDL statements

Include DDL statement text (9.0.1 and above)

Include all changes made by recursive statements

在写data block buffer前要先写redo log buffer

在写data file前要先写redo log file

 

写redo log file 的条件：commit、1M redo log buffer满、1/3redo log buffer满

Redo log中以redo record的方式记录

 

查询操作产生的redo量：通过执行mystat来得到现在的redo量，然后执行操作，操作结束后再执行mystat 来等到redo量，两者相减就是操作所产生的redo量。

Scripts from Tom Kyte

 

Mystat:

set verify off

column value new_val V

define S="&1"

set autotrace off

select a.name, b.value

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' || lower('&S')||'%'

/

 

Mystat2:

set verify off

select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' || lower('&S')||'%'

/

系统产生的redo量可以查询v$sysstat

Temp table不产生redo，只有undo产生的一点redo信息。Temp table上不要进行delete操作。

 

2、  undo

 

undo记录了数据块改变前的镜像。

 

Rollback segment和undo segment在概念上是一样的，815以前叫rollback segment

Undo记录的是数据逻辑意义上的一致，不是物理意义上的一致；回滚后的数据只是查询一致，数据块并不一定和以前一直。

 

3、  commit和rollback

 

commit只将undo中相应的位置于可用，不会清除块头上的信息，所以commit时很快。

在大批量commit后要做select表或analyze table。这样可以将块头的信息更新。

Commit不耗费资源，提交的数据量的多少与commit的速度没有关系，所以在应用系统允许的情况下尽量批量提交batch commit。

 

Rollback是昂贵的操作，系统认为rollback是有问题的，rollback操作不会释放锁，其他事务会来继承锁，commit能释放锁。

性能报表中的Rollback per transaction %应小于10%

 

4、  nologging

 

nologging可以在建表的时候指定，也可以在事务中指定。

设置nologging后仍会产生少量的redo，因为undo也会产生redo。

nologging 和归档模式有关。

 

5、  修改数据的过程：

 

1）、Get a rollback segment

2）、Allocate TX table slot in  rollback segment header

3）、Get a ITL in data block header, ITL point to TX table slot

4）、Store before image of effected data (or columns) in Undo record, TX table slot indicate the DBA (Data Block Address)

5）、Lock the row, modify lb-lock-byte to indicate there is a ITL

6）、Modify row data

 

6、  一致读的过程：

 

1. Read the Data Block. If the block is resident in memory, create a clone to perform the undo.

2. Read the Row Header.

3. Check the LockByte to determine if there is an ITL entry.

4. Read the ITL to determine the Transaction ID.

5. Read the Transaction Table. If the transaction has been committed and has a SCN less than the query’s SCN, cleanout the block and move on the next data block (if required) and Step 1.

6. Read the last undo block indicated

7. Compare the block transaction id with the transaction table transaction id. If the Transaction ID in the undo block does not equal the Transaction ID from the Transaction Table, then signal an ORA-01555 “Snapshot Too Old.”.

8. Starting with the head undo entry, apply the changes to the block (the clone block in the memory).

9. Follow rci value in undo chain to applied all the undo record in that transaction if that row updated several times.

10. If the tail undo entry (the last one read) indicates another data block address (rec# value in undo dump), read the indicated undo block into memory. Repeat 7 & 8 until the first record does not contain a value for the data block address.

11. When there is no previous data block address, the transaction has been undone.

12. If the undo entry contains

a) a pointer to a previous transaction undo block address, read the TxID in the previous transaction undo block header and read the appropriate Transaction Table entry. Return to step 5.

b) an ITL record, restore the ITL record to the data block. Return to step 4.