Secret of oracle logic IO: Consistent Gets

最新推荐文章于 2021-04-16 03:48:06 发布

haiross

最新推荐文章于 2021-04-16 03:48:06 发布

阅读量765

点赞数

分类专栏： oracle体系架构 oracle疑问集文章标签： undo机制一致性读

oracle体系架构同时被 2 个专栏收录

260 篇文章 6 订阅

订阅专栏

oracle疑问集

81 篇文章 1 订阅

订阅专栏

Secret of oracle logic IO: Consistent Gets

[中文]

Author: fuyuncat

Source: www.HelloDBA.com

Date: 2009-11-07 14:57:13

Then, let's study the Consistent Gets case. The obvious feature of consistent gets is that it will read the undo block to apply to current data block correspond to the SCN. To monitor the undo application, we turn the 10201 event trace on. Here is the demo,

SQL代码

-- Session 1: Update without commit
HELLODBA.COM>update tt set x=2;
2 rows updated.
HELLODBA.COM>update tt set x=3;
2 rows updated.
-- Session 2:
HELLODBA.COM>conn demo/demo
Connected.
HELLODBA.COM>alter system flush buffer_cache;
System altered.
HELLODBA.COM>ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';
Session altered.
HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
HELLODBA.COM>set autot trace stat
HELLODBA.COM>select * from tt;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
8 physical reads
172 redo size
440 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

13 Logical reads, 6 more than the case without CR undo application. Look into the trace file, see what we catched.

First, it still need read the segment header twice（为什么要读取两次表的段头块呢？是因为表tt有两个数据行（被修改）的关系吗？涉及到表的段头与表的数据块间的关系吗？）, then read the data block in sequence,

SQL代码

...
WAIT #3: nam='db file sequential read' ela= 22808 file#=5 block#=58955 blocks=1 obj#=200943 tim=3948903234
pin ktewh25: kteinicnt dba 140e64b:4 time 3948903366 //read the segment header twice,指的就是这里一处，段头块的地址为140e64b，即file#=5 block#=58955
pin ktewh26: kteinpscan dba 140e64b:4 time 3948903443 //read the segment header twice,指的就是这里一处，段头块的地址为140e64b，即file#=5 block#=58955
WAIT #3: nam='db file scattered read' ela= 572 file#=5 block#=58956 blocks=5 obj#=200943 tim=3948904149
pin kdswh01: kdstgr dba 140e64c:1 time 3948904251 //140e64c到140e64f都是数据块（data block）的地址
pin kdswh01: kdstgr dba 140e64d:1 time 3948904308
pin kdswh01: kdstgr dba 140e64e:1 time 3948904354
pin kdswh01: kdstgr dba 140e64f:1 time 3948904408 //140e64c到140e64f都是数据块的地址
...

It reached the 140e64f, the 1st block contain modified data without commit. It read the Transaction Table from UNDO segment header block, found the entries that need to be applied to the data block:

SQL代码

WAIT #3: nam='db file sequential read' ela= 10503 file#=2 block#=73 blocks=1 obj#=0 tim=3948916322

Then read UNDO Block （地址为 file#=2 block#=73）and apply the entries to the data block.

SQL代码

Applying CR undo to block 5 : 140e64f itl entry 02:
xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.05
flg: ---- lkc: 1 fsc: 0x0000.00000000
Then the 2nd ITL in it, read the UNDO to apply, increase 1 Logic reads
Applying CR undo to block 5 : 140e64f itl entry 02:
xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.03
flg: ---- lkc: 1 fsc: 0x0000.00000000

Both of the undo entries（即undo记录条目） were located at the same UNDO block（地址为 uba: 0x008012aa）, thus it will justincrease 1 logical read. After all of the changes inthe uncommited ITLs have been apllied, it will generate another logical read for the UNDOed data block（指的是CR块吗？）. Here totally 9 logical reads: 2 segment header reads, 4 data block reads, 1 UNDO Segment Header, 1 UNDO block read, 1 UNDOed data block.

注释：9 logical reads，即9次逻辑读，是由表tt的一条数据行产生的吗？怎么要有四次数据块读？难道一条数据行对应要产生一个UNDOed data block吗？

Then it undo the next data block, which will cause the other 4 logical reads (1 data block, 1 UNDO Segment Header, 1 UNDO block, 1 UNDOed data block),

注释：4 logical reads，即4次逻辑读，是由表tt的另一条数据行产生的吗？怎么要有一次数据块读？难道一条数据行对应要产生一个UNDOed data block吗？

SQL代码

pin kdswh01: kdstgr dba 140e650:1 time 3948928294
Applying CR undo to block 5 : 140e650 itl entry 02:
xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.06
flg: ---- lkc: 1 fsc: 0x0000.00000000
CRS upd rd env: (scn: 0x0000.ebadfff9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 0sch: scn: 0x0000.00000000) undo env: (scn: 0x0000.ebadfffb xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.06
statement num=0 parent xid: xid: 0x0005.000.00000000 scn: 0x0000.00000001 1sch: scn: 0xa098.1f7cd9b0)
CRS upd (before): 1880FA90 scn: 0x0000.ebadfff9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0x0000.ebadfffb sfl: 0
CRS upd (after) : 1880FA90 scn: 0x0000.ebadfff9 xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.06 scn: 0x0000.ebadfffb sfl: 0
Applying CR undo to block 5 : 140e650 itl entry 02:
xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.04
flg: ---- lkc: 1 fsc: 0x0000.00000000
CRS upd rd env: (scn: 0x0000.ebadfff9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 0sch: scn: 0x0000.00000000) undo env: (scn: 0x0000.ebadfffb xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.04
statement num=0 parent xid: xid: 0x0005.000.00000000 scn: 0x0000.00000001 1sch: scn: 0xa098.1f7cd9b0)
CRS upd (before): 1880FA90 scn: 0x0000.ebadfff9 xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.06 scn: 0x0000.ebadfffb sfl: 0
CRS upd (after) : 1880FA90 scn: 0x0000.ebadfff9 xid: 0x0005.00b.00023460 uba: 0x008012aa.7970.04 scn: 0x0000.ebadfffb sfl: 0
WAIT #3: nam='SQL*Net message to client' ela= 42 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=3948929421
FETCH #3:c=0,e=1237,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=4,tim=3948929506

We should also noted that there 2 physical reads/waits for the undo segment header & undo blocks.

注释：

1、我们可以看到，在当前模式读的过程中，读取的undo块（还有undo段头块）是为了本操作时保留数据行被本操作修改前的数据用的。这些读取的undo块（还有undo段头块）是对应于本操作所在的事务的undo块。

而在一致性读的过程中，读取的undo块（还有undo段头块）是为了查看本操作开始执行时表的内容而去通过undo机制的一致性读作用来构造CR块用的。这些读取的undo块（还有undo段头块）不是对应于本操作所在的事务的undo块，而对应于其他事务的undo块。

（？）2、服务器进程根据数据块的 oracle表示的地址（即文件号+块号）在内存上找到对应的数据块的过程，应该叫做 一次逻辑读。当服务器进程找到该数据块后，就可以知道该数据块在内存上的（内存）地址。这样以后就可以直接通过这个内存地址找到该数据块，而不用通过oracle表示的地址来间接找到内存上的该数据块。

--- Fuyuncat TBC ---