CBO Cost Formulas基于成本优化器的成本计算公式大全

CBO Cost Formulas成本计算公式大全:

成本模型:连接方式Join method

注意 连接基数(Join Cardinality)不受到连接方式(join method) 的影响, oracle中主要的三种join method HASH JOIN、Nested Loops、Sort Merge:

  • Nested Loops嵌套循环成本公式:
    • Cost(outer)+Cost(inner))*cardinality(outer)
  • Sort merge 合并排序连接成本公式:
    • Cost(outer) + Cost(inner) + Sort(outer) + Sort(inner)
  • Hash Join 哈希连接公式:
    • Cost(outer) + Cost(inner) + Build(outer) + Probe(inner)

 

Index Unique Scan Cost成本计算
INDEX UNIQUE SCAN COST = (BLEVEL (1-(OIC/100)) + 1) * (OICA/100)

 

Index Range Scan Cost成本计算
INDEX RANGE SCAN COST = (BLEVEL + FF*LFBL)*(1-(OIC/100))+ FF*CLUF)* (OICA/100)

 

 

formula does not include the CPU cost

  • BLEVEL = number of branch levels in index
  • add +1 for leaf block
  • FF = filtering factor – selectivity
  • LFBL = number of leaf blocks
  • CLUF = index clustering factor
  • OIC = optimizer_index_caching(default 0)
  • OICA = optimizer_index_cost_adj parameter(default=100)

 

CPU costing启用的情况下:

mreadtime -Average time , in milliseconds, for a multi-block read (according to sys.aux_stats$)

sreadtime – Average time , in milliseconds, for a single-block read (according to sys.aux_stats$)

MBRC – Average number of blocks to be read in a multi-block read (according to sys.aux_stats$

#SRDs – number of single block reads

#MRDs – number of multi block reads

#CPUCycles – number of CPU Cycles

sreadtime = ioseektim + db_block_size/iotfrspeed

mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size / iotrfspeed

#MRds = #Blks/MBRC

Cost 成本本身 =(#SRds * sreadtim +#MRds * mreadtim + #CPUCycles/cpuspeed)/sreadtim ,  

Cost成本的单位 为 single-block read time=sreadtim