Secret of oracle logic IO: Current Mode
[中文]
Author: fuyuncat
Source: www.HelloDBA.com
Date: 2009-11-09 01:02:38
When need update data, oracle will read the data block in current mode. Let's see the IO in an UPDATE statement, here will be more interesting things be found.
- HELLODBA.COM>conn demo/demo
- Connected.
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
- Session altered.
- HELLODBA.COM>set autot trace stat
- HELLODBA.COM>update tt set x=1;
- 2 rows updated.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 4 db block gets //表示当前模式读过程涉及到的数据块的读取次数。
- 7 consistent gets //表示一致性模式读过程涉及到的数据块的读取次数。两种读取过程涉及的数据块有重合的地方就是属于表的数据块,undo段的undo
- 块是一致性读过程要读取的块。
- 8 physical reads
- 824 redo size
- 665 bytes sent via SQL*Net to client
- 553 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 2 rows processed
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>update tt set x=1;
- 2 rows updated.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 3 db block gets
- 7 consistent gets
- 7 physical reads
- 536 redo size
- 668 bytes sent via SQL*Net to client
- 553 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 2 rows processed
Check the 1st UPDATE, it has 4 db block gets, means the reads in current mode. Look into the trace, besides the operations we find in previous traces, we can find these entries.
- pin kdswh01: kdstgr dba 140e64f:1 time 3828486551
- pin kduwh01: kdusru dba 140e64f:1 time 3828486616
- WAIT #1: nam='db file sequential read' ela= 7907 file#=2 block#=9 blocks=1 obj#=0 tim=3828495546
- pin ktuwh01: ktugus dba 800009:17 time 3828495628
- WAIT #1: nam='db file sequential read' ela= 3984 file#=2 block#=7227 blocks=1 obj#=0 tim=3828499685
- pin kcbwh2: kcbchg1 dba 801c3b:18 time 3828499816
- pin release 4305 ktuwh01: ktugus dba 800009:17
- pin release 176 kcbwh2: kcbchg1 dba 801c3b:18
- pin release 13432 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 3828500148
- pin kduwh01: kdusru dba 140e650:1 time 3828500249
- pin kcbwh5: kcbchg1 dba 801c3b:18 time 3828500352
- pin release 63 kcbwh5: kcbchg1 dba 801c3b:18
- pin release 207 kduwh01: kdusru dba 140e650:1
Let me guess what are these new operations:
- Kdusru: Read in current mode for update
- ktugus: Get Undo Segment Header
- kcbchg1: Change buffer content
P.S. the UNDO block's class:
- 17,19,21...: UNDO header;
- 18,20,22...: UNDO block.
All of these operations will lead to the current mode reading. In this UPDATE, there 4 db block gets, 2 data block (140e64f, 140e650), 1 undo header (800009), and 1 undo block (801c3b). Pls noted the undo block 801c3b be read twice for the 2 records, and just 1 current mode in one transaction.(译文:虽然地址为 801c3b 的undo 块被读取两次因为(它含)有两条记录,但是在一个事务里只算作一次的当前模式读。)
In the second UPDATE, there is bit of difference.
- There is no UNDO header read --- just 1 UNDO header read for each transaction;(在一次事务中undo段头只是读取一次,之后被重用)
- Since the buffer cache be flushed, even though the undo block is same as the one in the 1st UPDATE, it still be read in current mode.
Therefore, there are 3 db block gets in the 2nd update.(就是比第一次update少了一次undo段头的读取)
- pin kduwh01: kdusru dba 140e64f:1 time 3832560411
- WAIT #2: nam='db file sequential read' ela= 201 file#=2 block#=7227 blocks=1 obj#=0 tim=3832560683
- pin kcbwh2: kcbchg1 dba 801c3b:18 time 3832560736
- pin release 69 kcbwh2: kcbchg1 dba 801c3b:18
- pin release 477 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 3832561310
- pin kduwh01: kdusru dba 140e650:1 time 3832561392
- pin kcbwh5: kcbchg1 dba 801c3b:18 time 3832561465
- pin release 74 kcbwh5: kcbchg1 dba 801c3b:18
- pin release 199 kduwh01: kdusru dba 140e650:1
- EXEC #2:c=15625,e=17973,p=7,cr=7,cu=3,mis=0,r=2,dep=0,og=4,tim=3832561659
One thing to be noted, the pin of current mode read was released immediately.
Here we study another case, 2(nd) transactions with 3 UPDATE statement, no buffer be flushed during the transactions.
- HELLODBA.COM>conn demo/demo
- Connected.
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
- Session altered.
- HELLODBA.COM>set autot trace stat
- HELLODBA.COM>update tt set x=1;
- 2 rows updated.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 4 db block gets
- 7 consistent gets
- 8 physical reads
- 904 redo size
- 665 bytes sent via SQL*Net to client
- 553 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 2 rows processed
- HELLODBA.COM>rollback; //对比rollback前后的 db block gets 、consistent gets 、physical reads三个值,只有物理读有变化,其他不变。
- 其他两个不变,说明rollback没有将之前update时从磁盘读到内存上的表的数据块销毁掉,而rollback后再update的物理读为1,因为rollback会使第一次update对应的undo块给释放掉,不与该事务对应,而第二次update时因要保存之前的数据而重新为该事务从数据文件上的undo表空间上读取一个undo块(不一定是空,只要该块还可以放undo记录即可)到内存上来。
- Rollback complete.
- HELLODBA.COM>update tt set x=1;
- 2 rows updated.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 4 db block gets
- 7 consistent gets
- 1 physical reads
- 788 redo size
- 668 bytes sent via SQL*Net to client
- 553 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 2 rows processed
- HELLODBA.COM>update tt set x=1;
- 2 rows updated.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 2 db block gets
- 7 consistent gets
- 0 physical reads
- 536 redo size
- 668 bytes sent via SQL*Net to client
- 553 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 2 rows processed
The 1st(应该是指第二个) UPDATE is same as in the previous case. As the buffer not be flushed, there 1 new UNDO block be physical read in the 1st(应该是指第二个) UPDATE of the 2nd transaction, with 4 db block gets.
- ...
- pin kduwh01: kdusru dba 140e64f:1 time 680961643
- WAIT #2: nam='db file sequential read' ela= 6579 file#=2 block#=73 blocks=1 obj#=0 tim=4975935594
- pin ktuwh01: ktugus dba 800049:25 time 680968375
- pin ktuwh03: ktugnb dba 8012cb:26 time 680968434
- pin release 141 ktuwh01: ktugus dba 800049:25
- pin release 123 ktuwh03: ktugnb dba 8012cb:26
- pin release 6954 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 680968657
- pin kduwh01: kdusru dba 140e650:1 time 680968719
- pin kcbwh5: kcbchg1 dba 8012cb:26 time 680968776
- pin release 49 kcbwh5: kcbchg1 dba 8012cb:26
- pin release 146 kduwh01: kdusru dba 140e650:1
- EXEC #2:c=0,e=7657,p=1,cr=7,cu=4,mis=0,r=2,dep=0,og=4,tim=4975936219
- ...
While in the 2nd(应该是指第三个) UPDATE of the 2nd transaction, the UNDO block is be reused(undo块被重用,是因为第二个update后没有rollback,故而第二个update时读取的undo块还是该事务对应着。), so it just has 2 db block gets.(与第一个事务里的第一次update相比,少了一次undo段头块和一次undo块的读取,都是因为重用的关系)
- ...
- pin kduwh01: kdusru dba 140e64f:1 time 680977322
- pin kcbwh5: kcbchg1 dba 8012cb:26 time 680977384
- pin release 63 kcbwh5: kcbchg1 dba 8012cb:26
- pin release 166 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 680977538
- pin kduwh01: kdusru dba 140e650:1 time 680977595
- pin kcbwh5: kcbchg1 dba 8012cb:26 time 680977642
- pin release 48 kcbwh5: kcbchg1 dba 8012cb:26
- pin release 136 kduwh01: kdusru dba 140e650:1
- EXEC #2:c=0,e=829,p=0,cr=7,cu=2,mis=0,r=2,dep=0,og=4,tim=4975945080
- ...
--- Fuyuncat TBC ---
注释:
1、我们可以看到,在当前模式读的过程中,读取的undo块(还有undo段头块)是为了本操作时保留数据行被本操作修改前的数据用的。这些读取的undo块(还有undo段头块)是对应于本操作所在的事务的undo块。
而在一致性读的过程中,读取的undo块(还有undo段头块)是为了查看本操作开始执行时表的内容而去通过undo机制的一致性读作用来构造CR块用的。这些读取的undo块(还有undo段头块)不是对应于本操作所在的事务的undo块,而对应于其他事务的undo块。
(?)2、服务器进程根据数据块的oracle表示的地址(即文件号+块号)在内存上找到对应的数据块的过程,应该叫做一次逻辑读。当服务器进程找到该数据块后,就可以知道该数据块在内存上的(内存)地址。这样以后就可以直接通过这个内存地址找到该数据块,而不用通过oracle表示的地址来间接找到内存上的该数据块。