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

Adjusted MBRC formual in CBO

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2010-03-10 07:30:12

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

    In IO mode, CBO will calculate IO cost by an adjusted MBRC (ADJMBRC) instead of the MBRC (db_file_multiblock_read_count) directly.
    IO = 1+CEIL(TABBLKS/ADJMBRC)

    Jonathan Lewis ever disucssed it well, get the conclusion that ADJMBRC is decided by MBRC and block size(BLKSIZ). Take 8k block size as example, the ADJMBRC correspond to MBRC are,

SQL代码
  1. MBRC         ADJMBRC   
  2. ------------ --------------   
  3. 8            6.59   
  4. 16           10.40   
  5. 32           16.41   
  6. 64           25.90  

    Such ADJMBRC are rounded, and it's difficult to find any rules. Let's play a numbers game with them. 

    From the previous formula, get to know ADJMBRC could be calculated as below with test case's data.
    ADJMBRC ≈ TABBLKS/(IO - 1)

    With several test cases, adjusting TABBLKS, we can found the ADJMBRC reach a digital closely. For example, the ADJMBRC with MBRC=16 is 6.588755338. And here are the others.
 

SQL代码
  1. MBRC         ADJMBRC   
  2. ------------ --------------   
  3. 8            6.588755338   
  4. 16           10.39779676   
  5. 32           16.4088924   
  6. 64           25.89507721   

    Eventhoug they become more accurate, we still can't find any rules. Let's try to compute ADJMBRC/MBRC.

SQL代码
  1. MBRC         ADJMBRC        ADJMBRC/MBRC   
  2. ------------ -------------- -------------   
  3. 8            6.588755338    0.823594417   
  4. 16           10.39779676    0.649862298   
  5. 32           16.4088924     0.512777888   
  6. 64           25.89507721    0.404610581  

    mmm, still chaose. One thing should be noted, the next MBRC is multiple of the previous one. Let's try to devide the figures we just computed.
 

SQL代码
  1. MBRC         ADJMBRC        ADJMBRC/MBRC  (ADJMBRC/MBRC[n*2])/(ADJMBRC/MBRC[n])   
  2. ------------ -------------- ------------- -------------------------------------   
  3. 8            6.588755338    0.823594417   1.26733682   
  4. 16           10.39779676    0.649862298   1.26733682   
  5. 32           16.4088924     0.512777888   1.26733682   
  6. 64           25.89507721    0.404610581  

    aha, rule is at hand. With this rule, it's not hard to get the formula.
    ADJMBRC = 1.6764459346/POWER(1.26733682,LOG(MBRC,2))*MBRC

    Consider BLKSIZ also, the formula is adjusted as,
    ADJMBRC = 1.6764459346/POWER(1.26733682,LOG(8192/BLKSIZ*MBRC,2))*8192/BLKSIZ*MBRC

    For 1.6764459346, it's could be simplified as (1/0.5965). But I still where does 1.26733682 come from. Then the formula is,
    ADJMBRC = 1/0.5965/POWER(1.26733682,LOG(8192/BLKSIZ*MBRC,2))*8192/BLKSIZ*MBRC

    With this formula, it's easy to estimate the Full Table Scan IO cost in IO mode.
 

SQL代码
  1. HELLODBA.COM>select p.value mbrc,   
  2.   2         1/0.5965/POWER(1.26733682,LOG(2,8192/sp.block_size*p.value))*8192/sp.block_size*p.value adjmbrc,   
  3.   3         s.blocks,   
  4.   4         1+ceil(s.blocks/(1/0.5965/POWER(1.26733682,LOG(2,8192/sp.block_size*p.value))*8192/sp.block_size*p.value)) IOCOST   
  5.   5  from dba_segments s, dba_tablespaces sp, all_parameters p   
  6.   6  where s.owner='DEMO' and s.segment_name = 'T_TEST1'  
  7.   7  and s.tablespace_name = sp.tablespace_name   
  8.   8  and p.name = 'db_file_multiblock_read_count';   
  9.   
  10. MBRC   ADJMBRC     BLOCKS     IOCOST   
  11. ------ ----------- ---------- ----------   
  12. 16     10.3977968  680        67   
  13.   
  14. HELLODBA.COM>set autot trace exp   
  15. HELLODBA.COM>select /*+no_cpu_costing*/* from demo.t_test1;   
  16.   
  17. Execution Plan   
  18. ----------------------------------------------------------   
  19. Plan hash value: 1883417357   
  20.   
  21. -------------------------------------------------------------   
  22. | Id  | Operation         | Name    | Rows  | Bytes | Cost  |   
  23. -------------------------------------------------------------   
  24. |   0 | SELECT STATEMENT  |         | 47582 |  4600K|    67 |   
  25. |   1 |  TABLE ACCESS FULL| T_TEST1 | 47582 |  4600K|    67 |   
  26. -------------------------------------------------------------   

    --- Fuyuncat ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat