Oracle SQL Tuning_CBO算法01_Cost成本计算公式

This document is an incomplete survey of formulas to compute cost. It applies to 10.2.0.4 and 11.1.0.6.
本文档描述用于计算成本的公式，具有不完整性。 它适用于10.2.0.4和11.1.0.6。

Abreviations used: 

FTS:  Full Table Scan.      全表扫描
IRS:   Index Range Scan.     索引范围扫描
#MRds: Number of multi-block reads.       多块读取的数量
mreadtime: Average time for a multi-block read request (sequential read), in milliseconds, according to system statistics sys.aux_stats$.
                 多块读取（顺序读取）的平均时间，以毫秒为单位，根据系统统计信息sys.aux_stats$。
sreadtim: Average time for a single-block read request (random read), in milliseconds, according to system statistics sys.aux_stats$.
              单块读取（随机读取）的平均时间，以毫秒为单位，根据系统统计信息sys.aux_stats$。
#Blks: Number of blocks in table according to CBO stats in dba_tables.blocks.
          表中的块数，根据dba_tables.blocks中的CBO统计信息。
MBRC: Average multiblock read count for sequential reads, in blocks, according to system statistics sys.aux_stats$.
            连续读取的平均多块读取计数（以块为单位），根据系统统计信息sys.aux_stats$。
#CPUCycles: Number of CPU cycles consumed by an operation as reported in plan_table under cpu_cost column.
                     操作消耗的CPU周期数，在plan_table表的cpu_cost列下获取。
cpuspeed: Average CPU speed, in millions of instructions/second, as per workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options)
                工作负载的平均CPU速度，以百万计的指令/秒为单位
LVLS: B*Tree level. Depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same. (dba_indexes.blevel).
          B*Tree索引的级别。 索引从其根块到其叶块的深度。 深度为0表示根块和叶块相同。(dba_indexes.blevel)。
#LB: Number of leaf blocks in the index. (dba_indexes.leaf_blocks).
        索引中的叶块数。 （dba_indexes.leaf_blocks）。
CLUF: Indicates the amount of order of the rows in the table based on the values of the index. (dba_indexes.clustering_factor).
           聚簇因子，基于索引的值指示表中行的数量。 （dba_indexes.clustering_factor）。
ix_sel: Index selectivity. Fraction of table rows accessed by index’s leading columns in order to satisfy predicates. Value is shown on 10053.
           索引选择性。 索引的前几列访问的表行的分数以满足谓词。 值显示在10053上。
ix_sel_with_filters: Index selectivity with filters. Fraction of table rows accessed by index’s columns in order to satisfy predicates. Value is shown on 10053.
                            使用过滤器的索引选择性。 索引的列访问的表行的分数以满足谓词。 值显示在10053上。

1. Cost

• CPU Costing is enabled

Cost = (#SRds * sreadtim +
        #MRds * mreadtim +
        #CPUCycles / cpuspeed ) / sreadtim
        
sreadtim = ioseektim + db_block_size / iotfrspeed
mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size / iotfrspeed
#MRds = #Blks / MBRC

解析

Cost = (单块读IO次数*单块读平均时间 +
        多块读IO次数*多块读平均时间 +
        CPU周期 / CPU速度) / 单块读平均时间
        
sreadtim = IO磁盘寻道时间 + 数据块大小 / IO传输速度
mreadtim = IO磁盘寻道时间 + 多块读一次数量 * 数据块大小 / IO传输速度
#MRds表示多块读次数 = 表的块数 / 多块读一次数量(由参数db_file_multiblock_read_count控制)

2. Full Table Scan - FTS:

• Workload System Statistics are gathered
• CPU Costing is enabled

FTS Cost = I/O Cost + CPU Cost
I/O Cost = 1 + CEIL(#MRds * (mreadtim / sreadtim))
#MRds = #Blks / MBRC
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)

解析

FTS Cost = I/O成本 + CPU成本
I/O Cost = 1 + CEIL(多块读IO次数 * (多块读平均时间 / 单块读平均时间))
#MRds = 总的块数 / 多块读一次数量
CPU Cost = ROUND(CPU周期 / CPU速度 / 1000 / 单块读平均时间)
CEIL(n)取大于等于数值n的最小整数

3. B*Tree Index Range Scan - IRS:

• Workload System Statistics are gathered
• CPU Costing is enabled

IRS Cost = I/O Cost + CPU Cost
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)
Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)

解析

IRS Cost = I/O成本 + CPU成本
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = 索引高度 + CEIL(叶子块 * 索引选择率)
Table Access I/O Cost = CEIL(聚簇因子 * 表选择率)
CPU Cost = ROUND(CPU周期 / 速度 / 1000 / 单块读的平均时间)

4. Nested Loop

• Workload System Statistics are gathered
• CPU Costing is enabled

NL Cost = Outer Table Cost + (Outer Table Card * Inner Table Cost)
NL Cost = 外部表(驱动表)的成本 + (外部表的计算基数 * 内部表的成本)
Outer Table Cost = I/O Cost + CPU Cost
Inner Table Cost = I/O Cost + CPU Cost

5. Hash Join and Sort Merge Join

• Workload System Statistics are gathered
• CPU Costing is enabled

HJ/SMJ Cost = Outer Table Cost + Inner Table Cost + Join Cost