Oracle COST成本计算知识梳理
SQL> select sname,pname,pval1 from aux_stats$ where pname in ('CPUSPEEDNW','IOSEEKTIM','IOTFRSPEED') ;
SNAME PNAME PVAL1
-------------------- -------------------- ----------
SYSSTATS_MAIN CPUSPEEDNW 3308.9701 <<<< speed in millions of operations per second 每秒的百万操作数
SYSSTATS_MAIN IOSEEKTIM 10 <<<< disk seek time in milliseconds 毫秒 千分之一秒
SYSSTATS_MAIN IOTFRSPEED 4096 <<<< disk transfer time in bytes per millisecond 每毫秒磁盘传输的字节数
下面是10053跟踪中的成本信息
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 3309 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
注意这里是无负载统计，根据单块读和多块读的计算公式计算本服务器环境下的具体值如下
MBRC >>>>>NO VALUE blocks (default is 8) 这里默认是8个块
sreadtime = IOSEEKTIM + db_block_size/iotfrspeed = 10 + 8192/4096 = 12毫秒
mreadtim = IOSEEKTIM + MBRC*db_block_size/iotfrspeed = 10 + 8*8192/4096 = 26毫秒
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 3309 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TEST Alias: TEST
#Rows: 89733 SSZ: 0 LGR: 0 #Blks: 1543 AvgRowLen: 115.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Index Stats::
Index: IDX_TEST_ID Col#: 4
LVLS: 1 #LB: 199 #DK: 89733 LB/K: 1.00 DB/K: 1.00 CLUF: 2464.00 NRW: 89733.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
KKEISFLG: 1
try to generate single-table filter predicates from ORs for query block SEL$1 (#0)
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST[TEST]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Table: TEST Alias: TEST
Card: Original: 89733.000000 Rounded: 89733 Computed: 89733.000000 Non Adjusted: 89733.000000
Scan IO Cost (Disk) = 420.000000
Scan CPU Cost (Disk) = 54957551.920000
Total Scan IO Cost = 420.000000 (scan (Disk))
= 420.000000
Total Scan CPU Cost = 54957551.920000 (scan (Disk))
= 54957551.920000
Access Path: TableScan
Cost: 421.384055 Resp: 421.384055 Degree: 0
Cost_io: 420.000000 Cost_cpu: 54957552
Resp_io: 420.000000 Resp_cpu: 54957552
Best:: AccessPath: TableScan
Cost: 421.384055 Degree: 1 Resp: 421.384055 Card: 89733.000000 Bytes: 0.000000
***************************************
这里全表扫描使用多块读
根据公式计算
Cost = (
#SRds +
#MRds * mreadtim / sreadtime +
#CPUCycles/(cpuspeed * sreadtim)
)
CPU成本如下，注意54957551.920000/(3308.9701的单位是秒，而12是毫秒
SQL> select 54957551.920000/(3308.9701*12*1000) from dual;
54957551.920000/(3308.9701*12*1000)
-----------------------------------
1.38405481
此时的CPU成本四舍五入是1.384055
IO成本，此时只有多块读
#MRds * mreadtim / sreadtime =
SQL> select 1543/8*(26/12) from dual;
1543/8*(26/12)
--------------
417.895833
我们发现此时的计算结果跟10053跟踪计算结果有差异，差2，我们计算结果大概是418，而10053跟踪是420，目前数据库参数db_file_multiblock_read_count为128
而10053跟踪结果看Oracle在无负载模式下使用默认多块读为8个块，IO成本多了2，这里计算差异需要分析。
SQL> show parameter db_file_multiblock_read_count ;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_file_multiblock_read_count integer 128
所以
Cost = (
#SRds +
#MRds * mreadtim / sreadtime +
#CPUCycles/(cpuspeed * sreadtim)
) = 0 + 418 + 1.384055 = 419.384055
后来在Jonathan Lewis的书中解释如果隐藏参数_table_scan_cost_plus_one为true则Oracle在全表扫描或者索引ffs时会默认+1 。
1* SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm ='_table_scan_cost_plus_one'
KSPPINM KSPPSTVL KSPPDESC
------------------------------ ---------- ------------------------------------------------------------
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one
如果加1此时的Cost为420.384055 与10053跟踪结果还差1 ，基本接近了。但是目前还是差1，这个目前无从知晓Oracle哪里做了调整。
