编者按：
本文作者系Walt，关注SQL开发，Oracle、MySQL、PostgreSQL、TiDB等数据库，AWS、Azure、OCI等公有云计算架构和技术。
个人主页: https://blog.csdn.net/lukeUnique。
SQL专栏课程：https://www.modb.pro/course/125
【免责声明】本公众号文章仅代表个人观点，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
单表访问路径的评估
Access path analysis for EMP1***************************************SINGLE TABLE ACCESS PATHSingle Table Cardinality Estimation for EMP1[E]SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLEkkecdn: Single Table Predicate:"E"."EMPNO">90000Column (#1): EMPNO(NUMBER)AvgLen: 5 NDV: 101395 Nulls: 0 Density: 0.000010 Min: 0.000000 Max: 4.000000Estimated selectivity: 0.099995 , col: #1Table: EMP1 Alias: ECard: Original: 101948.000000 Rounded: 10194 Computed: 10194.290235 Non Adjusted: 10194.290235Scan IO Cost (Disk) = 238.000000Scan CPU Cost (Disk) = 22943498.560000Cost 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.000000Total Scan CPU Cost = 22943498.560000 (scan (Disk))+ 5097400.000000 (cpu filter eval) (= 50.000000 (per row) * 101948.000000 (#rows))= 28040898.560000Access Path: TableScanCost: 240.132964 Resp: 240.132964 Degree: 0Cost_io: 238.000000 Cost_cpu: 28040899Resp_io: 238.000000 Resp_cpu: 28040899Best:: AccessPath: TableScanCost: 240.132964 Degree: 1 Resp: 240.132964 Card: 10194.290235 Bytes: 0.000000
上面的内容是10053 Trace中对于单表访问路径的评估。
优化器成本的估算（基于CPU成本模型）
对于优化器成本的估算是一个复杂的过程，涉及到大量的的理论和内部操作。
Oracle对于成本的预估模型，也可以分为两种：
CPU成本模型 ：参考系统统计信息进行成本估算。I/O成本模型 ：仅通过I/O的次数进行成本估算。
成本模型的选择通过_optimizer_cost_model隐含参数控制。
SQL> select a.ksppinm "PARAMETER",b.ksppstvl "VALUE",a.KSPPDESC "DESCRIPTION"2 from x$ksppi a, x$ksppcv b3 where a.indx = b.indx and4 a.ksppinm like '%_optimizer_cost_model%';PARAMETER VALUE DESCRIPTION-------------------------------------------------- ---------- --------------------------------------------------_optimizer_cost_model CHOOSE optimizer cost model
_optimizer_cost_model隐含参数的值主要由如下三个值，成本模型的选择会根据其值相应调整。
- CHOOSE:默认值，这时候如果有系统统计信息的话会使用CPU成本模型 ，如果没有系统统计信息的话会使用I/O成本模型 。- IO ：使用基于I/O成本模型 。- CPU ：使用基于CPU成本模型 。
对于我们这个例子，是有系统统计信息的，并且使用的是没有负载的系统统计信息（NOWORKLOAD），所以使用的是基于CPU的成本模型计算访问表的成本。简单而言，基于CPU成本模型主要会考虑I/O和CPU两方面开销估算成本。
总COST = IO_COST+ CPU_COST
IO_COST主要包括单块读的成本和多块读的成本。
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可以通过如下方法进行估算。
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的值，一般通过如下方法计算其值。
SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE IOTFRSPEEDMREADTIM = 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 b3 where a.indx = b.indx and4 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
结合前面的系统统计信息的部分，我们可以带入如下的值：
-----------------------------SYSTEM STATISTICS INFORMATION-----------------------------Using dictionary system stats.Using NOWORKLOAD StatsCPUSPEEDNW: 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=1096IOTFRSPEED=4096IOSEEKTIM=10MBRC = 8
--初始参数的值
SQL> show parameter DB_BLOCK_SIZENAME TYPE VALUE------------------------------------ ---------------------- ------------------------------db_block_size integer 8192
因此,我们可以计算出单块读和多块读的平均时间。
单块读平均时间SREADTIM=10 + 8192/4096 =12 毫秒)多块读平均时间MREADTIM=10 +(8* 8192)/4096 =26 毫秒)
另外，通过上面的TRACE内容我们可以得到表中高水位线以下的数据块信息。
***************************************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。
SQL> select a.ksppinm "PARAMETER",b.ksppstvl "VALUE",a.KSPPDESC "DESCRIPTION"2 from x$ksppi a, x$ksppcv b3 where a.indx = b.indx and4 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。
总COST = IO_COST+ CPU_COSTIO_COST =（#SRDS * SREADTIM + #MRDS * MREADTIM） / SREADTIM= #SRDS + #MRDS * MREADTIM/ SREADTIM= 1 + #Blks / MBRC * MREADTIM/ SREADTIM= 1 + 874 / 8 *26 /12= 1 + 236.708333= 238CPU_COST = #CPUCycles / (CPUSPEED * 1000 * SREADTIM)= 28040899 / (1096*1000*12)= 2.13206349
所以最终结果的总Cost 为240.13206349左右。
与Cost: 240.132964大致相同。
后续文章更加精彩，欢迎关注本公众号。
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
手把手系列（帮助个人技术成长）：
手把手教你在Windows 10安装Oracle 19c（详细图文附踩坑指南）
【SQL】实时SQL监控功能(Real-Time SQL Monitoring)
【SQL】历史SQL监控(Historical SQL Monitoring ) 功能（12c）
SQL调优和诊断工具之SQL Trace （10046 Event）介绍
SQL调优和诊断工具之Optimizer Trace（10053 Event）介绍
SQL调优和诊断工具之DBMS_SQLDIAG介绍
