Secret of oracle logic IO: Consistent Gets
==========================================
Then, let's study the Consistent Gets case.The obvious feature of consistent gets is that it will read the undo block toapply to current data block correspond to the SCN. To monitor the undoapplication, we turn the 10201 event trace on. Here is the demo,
SQL代码
-- Session 1: Update withoutcommit
SQL> update tt setx=2;
2 rows updated.
SQL> update tt setx=3;
2 rows updated.
-- Session 2:
SQL> conn demo/demo
Connected.
SQL> alter system flushbuffer_cache;
System altered.
SQL> ALTER SESSION SETEVENTS '10201 trace name context forever, level 1';
Session altered.
SQL>ALTER SESSION SETEVENTS '10046 trace name context forever, level 8';
Session altered.
SQL>set autot tracestat
SQL>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 casewithout CR undo application. Look into the trace file, see what we catched.
First, it still need read the segmentheader twice, then read the data block in sequence,
代码
...
WAIT #3: nam='db filesequential read' ela= 22808 file#=5 block#=58955 blocks=1 obj#=200943tim=3948903234
pin ktewh25: kteinicnt dba140e64b:4 time 3948903366
pin ktewh26: kteinpscan dba140e64b:4 time 3948903443
WAIT #3: nam='db filescattered read' ela= 572 file#=5 block#=58956 blocks=5 obj#=200943tim=3948904149
pin kdswh01: kdstgr dba140e64c:1 time 3948904251
pin kdswh01: kdstgr dba 140e64d:1time 3948904308
pin kdswh01: kdstgr dba140e64e:1 time 3948904354
pin kdswh01: kdstgr dba140e64f:1 time 3948904408
...
It reached the 140e64f, the 1st blockcontain modified data without commit. It read the Transaction Table from UNDO segmentheader block, found the entries that need to be applied to the data block:
代码
WAIT #3: nam='db filesequential read' ela= 10503 file#=2 block#=73 blocks=1 obj#=0tim=3948916322
Then read UNDO Block and apply the entriesto the data block.
代码
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 toapply, 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 atthe same UNDO block, thus it will just increase 1 logical read. After all ofthe changes in the uncommited ITLs have been apllied, it will generate anotherlogical read for the UNDOed data block. Here totally 9 logical reads: 2 segmentheader reads, 4 data block reads, 1 UNDO Segment Header, 1 UNDO block read, 1UNDOed data block.
Then it undo the next data block, whichwill cause the other 4 logical reads (1 data block, 1 UNDO Segment Header, 1UNDO block, 1 UNDOed data block),
SQL代码
pin kdswh01: kdstgr dba140e650: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: