Oracle SQL Tuning_03 CBO算法

Oracle SQL Tuning

全表扫描成本计算法则实例

/img/2018/0725_sqltune_08.png

创建表空间、用户及授权

-- 查看数据库版本
select * from v$version where rownum<=1;
--=================================================================================
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--=================================================================================

col FILE_NAME for a50
select file_name from dba_data_files;
--=================================================================================
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/disdb01.dbf
--=================================================================================

create tablespace cbo datafile '/u01/app/oracle/oradata/orcl/cbo.dbf'
  size 100m autoextend on maxsize 300m uniform size 1m segment space management manual;

create user cbo identified by cbo default tablespace cbo;
grant dba to cbo;

设置多块读参数

col value for a50
select value from v$parameter where name='db_file_multiblock_read_count';
--=================================================================================
VALUE
--------------------------------------------------
128
--=================================================================================

alter system set db_file_multiblock_read_count=16;

show parameter db_file_multiblock_read_count
--=================================================================================
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
--=================================================================================

建表并插入数据

-- 设置pctfree 99,一行一个block
conn cbo/cbo
create table fulltable as select * from dba_objects where 1=0 ;
alter table fulltable  pctfree 99 pctused 1;
insert into fulltable  select * from dba_objects where rownum<2;
alter table fulltable  minimize records_per_block;
insert into fulltable  select * from dba_objects where rownum<1000;
commit;

收集表统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CBO',
tabname => 'FULLTABLE',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE);
END;
/

select owner,blocks from dba_tables where owner='CBO' and table_name='FULLTABLE';
--=================================================================================
OWNER                              BLOCKS
------------------------------ ----------
CBO                                  1000
--=================================================================================

查看SQL执行计划

explain plan for select count(*) from fulltable;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--=================================================================================
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2411201521

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   221   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| FULLTABLE |  1000 |   221   (1)| 00:00:03 |
------------------------------------------------------------------------
--=================================================================================

计算I/O COST

select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
--=================================================================================
PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           1200
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
--=================================================================================

mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size / iofrrspeed
         = 10 + 16 * 8192 / 4096 = 42

sreadtim = ioseektim + db_block_size / iotfrspeed 
         = 10 + 8192 / 4096 = 12

-- mreadtim
select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
 (select value from v$parameter where name = 'db_file_multiblock_read_count') *
 (select value from v$parameter where name = 'db_block_size') /
 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim" from dual;
--=================================================================================
  mreadtim
----------
        42
--=================================================================================

-- sreadtim
select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
       (select value from v$parameter where name = 'db_block_size') /
       (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
  from dual; 
--=================================================================================
  sreadtim
----------
        12
--=================================================================================

-- MRDs = +Blks / MBRC
-- 多块读次数 = 表的块数 / 多块读参数 = 1000 / 16
-- CEIL(n) 取大于等于数值n的最小整数
-- I/O Cost = 1 + CEIL(#MRDs * (mreadtim / sreadtim))
SELECT 1+CEIL((1000/16)*(42/12)) FROM DUAL;
--=================================================================================
1+CEIL((1000/16)*(42/12))
-------------------------
                      220
--=================================================================================

计算CPU COST

select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
--=================================================================================
PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           1200    # CPUSPEED
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
--=================================================================================

-- CPUCycles(CPU周期)等于PLAN_TABLE里面的CPU_COST
explain plan for select count(*) from fulltable;
select cpu_cost from plan_table where object_name='FULLTABLE';
--=================================================================================
  CPU_COST
----------
   7271440
--=================================================================================

CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)

select ROUND(7271440 / 1200 / 1000 / 12) CPU_COST from dual;
--=================================================================================
  CPU_COST
----------
         1
--=================================================================================

FTS COST


SELECT 1+CEIL((1000/16)*(42/12)) + ROUND(7271440/1200/1000/12) FTS_COST FROM DUAL;
--=================================================================================
  FTS_COST
----------
       221
--================================================================================= 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2411201521

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   221   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| FULLTABLE |  1000 |   221   (1)| 00:00:03 |
------------------------------------------------------------------------

隐含参数: _table_scan_cost_plus_one

-- 根据该参数的描述，在table full scan和index fast full scan的时侯会将cost + 1
col name for a30
col VALUE for a20
col DESCRIB for a60

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
 FROM x$ksppi x, x$ksppcv y
  WHERE x.inst_id = USERENV ('Instance')
   AND y.inst_id = USERENV ('Instance')
   AND x.indx = y.indx
   AND x.ksppinm LIKE '%_table_scan_cost_plus_one%';
--=================================================================================
NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_table_scan_cost_plus_one      TRUE                 bump estimated full table scan and index ffs cost by one
--=================================================================================

-- 棼用隐含参数
conn / as sysdba
alter system set "_table_scan_cost_plus_one" = false;

conn cbo/cbo
explain plan for select count(*) from fulltable;

select * from table(dbms_xplan.display);
--=================================================================================
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2411201521

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   220   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| FULLTABLE |  1000 |   220   (1)| 00:00:01 |
------------------------------------------------------------------------
--=================================================================================

索引范围扫描成本算法

创建表和索引

conn cbo/cbo
create table idx_range as select * from dba_objects;
create index idx_range_id on idx_range(object_id);

收集统计信息

BEGIN  
 DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'CBO',  
 tabname => 'IDX_RANGE',  
 estimate_percent => 100,  
 method_opt => 'for all columns size auto',  
 degree => DBMS_STATS.AUTO_DEGREE,  
 cascade => TRUE);  
