最基本的io成本计算法则

【建立初始环境】

ALTER SESSION SET db_file_multiblock_read_count=8;
ALTER SESSION SET optimizer_mode=ALL_ROWS;
ALTER SESSION SET statistics_level=ALL;
ALTER SESSION SET "_optimizer_cost_model"='IO';  --禁用cpu_costing

--建立实验表空间
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UNIFORM_TBS') from dual;

CREATE TABLESPACE UNIFORM_SIZE_TBS DATAFILE
'D:\APP\ADMINISTRATOR\ORADATA\ZHANGYU\UNIFORM_SIZE_TBS01.DBF' SIZE 100M
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
BLOCKSIZE 8192  --8k大小的块
EXTENT MANAGEMENT LOCAL --本地管理表空间
UNIFORM SIZE 1048576   --固定extent大小
SEGMENT SPACE MANAGEMENT MANUAL;  --非assm管理

【创建实验表】

CREATE TABLE t1 TABLESPACE UNIFORM_SIZE_TBS PCTFREE 99 PCTUSED 1
AS
SELECT LEVEL AS ID,
       TRUNC(dbms_random.normal * 100) AS val,
       RPAD('x', 100) AS padding
FROM   dual
CONNECT BY LEVEL <= 10000;

--收集统计信息
BEGIN
  dbms_stats.gather_table_stats(ownname          => USER,
                                tabname          => 't1',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',  --不生成直方图
                                cascade          => TRUE);
END;
/

SELECT t.HISTOGRAM
FROM   user_tab_col_statistics t
WHERE  t.table_name = 'T1';

/*
NONE
NONE
NONE
*/

SELECT uts.NUM_ROWS,
       uts.BLOCKS,
       uts.AVG_ROW_LEN
FROM   user_tab_statistics uts
WHERE  uts.TABLE_NAME = 'T1';
--10000 10000 109

SELECT column_name,
       num_distinct,
       utl_raw.cast_to_number(HEXTORAW(low_value)) numl,
       utl_raw.cast_to_varchar2(HEXTORAW(low_value)) charl,
       utl_raw.cast_to_number(HEXTORAW(high_value)) numh,
       utl_raw.cast_to_varchar2(HEXTORAW(high_value)) charh,
       density,
       num_nulls,
       avg_col_len
FROM   user_tab_col_statistics utcs
WHERE  utcs.table_name = 'T1';

COLUMN_NAME     NUM_DISTINCT       NUML CHARL                      NUMH CHARH                   DENSITY  NUM_NULLS AVG_COL_LEN
--------------- ------------ ---------- -------------------- ---------- -------------------- ---------- ---------- -----------
ID                     10000          1 ?                         10000 ?                         .0001          0           4
VAL                      587       -367 =b"f                        416 ?                   .001703578          0           4
PADDING                    1 -6.97E-115 x                    -6.97E-115 x                             1          0         101

【初试 - 简单表扫描】

SELECT MAX(val) FROM t1;

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  1519 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |       |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1519 |
-----------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)
  
  我们看到扫描10000个块的cost成本（纯io）是1519，对应处理的数据量是
是40000bytes （num_rows * avg_col_len）。

【修改db_file_multiblock_read_count】

ALTER session SET db_file_multiblock_read_count=16;

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   963 |
-----------------------------------------------------------

ALTER session SET db_file_multiblock_read_count=32;

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   611 |
-----------------------------------------------------------

ALTER session SET db_file_multiblock_read_count=64;

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   388 |
-----------------------------------------------------------

ALTER session SET db_file_multiblock_read_count=128;

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   246 |
-----------------------------------------------------------

ALTER session SET db_file_multiblock_read_count=256;

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   157 |
-----------------------------------------------------------

【io_cost公式】

  通过查看sys.Aux_Stats$发现，目前只存在noworkload系统统计信息，所以
对应io_cost计算公式为：

COST = io COST = blocks / ( POWER(db_file_multiblock_read_count,0.6581)*1.6765 )

SELECT round(10000 / (POWER(8, 0.6581) * 1.6765) + 1) COST FROM dual;
SELECT round(10000 / (POWER(16, 0.6581) * 1.6765) + 1) COST FROM dual;
SELECT round(10000 / (POWER(32, 0.6581) * 1.6765) + 1) COST FROM dual;
SELECT round(10000 / (POWER(64, 0.6581) * 1.6765) + 1) COST FROM dual;
SELECT round(10000 / (POWER(128, 0.6581) * 1.6765) + 1) COST FROM dual;
SELECT round(10000 / (POWER(256, 0.6581) * 1.6765) + 1) COST FROM dual;

  我们发现上面各sql计算出来的cost与实际中差距并不大，而+1操作是因为下面这个
隐含参数设置为true的原因：

SELECT n.ksppinm  AS NAME,
       v.ksppstvl AS val
FROM   x$ksppi  n,
       x$ksppcv v
WHERE  n.indx = v.indx
AND    n.ksppinm = '_table_scan_cost_plus_one';

--_table_scan_cost_plus_one TRUE

【总结】

  非workload下的io成本计算公式并不是官方给出的，而且要

记住这个公式的一些因子的值随着版本的不同而不同，但是他

们都有一个统一的目的：在cbo评估成本时，告诉它一次性读取

的块数总是比期望值要小一些，而我们知道一些打断多块读的

情况有：

  段头块读

  中间遇到缓冲块

  达到一个区的边界块则重启多块读

...