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 messageto client' ela= 42 driver id=1111838976 #bytes=1 p3=0 obj#=0tim=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 physicalreads/waits for the undo segment header & undo blocks.
Secret of oracle logic IO: Current Mode
=======================================
When need update data, oracle will read thedata block in current mode. Let's see the IO in an UPDATE statement, here willbe more interesting things be found.
SQL代码
SQL>conn demo/demo
Connected.
SQL>alter system flushbuffer_cache;
System altered.
SQL>ALTER SESSION SETEVENTS '10046 trace name context forever, level 8';
Session altered.
SQL>set autot tracestat
SQL>update tt setx=1;
2 rows updated.
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
7 consistent gets
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
SQL>alter system flushbuffer_cache;
System altered.
SQL>update tt setx=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 blockgets, means the reads in current mode. Look into the trace, besides theoperations we find in previous traces, we can find these entries.
SQL代码
pin kdswh01: kdstgr dba140e64f:1 time 3828486551
pin kduwh01: kdusru dba 140e64f:1time 3828486616
WAIT #1: nam='db filesequential read' ela= 7907 file#=2 block#=9 blocks=1 obj#=0 tim=3828495546
pin ktuwh01: ktugus dba800009:17 time 3828495628
WAIT #1: nam='db filesequential read' ela= 3984 file#=2 block#=7227 blocks=1 obj#=0tim=3828499685
pin kcbwh2: kcbchg1 dba801c3b: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 140e