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

 

 

OSS Description

Provide a description of the component including how it will be built and what it will do, with a reference to the functional requirements (from the Functional Specification) that are being addressed.

Optimizer system statistics contains hardware characteristics. With OSS optimizer combines IO and CPU resources needed to execute query into single unit – estimated execution time.

OSS Components:

 

Component Initialization Maintenance Description Abbreviated component’s name (get_system_stats and set_system_stats)
CPU speed At system startup Gathering system stats with gathering_mode =  ‘NOWORKLOAD’ or ‘START’, ‘STOP’ or ‘INTERVAL’ or setting manually # CPU cycles per second cpuspeed
IO seek time At system startup Gathering system stats with gathering_mode =  ‘NOWORKLOAD’ or setting manually Seek time + latency time + OS overhead time ioseektim
IO transfer speed At system startup Gathering system stats with gathering_mode =  ‘NOWORKLOAD’ or setting manually Rate at which oracle can read data in the single read request iotfrspeed
Max IO throughput None Gathering system stats with gathering_mode =  ‘NOWORKLOAD’ or ‘START’, ‘STOP’ or ‘INTERVAL’ or setting manually This number characterizes maximum throughput (MB / sec) IO subsystem can deliver maxthr
Average slave IO throughput None Gathering system stats with gathering_mode =  ‘START’, ‘STOP’ or ‘INTERVAL’ or setting manually Average parallel slave IO throughput slavethr
Average Single Block Read Time None Gathering system stats with gathering_mode =  ‘START’, ‘STOP’ or ‘INTERVAL’ or setting manually sreadtim
Average Multi Block Read Time None Gathering system stats with gathering_mode =  ‘START’, ‘STOP’ or ‘INTERVAL’ or setting manually mreadtim
Average multi block read count None Gathering system stats with gathering_mode =  ‘START’, ‘STOP’ or ‘INTERVAL’ or setting manually mbrc

 

  1. cpuspeed, ioseektim, iotfrspeed are always collected
  2. maxthr, slavethr, sreadtim, mreadtim and mbrc collected only when user gathers workload statistics.

These two sets contain equivalent information. The difference is that A) does not relate to workload and B) does. At any moment of time only one set of OSS can be used.

 

 

OSS data located in data dictionary in the aux_stats$ table and in the SGA variable kkossga. aux_stats$ keeps persistent copy of the OSS. kkossga keeps working copy. Data in kkossga and  aux_stats$ is always synchronized. User can modify, delete and gather OSS through interface provided in the DBMS_STATS package.

 

OSS used to represent cost as query estimated running time (it’s implemented as #CPU cycles and # multi block reads conversion to # single block reads) and to adjust FTS cost for parallel reads.

SRead_Cost(#Cycles) = (#Cycles * (1 / CPUSpeed)) / sreadtim

SRead_Cost(#MReads) = (#MReads * mreadtim) / sreadtim

When no workload stats available optimizer uses NOWORKLOAD stats to compute sreadtim and mreadtim:

sreadtim = ioseektim + block_size / iotfrspeed

mreadtim = ioseektim + mbrc * block_size / iotfrspeed

Optimizer converts multi block reads to single block reads (even if cost formula looks elegant the actual processing has to support old days behavior and it causes that internally everything converted to single block reads)

aux_stats$

table aux_stats$ (

sname varchar2(30) not null,       /* Prefix */

pname varchar2(30) not null,       /* Name of parameter */

pval1 number,                      /* NUMBER parameter value */

pval2 varchar2(255)                /* VARCHAR2 parameter value */

)

This table stores OSS. It also used to store the intermediate values when analyzing workload.

sname column used to store global prefixes of the stats SYSSTATS_MAIN, SYSSTATS_TEMP and SYSSTATS_INFO.

if sname = SYSSTATS_MAIN then pname and pval1 columns store name-value pairs for data representing current stats:

cpuspeed (# cpu cycles per second) in millions;

ioseektim (Seek time + latency time + OS overhead time) in milliseconds;

iotfrspeed (IO transfer speed) in bytes/ second;

maxthr (maximum I/O system throughput) in bytes/sec;

slavethr (average slave throughput);

sreadtim (wait time to read single block) in milliseconds;

mreadtim (wait time to read a multiblock) in milliseconds;

mbrc (multiblock read count) in blocks;

if sname = SYSSTATS_TEMP then pname and pval1 columns store name-value pairs for intermediate data, generated than user issues DBMS_STAT.GATHER_SYSTEM_STATS procedure and removed then gathering completes.

if sname = SYSSTATS_INFO then pname, pval2 columns store name-values for current and intermediate stats:

DSTART – then gathering was started, format “MM-DD-YYYY HH:MI”

DSTOP – then gathering was (will be, had to be) finished format “MM-DD-YYYY HH:MI”

STATUS – ‘COMPLETED’, ‘AUTOGATHERING’, ‘MANUALGATHERING’, ‘INVALID’

  1. LegendThe following abbreviations are used by optimizer trace.CBQT – cost-based query transformationJPPD – join predicate push-downFPD – filter push-downPM – predicate move-aroundCVM – complex view mergingSPJ – select-project-joinSJC – set join conversionSU – subquery unnestingOBYE – order by eliminationST – star transformationqb – query blockLB – leaf blocksDK – distinct keysLB/K – average number of leaf blocks per keyDB/K – average number of data blocks per keyCLUF – clustering factorNDV – number of distinct valuesResp – response costCard – cardinalityResc – resource costNL – nested loops (join)SM – sort merge (join)HA – hash (join)CPUCSPEED – CPU SpeedIOTFRSPEED – I/O transfer speedIOSEEKTIM – I/O seek timeSREADTIM – average single block read timeMREADTIM – average multiblock read timeMBRC – average multiblock read countMAXTHR – maximum I/O system throughputSLAVETHR – average slave I/O throughputdmeth – distribution method 1: no partitioning required 2: value partitioned 4: right is random (round-robin) 512: left is random (round-robin) 8: broadcast right and partition left 16: broadcast left and partition right 32: partition left using partitioning of right 64: partition right using partitioning of left 128: use hash partitioning dimension 256: use range partitioning dimension 2048: use list partitioning dimension 1024: run the join in serial 0: invalid distribution methodsel – selectivityptn – partition