[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2009-11-09 01:02:38
When need update data, oracle will read the data block in current mode. Let's see the IO in an UPDATE statement, here will be more interesting things be found.
SQL代码
- HELLODBA.COM>conn demo/demo
- Connected.
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
- Session altered.
- HELLODBA.COM>set autot trace stat
- HELLODBA.COM>update tt set x=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
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>update tt set x=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 block gets, means the reads in current mode. Look into the trace, besides the operations we find in previous traces, we can find these entries.
SQL代码
- pin kdswh01: kdstgr dba 140e64f:1 time 3828486551
- pin kduwh01: kdusru dba 140e64f:1 time 3828486616
- WAIT #1: nam='db file sequential read' ela= 7907 file#=2 block#=9 blocks=1 obj#=0 tim=3828495546
- pin ktuwh01: ktugus dba 800009:17 time 3828495628
- WAIT #1: nam='db file sequential read' ela= 3984 file#=2 block#=7227 blocks=1 obj#=0 tim=3828499685
- pin kcbwh2: kcbchg1 dba 801c3b: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 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 3828500148
- pin kduwh01: kdusru dba 140e650:1 time 3828500249
- pin kcbwh5: kcbchg1 dba 801c3b: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 the current mode reading. In this UPDATE, there 4 db block gets, 2 data block (140e64f, 140e650), 1 undo header (800009), and 1 undo block (801c3b). Pls noted the undo block 801c3b be read twice for the 2 records, and just 1 current mode in one transaction.
In the second UPDATE, there is bit of difference.
- There is no UNDO header read --- just 1 UNDO header read for each transaction;
- Since the buffer cache be flushed, even though the undo block is same as the one in the 1st UPDATE, it still be read in current mode.
Therefore, there are 3 db block gets in the 2nd update.
SQL代码
- pin kduwh01: kdusru dba 140e64f:1 time 3832560411
- WAIT #2: nam='db file sequential read' ela= 201 file#=2 block#=7227 blocks=1 obj#=0 tim=3832560683
- pin kcbwh2: kcbchg1 dba 801c3b:18 time 3832560736
- pin release 69 kcbwh2: kcbchg1 dba 801c3b:18
- pin release 477 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 3832561310
- pin kduwh01: kdusru dba 140e650:1 time 3832561392
- pin kcbwh5: kcbchg1 dba 801c3b: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 current mode read was released immediately.
Here we study another case, 2 transactions with 3 UPDATE statement, no buffer be flushed during the transactions.
SQL代码
- HELLODBA.COM>conn demo/demo
- Connected.
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
- Session altered.
- HELLODBA.COM>set autot trace stat
- HELLODBA.COM>update tt set x=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
- HELLODBA.COM>rollback;
- Rollback complete.
- HELLODBA.COM>update tt set x=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
- HELLODBA.COM>update tt set x=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 previous case. As the buffer not be flushed, there 1 new UNDO block be physical read in the 1st UPDATE of the 2nd transaction, with 4 db block gets.
SQL代码
- ...
- pin kduwh01: kdusru dba 140e64f:1 time 680961643
- WAIT #2: nam='db file sequential read' ela= 6579 file#=2 block#=73 blocks=1 obj#=0 tim=4975935594
- pin ktuwh01: ktugus dba 800049:25 time 680968375
- pin ktuwh03: ktugnb dba 8012cb:26 time 680968434
- pin release 141 ktuwh01: ktugus dba 800049:25
- pin release 123 ktuwh03: ktugnb dba 8012cb:26
- pin release 6954 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 680968657
- pin kduwh01: kdusru dba 140e650:1 time 680968719
- pin kcbwh5: kcbchg1 dba 8012cb:26 time 680968776
- pin release 49 kcbwh5: kcbchg1 dba 8012cb:26
- pin release 146 kduwh01: kdusru dba 140e650:1
- EXEC #2:c=0,e=7657,p=1,cr=7,cu=4,mis=0,r=2,dep=0,og=4,tim=4975936219
- ...
While in the 2nd UPDATE of the 2nd transaction, the UNDO block is be reused, so it just has 2 db block gets.
SQL代码
- ...
- pin kduwh01: kdusru dba 140e64f:1 time 680977322
- pin kcbwh5: kcbchg1 dba 8012cb:26 time 680977384
- pin release 63 kcbwh5: kcbchg1 dba 8012cb:26
- pin release 166 kduwh01: kdusru dba 140e64f:1
- pin kdswh01: kdstgr dba 140e650:1 time 680977538
- pin kduwh01: kdusru dba 140e650:1 time 680977595
- pin kcbwh5: kcbchg1 dba 8012cb:26 time 680977642
- pin release 48 kcbwh5: kcbchg1 dba 8012cb:26
- pin release 136 kduwh01: kdusru dba 140e650:1
- EXEC #2:c=0,e=829,p=0,cr=7,cu=2,mis=0,r=2,dep=0,og=4,tim=4975945080
- ...
--- Fuyuncat TBC ---