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: Current Mode

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-11-09 01:02:38

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

    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代码
  1. HELLODBA.COM>conn demo/demo   
  2. Connected.   
  3. HELLODBA.COM>alter system flush buffer_cache;   
  4.   
  5. System altered.   
  6.   
  7. HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';   
  8.   
  9. Session altered.   
  10.   
  11. HELLODBA.COM>set autot trace stat   
  12. HELLODBA.COM>update tt set x=1;   
  13.   
  14. rows updated.   
  15.   
  16. Statistics  
  17. ----------------------------------------------------------   
  18.           0  recursive calls   
  19.           4  db block gets   
  20.           7  consistent gets   
  21.           8  physical reads   
  22.         824  redo size  
  23.         665  bytes sent via SQL*Net to client   
  24.         553  bytes received via SQL*Net from client   
  25.           4  SQL*Net roundtrips to/from client   
  26.           1  sorts (memory)   
  27.           0  sorts (disk)   
  28.           2  rows processed   
  29.   
  30. HELLODBA.COM>alter system flush buffer_cache;   
  31.   
  32. System altered.   
  33.   
  34. HELLODBA.COM>update tt set x=1;   
  35.   
  36. rows updated.   
  37.   
  38. Statistics  
  39. ----------------------------------------------------------   
  40.           0  recursive calls   
  41.           3  db block gets   
  42.           7  consistent gets   
  43.           7  physical reads   
  44.         536  redo size  
  45.         668  bytes sent via SQL*Net to client   
  46.         553  bytes received via SQL*Net from client   
  47.           4  SQL*Net roundtrips to/from client   
  48.           1  sorts (memory)   
  49.           0  sorts (disk)   
  50.           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代码
  1. pin kdswh01: kdstgr dba 140e64f:1 time 3828486551   
  2. pin kduwh01: kdusru dba 140e64f:1 time 3828486616   
  3. WAIT #1: nam='db file sequential read' ela= 7907 file#=2 block#=9 blocks=1 obj#=0 tim=3828495546   
  4. pin ktuwh01: ktugus dba 800009:17 time 3828495628   
  5. WAIT #1: nam='db file sequential read' ela= 3984 file#=2 block#=7227 blocks=1 obj#=0 tim=3828499685   
  6. pin kcbwh2: kcbchg1 dba 801c3b:18 time 3828499816   
  7. pin release      4305 ktuwh01: ktugus dba 800009:17   
  8. pin release       176 kcbwh2: kcbchg1 dba 801c3b:18   
  9. pin release     13432 kduwh01: kdusru dba 140e64f:1   
  10. pin kdswh01: kdstgr dba 140e650:1 time 3828500148   
  11. pin kduwh01: kdusru dba 140e650:1 time 3828500249   
  12. pin kcbwh5: kcbchg1 dba 801c3b:18 time 3828500352   
  13. pin release        63 kcbwh5: kcbchg1 dba 801c3b:18   
  14. 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代码
  1. pin kduwh01: kdusru dba 140e64f:1 time 3832560411   
  2. WAIT #2: nam='db file sequential read' ela= 201 file#=2 block#=7227 blocks=1 obj#=0 tim=3832560683   
  3. pin kcbwh2: kcbchg1 dba 801c3b:18 time 3832560736   
  4. pin release        69 kcbwh2: kcbchg1 dba 801c3b:18   
  5. pin release       477 kduwh01: kdusru dba 140e64f:1   
  6. pin kdswh01: kdstgr dba 140e650:1 time 3832561310   
  7. pin kduwh01: kdusru dba 140e650:1 time 3832561392   
  8. pin kcbwh5: kcbchg1 dba 801c3b:18 time 3832561465   
  9. pin release        74 kcbwh5: kcbchg1 dba 801c3b:18   
  10. pin release       199 kduwh01: kdusru dba 140e650:1   
  11. 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代码
  1. HELLODBA.COM>conn demo/demo   
  2. Connected.   
  3. HELLODBA.COM>alter system flush buffer_cache;   
  4.   
  5. System altered.   
  6.   
  7. HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';   
  8.   
  9. Session altered.   
  10.   
  11. HELLODBA.COM>set autot trace stat   
  12. HELLODBA.COM>update tt set x=1;   
  13.   
  14. rows updated.   
  15.   
  16. Statistics  
  17. ----------------------------------------------------------   
  18.           0  recursive calls   
  19.           4  db block gets   
  20.           7  consistent gets   
  21.           8  physical reads   
  22.         904  redo size  
  23.         665  bytes sent via SQL*Net to client   
  24.         553  bytes received via SQL*Net from client   
  25.           4  SQL*Net roundtrips to/from client   
  26.           1  sorts (memory)   
  27.           0  sorts (disk)   
  28.           2  rows processed   
  29.   
  30. HELLODBA.COM>rollback;   
  31.   
  32. Rollback complete.   
  33.   
  34. HELLODBA.COM>update tt set x=1;   
  35.   
  36. rows updated.   
  37.   
  38. Statistics  
  39. ----------------------------------------------------------   
  40.           0  recursive calls   
  41.           4  db block gets   
  42.           7  consistent gets   
  43.           1  physical reads   
  44.         788  redo size  
  45.         668  bytes sent via SQL*Net to client   
  46.         553  bytes received via SQL*Net from client   
  47.           4  SQL*Net roundtrips to/from client   
  48.           1  sorts (memory)   
  49.           0  sorts (disk)   
  50.           2  rows processed   
  51.   
  52. HELLODBA.COM>update tt set x=1;   
  53.   
  54. rows updated.   
  55.   
  56. Statistics  
  57. ----------------------------------------------------------   
  58.           0  recursive calls   
  59.           2  db block gets   
  60.           7  consistent gets   
  61.           0  physical reads   
  62.         536  redo size  
  63.         668  bytes sent via SQL*Net to client   
  64.         553  bytes received via SQL*Net from client   
  65.           4  SQL*Net roundtrips to/from client   
  66.           1  sorts (memory)   
  67.           0  sorts (disk)   
  68.           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代码
  1. ...   
  2. pin kduwh01: kdusru dba 140e64f:1 time 680961643   
  3. WAIT #2: nam='db file sequential read' ela= 6579 file#=2 block#=73 blocks=1 obj#=0 tim=4975935594   
  4. pin ktuwh01: ktugus dba 800049:25 time 680968375   
  5. pin ktuwh03: ktugnb dba 8012cb:26 time 680968434   
  6. pin release       141 ktuwh01: ktugus dba 800049:25   
  7. pin release       123 ktuwh03: ktugnb dba 8012cb:26   
  8. pin release      6954 kduwh01: kdusru dba 140e64f:1   
  9. pin kdswh01: kdstgr dba 140e650:1 time 680968657   
  10. pin kduwh01: kdusru dba 140e650:1 time 680968719   
  11. pin kcbwh5: kcbchg1 dba 8012cb:26 time 680968776   
  12. pin release        49 kcbwh5: kcbchg1 dba 8012cb:26   
  13. pin release       146 kduwh01: kdusru dba 140e650:1   
  14. EXEC #2:c=0,e=7657,p=1,cr=7,cu=4,mis=0,r=2,dep=0,og=4,tim=4975936219   
  15. ...  

    While in the 2nd UPDATE of the 2nd transaction, the UNDO block is be reused, so it just has 2 db block gets.

SQL代码
  1. ...   
  2. pin kduwh01: kdusru dba 140e64f:1 time 680977322   
  3. pin kcbwh5: kcbchg1 dba 8012cb:26 time 680977384   
  4. pin release        63 kcbwh5: kcbchg1 dba 8012cb:26   
  5. pin release       166 kduwh01: kdusru dba 140e64f:1   
  6. pin kdswh01: kdstgr dba 140e650:1 time 680977538   
  7. pin kduwh01: kdusru dba 140e650:1 time 680977595   
  8. pin kcbwh5: kcbchg1 dba 8012cb:26 time 680977642   
  9. pin release        48 kcbwh5: kcbchg1 dba 8012cb:26   
  10. pin release       136 kduwh01: kdusru dba 140e650:1   
  11. EXEC #2:c=0,e=829,p=0,cr=7,cu=2,mis=0,r=2,dep=0,og=4,tim=4975945080   
  12. ...  

    --- Fuyuncat TBC ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat