Secret of oracle logic IO: Consistent Gets
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2009-11-07 14:57:13
Share to
Then, let's study the Consistent Gets case. The obviouse 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, 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
- pin ktewh26: kteinpscan dba 140e64b:4 time 3948903443
- 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
- pin kdswh01: kdstgr dba 140e64d:1 time 3948904308
- pin kdswh01: kdstgr dba 140e64e:1 time 3948904354
- pin kdswh01: kdstgr dba 140e64f:1 time 3948904408
- ...
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 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 were located at the same UNDO block, thus it will just increase 1 logical read. After all of the changes in the uncommited ITLs have been apllied, it will generate another logical read for the UNDOed data block. 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.
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),
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.
--- Fuyuncat TBC ---