END;  
/

select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_RANGE_ID';  
--=================================================================================
LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
         31          1               244
--=================================================================================

计算有效选择率和计算基数

select 
    b.num_rows,  
    a.num_distinct,
    a.num_nulls,  
    utl_raw.cast_to_number(high_value) high_value,
    utl_raw.cast_to_number(low_value) low_value,
    (b.num_rows-a.num_nulls) "NUM_ROWS-NUM_NULLS",
    utl_raw.cast_to_number(high_value)- utl_raw.cast_to_number(low_value)"HIGH_VALUE-LOW_VALUE"  
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
    and a.table_name = b.table_name
    and a.owner ='CBO'
    and a.table_name = upper('IDX_RANGE')
    and a.column_name='OBJECT_ID';
--=================================================================================
  NUM_ROWS NUM_DISTINCT  NUM_NULLS HIGH_VALUE  LOW_VALUE NUM_ROWS-NUM_NULLS HIGH_VALUE-LOW_VALUE
---------- ------------ ---------- ---------- ---------- ------------------ --------------------
     14329        14329          0      24246          2              14329                24244
--=================================================================================

有效选择率 = (limit-low_value) / (high_value-low_value)=(1000-2)/24244
计算基数 = 基本基数 * 有效选择率 = 14329 * (1000-2)/24244

查看执行计划

explain plan for select owner from idx_range where object_id<1000;  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--=================================================================================
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1377850575

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   590 |  5900 |    14   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| IDX_RANGE    |   590 |  5900 |    14   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_RANGE_ID |   590 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<1000)
--=================================================================================

I/O成本公式

-- Index Access I/O = LVLS + CEIL($LB * ix_sel)
SQL> select 1+ceil(31*(1000-2)/24244) from dual;

1+CEIL(31*(1000-2)/24244)
-------------------------
                        3

-- Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filiters)
SQL> select ceil(244 * (1000-2)/24244) from dual;

CEIL(244*(1000-2)/24244)
------------------------
                      11

-- I/O Cost = Index Access I/O + Table Access I/O Cost
SQL> select 3 + 11 from dual;

      3+11
----------
        14

CPU成本公式

-- CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
explain plan for select owner from idx_range where object_id<1000;  
select cpu_cost from plan_table where object_name = 'IDX_RANGE';

  CPU_COST
----------
    341600

select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN' and pname='CPUSPEEDNW';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           1194

select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
       (select value from v$parameter where name = 'db_block_size') /
       (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
  from dual; 

  sreadtim
----------
8.25704424

select ROUND(341600 / 1194 / 1000 / 8.25704424) CPU_COST from dual;

  CPU_COST
----------
         0

嵌套循环连接成本算法L

专业术语：

1、为什么执行计划中的COST很小，SQL却跑的很慢？（利用实验步骤证明）
统计信息不正确

2、根据以下的Hash Join的COST算法用实验过程证明？（写出整个计算过程）
HJ Cost = Outer Table Cost + Inner Table Cost + Join Cost

3、怎么判断NEST LOOP连接有问题，导致SQL执行缓慢？

文章目录
  1. 1. 全表扫描成本计算法则实例
    1. 1.1. 创建表空间、用户及授权
    2. 1.2. 设置多块读参数
    3. 1.3. 建表并插入数据
    4. 1.4. 收集表统计信息
    5. 1.5. 查看SQL执行计划
    6. 1.6. 计算I/O COST
    7. 1.7. 计算CPU COST
    8. 1.8. FTS COST
    9. 1.9. 隐含参数: _table_scan_cost_plus_one
  2. 2. 索引范围扫描成本算法
    1. 2.1. 创建表和索引
    2. 2.2. 收集统计信息
    3. 2.3. 计算有效选择率和计算基数
    4. 2.4. 查看执行计划
    5. 2.5. I/O成本公式
    6. 2.6. CPU成本公式
  3. 3. 嵌套循环连接成本算法L