06-优化学习-成本计算

原创 ziyoo0830 2019-10-31

699

第6章成本计算

6.1、优化SQL需要看COST吗

COST是基于统计信息、基于数学公式计算出来的；

6.2、全表扫描成本计算

SCOTT@o11g>create table t_fullscan_cost as select * from dba_objects where 1=0 ;

SCOTT@o11g>alter table t_fullscan_cost pctfree 99 pctused 1 ;

SCOTT@o11g>insert into t_fullscan_cost select * from dba_objects where rownum <2 ;

SCOTT@o11g>alter table t_fullscan_cost minimize records_per_block ;

SCOTT@o11g>insert into t_fullscan_cost select * from dba_objects where rownum <1000 ;

SCOTT@o11g>begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'T_FULLSCAN_COST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                degree           => 1,
                                cascade          => true);
end;

SQL> select owner, blocks
  2    from dba_tables
  3   where owner = 'SCOTT'
  4     and table_name = 'T_FULLSCAN_COST';
OWNER                              BLOCKS
------------------------------ ----------
SCOTT                                1000

SCOTT@o11g>alter session set db_file_multiblock_read_count=16;
SCOTT@o11g>set autot traceonly
SCOTT@o11g>select count(*) from t_fullscan_cost ;


Execution Plan
----------------------------------------------------------
Plan hash value: 387824861

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


Statistics
----------------------------------------------------------
	 57  recursive calls
	 26  db block gets
       1024  consistent gets
	 24  physical reads
	  0  redo size
	526  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  4  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

执行计划中全表扫描的成本是272；

全表扫描成本的计算方式如下：

Cost = （

#SRds * sreadtim +

#MRds * mreadtim +

CPUCycles / cpuspeed

) / sreadtime

#SRds - number of single block reads 表示单块读次数
#MRds - number of multi block reads  表示多块读次数
#CPUCyles - number of CPU cycles     CPU时钟周期数
sreadtim - single block read time    一次单块读耗时，单位毫秒
mreadtim - multi block read time     一次多块读耗时，单位毫秒
cpuspeed - CPU cycles per second     每秒CPU时钟周期数

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     3074.07407   -- cpuspeed 
IOSEEKTIM                              10   -- I/O寻道寻址耗时
IOTFRSPEED                           4096   -- I/O传输速度
SREADTIM                          357.434
MREADTIM                       
CPUSPEED                             1540
MBRC                           
MAXTHR                         
SLAVETHR                       
9 rows selected

因为MBRC为NULL，所以CBO采用了非工作量来计算成本；

在全表扫描成本计算公式中，#SRds=0，因为全表扫描一般都是多块读;

#MRds=表的块数/多块读参数=1000/16;

sreadtim=ioseektim + db_block_size/iotfrspeed，单块读耗时=I/O寻道寻址耗时+块大小 / I/O传输速度；

计算单块读耗时：
SQL>  select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
  2          (select value from v$parameter where name = 'db_block_size') /
  3          (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
  4     from dual;
  sreadtim
----------
        12

mreadtim=ioseektim + db_file_multiblock_count * db_block_size / I/O传输速度；

多块读耗时=I/O寻道寻址耗时+多块读参数*块大小/ I/O传输速度；

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

CPUCycles等于PLAN_TABLE/V$SQL_PLAN里面的CPU_COST;

SCOTT@o11g>explain plan for select count(*) from t_fullscan_cost ;

Explained.

SCOTT@o11g>select cpu_cost from plan_table where rownum<=1 ;

  CPU_COST
----------
   7121590

计算全表扫描成本：

SCOTT@o11g>l
 1  select (0 * 12 + 1000 / 16 * 42 / 12 + 7121590 / (3074.07407 * 1000) / 12) cost
 2*   from dual
SCOTT@o11g>/

     COST
----------
218.943055

SYS@o11g>l
 1  SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
 2    FROM x$ksppi x, x$ksppcv y
 3   WHERE x.inst_id = USERENV('Instance')
 4	AND y.inst_id = USERENV('Instance')
 5	AND x.indx = y.indx
 6*	AND x.ksppinm LIKE '%_table_scan_cost_plus_one%'
SYS@o11g>/

NAME					  VALUE	 DESCRIB
------------------------- ----- ----------------
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one

6.3、索引范围扫描成本计算

TEST01@testora>create table t_indexscan_cost as select * from dba_objects ;

Table created.

TEST01@testora>create index idx_cost on t_indexscan_cost(object_id);

Index created.
收集统计信息
begin
  dbms_stats.gather_table_stats(ownname          => 'TEST01',
                                tabname          => 'T_INDEXSCAN_COST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                degree           => 1,
                                cascade          => true);
end;
查看表总行数、object_id最大值、object_id最小值以及null值;
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,
       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.table_name = 'T_INDEXSCAN_COST'
   and a.column_name = 'OBJECT_ID';
   
执行计划：
TEST01@testora>select owner from t_indexscan_cost where object_id <1000 ;

997 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1756649757

-----------------------------------------------------------------------------------------
| Id  | Operation		    | Name	       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		       |   975 | 10725 |    19	 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEXSCAN_COST |   975 | 10725 |    19	 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_COST	       |   975 |       |     4	 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<1000)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	151  consistent gets
	  0  physical reads
	  0  redo size
      17697  bytes sent via SQL*Net to client
       1245  bytes received via SQL*Net from client
	 68  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	997  rows processed

索引范围扫描的成本计算公式

cost =  
 blevel +  
 celiling(leaf_blocks *effective index selectivity) +  
 celiling(clustering_factor * effective table selectivity)

