HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Secret of oracle logic IO: Consistent Gets

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-11-07 14:57:13

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

    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代码
  1. -- Session 1: Update without commit   
  2. HELLODBA.COM>update tt set x=2;   
  3.   
  4. rows updated.   
  5.   
  6. HELLODBA.COM>update tt set x=3;   
  7.   
  8. rows updated.   
  9.   
  10. -- Session 2:   
  11. HELLODBA.COM>conn demo/demo   
  12. Connected.   
  13. HELLODBA.COM>alter system flush buffer_cache;   
  14.   
  15. System altered.   
  16.   
  17. HELLODBA.COM>ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';   
  18.   
  19. Session altered.   
  20.   
  21. HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';   
  22.   
  23. Session altered.   
  24.   
  25. HELLODBA.COM>set autot trace stat   
  26. HELLODBA.COM>select * from tt;   
  27.   
  28. Statistics  
  29. ----------------------------------------------------------   
  30.           1  recursive calls   
  31.           0  db block gets   
  32.          13  consistent gets   
  33.           8  physical reads   
  34.         172  redo size  
  35.         440  bytes sent via SQL*Net to client   
  36.         385  bytes received via SQL*Net from client   
  37.           2  SQL*Net roundtrips to/from client   
  38.           0  sorts (memory)   
  39.           0  sorts (disk)   
  40.           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代码
  1. ...   
  2. WAIT #3: nam='db file sequential read' ela= 22808 file#=5 block#=58955 blocks=1 obj#=200943 tim=3948903234   
  3. pin ktewh25: kteinicnt dba 140e64b:4 time 3948903366   
  4. pin ktewh26: kteinpscan dba 140e64b:4 time 3948903443   
  5. WAIT #3: nam='db file scattered read' ela= 572 file#=5 block#=58956 blocks=5 obj#=200943 tim=3948904149   
  6. pin kdswh01: kdstgr dba 140e64c:1 time 3948904251   
  7. pin kdswh01: kdstgr dba 140e64d:1 time 3948904308   
  8. pin kdswh01: kdstgr dba 140e64e:1 time 3948904354   
  9. pin kdswh01: kdstgr dba 140e64f:1 time 3948904408   
  10. ...   

     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代码
  1. WAIT #3: nam='db file sequential read' ela= 10503 file#=2 block#=73 blocks=1 obj#=0 tim=3948916322   
  2.   

     Then read UNDO Block and apply the entries to the data block.
 

SQL代码
  1. Applying CR undo to block 5 : 140e64f itl entry 02:   
  2.           xid:  0x0005.00b.00023460 uba: 0x008012aa.7970.05   
  3.           flg: ----    lkc:  1     fsc: 0x0000.00000000   
  4.   
  5. Then the 2nd ITL in it, read the UNDO to apply, increase 1 Logic reads   
  6.   
  7. Applying CR undo to block 5 : 140e64f itl entry 02:   
  8.           xid:  0x0005.00b.00023460 uba: 0x008012aa.7970.03   
  9.           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代码
  1. pin kdswh01: kdstgr dba 140e650:1 time 3948928294   
  2. Applying CR undo to block 5 : 140e650 itl entry 02:   
  3.           xid:  0x0005.00b.00023460 uba: 0x008012aa.7970.06   
  4.           flg: ----    lkc:  1     fsc: 0x0000.00000000   
  5. 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   
  6.   statement num=0  parent xid: xid: 0x0005.000.00000000  scn: 0x0000.00000001 1sch: scn: 0xa098.1f7cd9b0)   
  7. CRS upd (before): 1880FA90  scn: 0x0000.ebadfff9  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0x0000.ebadfffb  sfl: 0   
  8. CRS upd (after) : 1880FA90  scn: 0x0000.ebadfff9  xid: 0x0005.00b.00023460  uba: 0x008012aa.7970.06  scn: 0x0000.ebadfffb  sfl: 0   
  9. Applying CR undo to block 5 : 140e650 itl entry 02:   
  10.           xid:  0x0005.00b.00023460 uba: 0x008012aa.7970.04   
  11.           flg: ----    lkc:  1     fsc: 0x0000.00000000   
  12. 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   
  13.   statement num=0  parent xid: xid: 0x0005.000.00000000  scn: 0x0000.00000001 1sch: scn: 0xa098.1f7cd9b0)   
  14. CRS upd (before): 1880FA90  scn: 0x0000.ebadfff9  xid: 0x0005.00b.00023460  uba: 0x008012aa.7970.06  scn: 0x0000.ebadfffb  sfl: 0   
  15. CRS upd (after) : 1880FA90  scn: 0x0000.ebadfff9  xid: 0x0005.00b.00023460  uba: 0x008012aa.7970.04  scn: 0x0000.ebadfffb  sfl: 0   
  16. WAIT #3: nam='SQL*Net message to client' ela= 42 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=3948929421   
  17. 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 ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat