[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2010-03-10 07:30:12
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代码
- MBRC ADJMBRC
- ------------ --------------
- 8 6.59
- 16 10.40
- 32 16.41
- 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代码
- MBRC ADJMBRC
- ------------ --------------
- 8 6.588755338
- 16 10.39779676
- 32 16.4088924
- 64 25.89507721
Eventhoug they become more accurate, we still can't find any rules. Let's try to compute ADJMBRC/MBRC.
SQL代码
- MBRC ADJMBRC ADJMBRC/MBRC
- ------------ -------------- -------------
- 8 6.588755338 0.823594417
- 16 10.39779676 0.649862298
- 32 16.4088924 0.512777888
- 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代码
- MBRC ADJMBRC ADJMBRC/MBRC (ADJMBRC/MBRC[n*2])/(ADJMBRC/MBRC[n])
- ------------ -------------- ------------- -------------------------------------
- 8 6.588755338 0.823594417 1.26733682
- 16 10.39779676 0.649862298 1.26733682
- 32 16.4088924 0.512777888 1.26733682
- 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代码
- HELLODBA.COM>select p.value mbrc,
- 2 1/0.5965/POWER(1.26733682,LOG(2,8192/sp.block_size*p.value))*8192/sp.block_size*p.value adjmbrc,
- 3 s.blocks,
- 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 from dba_segments s, dba_tablespaces sp, all_parameters p
- 6 where s.owner='DEMO' and s.segment_name = 'T_TEST1'
- 7 and s.tablespace_name = sp.tablespace_name
- 8 and p.name = 'db_file_multiblock_read_count';
- MBRC ADJMBRC BLOCKS IOCOST
- ------ ----------- ---------- ----------
- 16 10.3977968 680 67
- HELLODBA.COM>set autot trace exp
- HELLODBA.COM>select /*+no_cpu_costing*/* from demo.t_test1;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1883417357
- -------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost |
- -------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 47582 | 4600K| 67 |
- | 1 | TABLE ACCESS FULL| T_TEST1 | 47582 | 4600K| 67 |
- -------------------------------------------------------------
--- Fuyuncat ---