pin kdswh01: kdstgr dba140e650:1 time 3828500148
pin kduwh01: kdusru dba140e650:1 time 3828500249
pin kcbwh5: kcbchg1 dba801c3b: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 thecurrent mode reading. In this UPDATE, there 4 db block gets, 2 data block(140e64f, 140e650), 1 undo header (800009), and 1 undo block (801c3b). Plsnoted the undo block 801c3b be read twice for the 2 records, and just 1 currentmode in one transaction.
In the second UPDATE, there is bit ofdifference.
@ There is no UNDO header read --- just 1 UNDO header read for eachtransaction;
@ Since the buffer cache be flushed, even though the undo block is same asthe one in the 1st UPDATE, it still be read in current mode.
Therefore, there are 3 db block gets in the2nd update.
SQL代码
pinkduwh01: kdusru dba 140e64f:1 time 3832560411
WAIT #2: nam='db filesequential read' ela= 201 file#=2 block#=7227 blocks=1 obj#=0 tim=3832560683
pin kcbwh2: kcbchg1 dba801c3b:18 time 3832560736
pin release 69 kcbwh2: kcbchg1 dba 801c3b:18
pin release 477 kduwh01: kdusru dba 140e64f:1
pin kdswh01: kdstgr dba140e650:1 time 3832561310
pin kduwh01: kdusru dba140e650:1 time 3832561392
pin kcbwh5: kcbchg1 dba801c3b: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 currentmode read was released immediately.
Here we study another case, 2 transactionswith 3 UPDATE statement, no buffer be flushed during the transactions.
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
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
SQL>rollback;
Rollback complete.
SQL>update tt setx=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
SQL>update tt setx=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 previouscase. As the buffer not be flushed, there 1 new UNDO block be physical read inthe 1st UPDATE of the 2nd transaction, with 4 db block gets.
SQL代码
...
pin kduwh01: kdusru dba140e64f:1 time 6809