编者按：
本文作者系Walt，关注SQL开发，Oracle、MySQL、PostgreSQL、TiDB等数据库，AWS、Azure、OCI等公有云计算架构和技术。
个人主页: https://blog.csdn.net/lukeUnique。
SQL专栏课程：https://www.modb.pro/course/125
【免责声明】本号文章仅代表个人观点，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
单表访问路径的评估
代码语言：javascript复制Access path analysis for EMP1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP1[E]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"E"."EMPNO">90000
Column (#1): EMPNO(NUMBER)
AvgLen: 5 NDV: 101395 Nulls: 0 Density: 0.000010 Min: 0.000000 Max: 4.000000
Estimated selectivity: 0.099995 , col: #1
Table: EMP1 Alias: E
Card: Original: 101948.000000 Rounded: 10194 Computed: 10194.290235 Non Adjusted: 10194.290235
Scan IO Cost (Disk) = 238.000000
Scan CPU Cost (Disk) = 22943498.560000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.099995 flag = 2048 ("E"."EMPNO">90000)
Total Scan IO Cost = 238.000000 (scan (Disk))
0.000000 (io filter eval) (= 0.000000 (per row) * 101948.000000 (#rows))
= 238.000000
Total Scan CPU Cost = 22943498.560000 (scan (Disk))
5097400.000000 (cpu filter eval) (= 50.000000 (per row) * 101948.000000 (#rows))
= 28040898.560000
Access Path: TableScan
Cost: 240.132964 Resp: 240.132964 Degree: 0
Cost_io: 238.000000 Cost_cpu: 28040899
Resp_io: 238.000000 Resp_cpu: 28040899
Best:: AccessPath: TableScan
Cost: 240.132964 Degree: 1 Resp: 240.132964 Card: 10194.290235 Bytes: 0.000000上面的内容是10053 Trace中对于单表访问路径的评估。
优化器成本的估算（基于CPU成本模型）
对于优化器成本的估算是一个复杂的过程，涉及到大量的的理论和内部操作。
Oracle对于成本的预估模型，也可以分为两种：
代码语言：javascript复制CPU成本模型 ：参考系统统计信息进行成本估算。
I/O成本模型 ：仅通过I/O的次数进行成本估算。成本模型的选择通过_optimizer_cost_model隐含参数控制。
代码语言：javascript复制SQL> select a.ksppinm "PARAMETER",b.ksppstvl "VALUE",a.KSPPDESC "DESCRIPTION"
2 from x$ksppi a, x$ksppcv b
3 where a.indx = b.indx and
4 a.ksppinm like '%_optimizer_cost_model%';
PARAMETER VALUE DESCRIPTION
-------------------------------------------------- ---------- --------------------------------------------------
_optimizer_cost_model CHOOSE optimizer cost model_optimizer_cost_model隐含参数的值主要由如下三个值，成本模型的选择会根据其值相应调整。
代码语言：javascript复制- CHOOSE:默认值，这时候如果有系统统计信息的话会使用CPU成本模型 ，如果没有系统统计信息的话会使用I/O成本模型 。
- IO ：使用基于I/O成本模型 。
- CPU ：使用基于CPU成本模型 。对于我们这个例子，是有系统统计信息的，并且使用的是没有负载的系统统计信息（NOWORKLOAD），所以使用的是基于CPU的成本模型计算访问表的成本。简单而言，基于CPU成本模型主要会考虑I/O和CPU两方面开销估算成本。
代码语言：javascript复制总COST = IO_COST CPU_COSTIO_COST主要包括单块读的成本和多块读的成本。
代码语言：javascript复制IO_COST = （#SRDS * SREADTIM #MRDS * MREADTIM） / SREADTIM
其中：
- #SRDS （NUMBER OF SINGLE BLOCK READS ）是单块读的次数 。
- #MRDS（NUMBER OF MULTI BLOCK READS）是多块读的次数，其值等于表中高水位线以下数据块数除以一次多块读的块数，即#Blks / MBRC。
- SREADTIM（SINGLE BLOCK READ TIME）是单块读平均时间(单位milliseconds 毫秒)。
- MREADTIM（MULTI BLOCK READ TIME ）是多块读平均时间(单位milliseconds 毫秒)。CPU_COST可以通过如下方法进行估算。
代码语言：javascript复制CPU_COST=#CPUCycles / (CPUSPEED * 1000 * SREADTIM)
其中：
- #CPUCycles 是预估的CPU开销(周期数)，可以通过PLAN_TABLE/V$SQL_PLAN的CPU_COST列或者10053 Trace中的Cost_cpu，本例中为Cost_cpu: 28040899。
- CPUSPEED（CPU cycles per second） 是CPU的频率（速度）。
- SREADTIM是单块读平均时间(单位milliseconds 毫秒)。对于没有负载的系统统计信息（NOWORKLOAD），并不会收集SREADTIM和MREADTIM的值，一般通过如下方法计算其值。
代码语言：javascript复制SREADTIM = IOSEEKTIM DB_BLOCK_SIZE / IOTFRSPEED
MREADTIM = IOSEEKTIM (DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE) / IOTFRSPEED
**如果DB_FILE_MULTIBLOCK_READ_COUNT没有显式设置的话，Oracle会使用隐含参数_db_file_optimizer_read_count的值替代DB_FILE_MULTIBLOCK_READ_COUNT，
对于本例而言值为8。
SQL> select a.ksppinm "PARAMETER",b.ksppstvl "VALUE",a.KSPPDESC "DESCRIPTION"
2 from x$ksppi a, x$ksppcv b
3 where a.indx = b.indx and
4 a.ksppinm like '%_db_file_optimizer_read_count%';
PARAMETER VALUE DESCRIPTION
-------------------------------------------------- ---------- --------------------------------------------------
_db_file_optimizer_read_count 8 multiblock read count for regular clients
另外，CPUSPEED即是CPUSPEEDNW的值。
CPUSPEED=CPUSPEEDNW结合前面的系统统计信息的部分，我们可以带入如下的值：
代码语言：javascript复制-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 1096 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)
====》》》
CPUSPEEDNW=1096
IOTFRSPEED=4096
IOSEEKTIM=10
MBRC = 8--初始参数的值
代码语言：javascript复制SQL> show parameter DB_BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_block_size integer 8192因此,我们可以计算出单块读和多块读的平均时间。
代码语言：javascript复制单块读平均时间SREADTIM=10 8192/4096 =12 毫秒)
多块读平均时间MREADTIM=10 (8* 8192)/4096 =26 毫秒)另外，通过上面的TRACE内容我们可以得到表中高水位线以下的数据块信息。
代码语言：javascript复制***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP1 Alias: E
#Rows: 101948 SSZ: 0 LGR: 0 #Blks: 874 AvgRowLen: 50.00 NEB: 0 ChainCnt: 0.00 ScanRate: 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
====》》》
数据块数#Blks = 874
多块读#MRDS = #Blks / MBRC =874 / 8对于全表扫描（TABLE FULL SCAN）而言，尽管读取表中管理块信息等情况时也可能发生单块读，但成本估算时一般全表扫描可以认为都是多块读，单块读#SRDS为0。但是为了减少优化器的估算误差，Oracle通过隐含参数_table_scan_cost_plus_one来调节全表扫描和索引快速扫描的成本。当_table_scan_cost_plus_one为TRUE时，会默认在计算成本时加1。
代码语言：javascript复制SQL> select a.ksppinm "PARAMETER",b.ksppstvl "VALUE",a.KSPPDESC "DESCRIPTION"
2 from x$ksppi a, x$ksppcv b
3 where a.indx = b.indx and
4 a.ksppinm like '%_table_scan_cost_plus_one%';
PARAMETER VALUE DESCRIPTION
------------------------- ---------- ------------------------------------------------------------
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one综上，我们可以进一步计算出全表访问DEPT1表的COST。
代码语言：javascript复制总COST = IO_COST CPU_COST
IO_COST =（#SRDS * SREADTIM #MRDS * MREADTIM） / SREADTIM
= #SRDS #MRDS * MREADTIM/ SREADTIM
= 1 #Blks / MBRC * MREADTIM/ SREADTIM
= 1 874 / 8 *26 /12
= 1 236.708333
= 238
CPU_COST = #CPUCycles / (CPUSPEED * 1000 * SREADTIM)
= 28040899 / (1096*1000*12)
= 2.13206349所以最终结果的总Cost 为240.13206349左右。
与Cost: 240.132964大致相同。
