Oracle优化器成本的估算（10053 Trace中基于CPU成本模型的内容解析）

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

CPU成本模型 ：参考系统统计信息进行成本估算。
I/O成本模型 ：仅通过I/O的次数进行成本估算。

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

- CHOOSE:默认值，这时候如果有系统统计信息的话会使用CPU成本模型 ，如果没有系统统计信息的话会使用I/O成本模型 。
- IO ：使用基于I/O成本模型 。
- CPU ：使用基于CPU成本模型 。

总COST = IO_COST+ CPU_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=#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 毫秒)。

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

-----------------------------
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

SQL> show parameter DB_BLOCK_SIZE

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_block_size                        integer                8192

单块读平均时间SREADTIM=10 + 8192/4096 =12  毫秒)
多块读平均时间MREADTIM=10 +(8* 8192)/4096 =26 毫秒)

***************************************
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

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

总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