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.
SQL代码
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.
SQL代码
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段头的读取)
SQL代码
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.
SQL代码
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.
SQL代码
...
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块的读取，都是因为重用的关系)
SQL代码
...
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表示的地址来间接找到内存上的该数据块。
扫一扫
3468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
