計算表掃描中執行計劃的COST

        以下計算執行計劃中表掃描的成本實驗是在AIX平臺下10.2.0.1進行的：

1、建立測試表

SQL>execute dbms_random.seed(0);

SQL>create table t1
    pctfree 99
    pctused 1
    as
    with generator as (
      select --+ materialize
        rownum id
      from all_objects
      where rownum <=3000
     )
    select /*+ ordered use_nl(v2) */
      rownum id,
      trunc(100*dbms_random.normal) val,
      rpad('x', 100) padding
    from
       generator v1,
       generator v2
    where rownum <=10000;

2、進行統計分析
SQL>begin
       dbms_stats.gather_table_stats(
          user,
          't1',
          cascade =>true,
          estimate_percent => null,
          method_opt => 'for all columns size 1'
          );
      end;
      /

3、檢視實際的IO和CPU成本

SQL>alter system flush shared_pool;
SQL>alter session set events '10053 trace name context forever, level 2';
SQL>select max(val) from t1;
SQL>alter session set events '10053 trace name context off';

提取跟蹤檔案相應數值：
跟蹤檔案：
    cost_io=2221
    cost_cpu=73954130
    #Blks = 10146
    cost=2228.75

4、10g下noworkload統計資訊以及相關引數查詢

SQL>select pname, pval1 from sys.aux_stats$
     where sname='SYSSTATS_MAIN'；
 
  PNAME                               PVAL1
  ------------------------------ ----------
   CPUSPEEDNW                        795.134
   IOSEEKTIM                              10
   IOTFRSPEED                           4096 

SQL>show parameter DB_FILE_MULTIBLOCK_READ_COUNT;
 DB_FILE_MULTIBLOCK_READ_COUNT=16
該數值匹配到計算公式中mbrc

5、ORACLE10g的成本計算公式

cost = (#SRDs * SREADTIM +
          #MRDs * MREADTIM +
          #CPUCycles/cpuspeed)/SREADTIM
       = #SRDs +                            --因為是表掃描，所以該值為0
         #MRDs * MREADTIM/SREADTIM +        -- IO cost
         #CPUCycles/(cpuspeed * SREADTIM)   -- CPU cost

6、手工計算IO成本，看是否與步驟2中IO成本實際值匹配

手工計算：
      cost_io= #MRDs * MREADTIM/SREADTIM，所得數值取最大整數，另外隱含引數_table_scan_cost_plus_one=true，所以cost_io需要新增1。所以10g實際公式為：
    cost_io=ceiling(#MRDs * MREADTIM/SREADTIM) +1
 
其中：
    #MRDs=#Blks/mbrc=10146/16
    MREADTIM=IOSEEKTIM + mbrc*db_block_size/IOTFRSPEED
                       = 10+ 16*8k/4k=42
   SREADTIM=IOSEEKTIM + db_block_size/IOTFRSPEED
                       =10+8k/4k=12

   cost_io=ceiling(10146/16*42/12)+1=ceiling(2219.4375)+1=2220+1=2221

計算值與步驟2中實際IO成本cost_io=2221一致。

7、手工計算CPU成本

     cpu成本=#CPUCycles/(cpuspeed * SREADTIM)

    其中
         #CPUCycles等於10053跟蹤檔案中cost_cpu=73954130，
         cpuspeed=795.134MHz=795134Hz
         SREADTIM=12
    所以
       cpu成本=73954130/(795134*12)=7.75

8、總的成本計算值
     cost=cost_io+ cpu成本=2221+7.75=2228.75

以上的計算值與實際所獲得的成本值完全一致。

備註：來源於Jonathan Lewis的《CBO基礎》第二章節tablescan。
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-613476/，如需轉載，請註明出處，否則將追究法律責任。