获取blevel、leaf_blocks、clustering_factor从如下SQL中获取：

TEST01@testora>select leaf_blocks,blevel,clustering_factor from dba_indexes where owner='TEST01' and index_name='IDX_COST' ;

LEAF_BLOCKS	BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
	191	     1		    1323

未完，待续…

6.4、SQL优化核心思想

核心思想：想方设法减少SQL的物理I/O次数（不管是单块读次数还是多块读次数）。

第7章查询转换

7.1、子查询非嵌套

子查询非嵌套（Subquery Unnesting）：当where子查询中有in、not in、exists、not exists等，CBO会尝试将子查询展开（unnest），从而消除FILTER，这个过程就叫作子查询非嵌套。子查询非嵌套的目的就是消除FILTER。

为什么要消除FILTER呢？因为FILTER的驱动表是固定的，一旦驱动表被固定，那么执行计划也就被固定了。对于DBA来说这并不是好事，因为一旦固定的执行计划本身是错误的（低效的），就会引起性能问题，想要提升性能必须改写SQL语句，但是这时SQL已经上线，无法更改，所以，一定要消除FILTER。

样例

TEST01@testora>set lines 200
select ename, deptno
  from scott.emp
 where exists (select deptno
          from scott.dept
         where dname = 'CHICAGO'
           and emp.deptno = dept.deptno
        union
        select deptno
          from scott.dept
         where loc = 'CHICAGO'
 11             and dept.deptno = emp.deptno);

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2705207488

-----------------------------------------------------------------------------------------
| Id  | Operation		       | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	 |     5 |    45 |    15  (40)| 00:00:01 |
|*  1 |  FILTER 		       |	 |	 |	 |	      | 	 |
|   2 |   TABLE ACCESS FULL	       | EMP	 |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   SORT UNIQUE		       |	 |     2 |    48 |     4  (50)| 00:00:01 |
|   4 |    UNION-ALL		       |	 |	 |	 |	      | 	 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |    1 |    13 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN	       | PK_DEPT |     1 |	 |     0   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |    1 |    11 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN	       | PK_DEPT |     1 |	 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "SCOTT"."DEPT" "DEPT" WHERE
	      "DEPT"."DEPTNO"=:B1 AND "DNAME"='CHICAGO')UNION (SELECT "DEPTNO" FROM
	      "SCOTT"."DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=:B2 AND "LOC"='CHICAGO')))
   5 - filter("DNAME"='CHICAGO')
   6 - access("DEPT"."DEPTNO"=:B1)
   7 - filter("LOC"='CHICAGO')
   8 - access("DEPT"."DEPTNO"=:B1)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 19  consistent gets
	  0  physical reads
	  0  redo size
	714  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  3  sorts (memory)
	  0  sorts (disk)
	  6  rows processed
改写SQL，消除FILTER：(/*+ no_unnest */ , /*+ unnset */)

select ename, deptno
  from scott.emp
 where exists (select 1
          from (select deptno
                  from scott.dept
                 where dname = 'CHICAGO'
                union
                select deptno
                  from scott.dept
                 where loc = 'CHICAGO') a
 11           where a.deptno = emp.deptno);

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4243948922

------------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |	   5 |	 110 |	  11  (19)| 00:00:01 |
|*  1 |  HASH JOIN SEMI       |      |	   5 |	 110 |	  11  (19)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | EMP  |	  14 |	 126 |	   3   (0)| 00:00:01 |
|   3 |   VIEW		      |      |	   2 |	  26 |	   8  (25)| 00:00:01 |
|   4 |    SORT UNIQUE	      |      |	   1 |	  24 |	   8  (25)| 00:00:01 |
|   5 |     UNION-ALL	      |      |	     |	     |		  |	     |
|*  6 |      TABLE ACCESS FULL| DEPT |	   1 |	  13 |	   3   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL| DEPT |	   1 |	  11 |	   3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("A"."DEPTNO"="EMP"."DEPTNO")
   6 - filter("DNAME"='CHICAGO')
   7 - filter("LOC"='CHICAGO')


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 18  consistent gets
	  0  physical reads
	  0  redo size
	714  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  6  rows processed

7.2、视图合并（view merge）

视图合并（View Merge）：当SQL语句中有内联视图（in-line view，from后面的子查询），或者SQL语句中有用create view创建的视图，CBO会尝试将内联视图/视图拆开，进行等价的改写，这个过程就叫作视图合并。如果没有发生视图合并，在执行计划中，我们可以看到VIEW关键字，而且视图/子查询会作为一个整体。如果发生了视图合并，那么视图/子查询就会被拆开，而且执行计划中视图/子查询部分就没有VIEW关键字。

*/*+ no_merge /

7.3、谓词推入（pushing predicate）

谓词推入（Pushing Predicate）：当SQL语句中包含不能合并的视图，同时视图有谓词过滤（也就是where过滤条件），CBO会将谓词过滤条件推入视图中，这个过程就叫作谓词推入。谓词推入的主要目的就是让Oracle尽可能早地过滤掉无用的数据，从而提升查询性能。

关闭连接谓词推入：
alter session set "_push_join_predicate" = false ;

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

学习笔记-ziyoo0830（共13篇）

个人学习

第6章成本计算
- 6.1、优化SQL需要看COST吗
- 6.2、全表扫描成本计算
- 6.3、索引范围扫描成本计算
  - 索引范围扫描的成本计算公式
- 6.4、SQL优化核心思想
第7章查询转换
- 7.1、子查询非嵌套
  - 样例
- 7.2、视图合并（view merge）
- 7.3、谓词推入（pushing predicate）