Oracle：执行计划+cost成本计算

1.访问数据的存取方法

全表扫描（Full Table Scans, FTS）

 

通过ROWID的表存取（Table Access by ROWID或rowid lookup）

 

索引扫描（Index Scan或index lookup）：根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
            索引唯一扫描(index unique scan)：索引返回单值+值不在索引中

            索引范围扫描(index range scan)：

            索引全扫描(index full scan)：值在索引中+输出排序

            索引快速扫描(index fast full scan)：

 

2.目前为止，无论连接操作符如何，典型的连接类型共有3种：
排序 - - 合并连接(Sort Merge Join (SMJ) )

嵌套循环(Nested Loops (NL) )

哈希连接(Hash Join)

 

总结一下，在哪种情况下用哪种连接方法比较好：

排序 - - 合并连接(Sort Merge Join, SMJ)：
a) 对于非等值连接，这种连接方式的效率是比较高的。
b) 如果在关联的列上都有索引，效果更好。
c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。
d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。

 

嵌套循环(Nested Loops, NL)：
a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。
b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。

 

哈希连接(Hash Join, HJ)：
a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。
c) 只能用于等值连接中

 

http://database.51cto.com/art/200611/34273.htm

 

 

===========================================================================

 

According to the CPU costing model:

Cost = (

#SRds * sreadtim +

#MRds * mreadtim +

#CPUCycles / cpuspeed

) / sreadtim

where

#SRDs - number of single block reads

#MRDs - number of multi block reads

#CPUCycles - number of CPU Cycles

sreadtim - single block read time

mreadtim - multi block read time

cpuspeed - CPU cycles per second

 

  以下计算执行计划中表扫描的成本实验是在AIX平台下10.2.0.1进行的：

1、创建测试表

SQL>execute dbms_random.seed(0);

SQL>create table t1
    pctfree 99
    pctused 1
    as
    with generator as (
      select --+ materialize
        rownum id
      from all_objects
      where rownum <=3000
     )
    select /*+ ordered use_nl(v2) */
      rownum id,
      trunc(100*dbms_random.normal) val,
      rpad('x', 100) padding
    from
       generator v1,
       generator v2
    where rownum <=10000;

2、进行统计分析
SQL>begin
       dbms_stats.gather_table_stats(
          user,
          't1',
          cascade =>true,
          estimate_percent => null,
          method_opt => 'for all columns size 1'
          );
      end;
      /

3、查看实际的IO和CPU成本

SQL>alter system flush shared_pool;
SQL>alter session set events '10053 trace name context forever, level 2';
SQL>select max(val) from t1;
SQL>alter session set events '10053 trace name context off';

提取跟踪文件相应数值：
跟踪文件：
    cost_io=2221
    cost_cpu=73954130
    #Blks = 10146
    cost=2228.75

4、10g下noworkload统计信息以及相关参数查询

SQL>select pname, pval1 from sys.aux_stats$
     where sname='SYSSTATS_MAIN'；
 
  PNAME                               PVAL1
  ------------------------------ ----------
   CPUSPEEDNW                        795.134
   IOSEEKTIM                              10
   IOTFRSPEED                           4096 

SQL>show parameter DB_FILE_MULTIBLOCK_READ_COUNT;
 DB_FILE_MULTIBLOCK_READ_COUNT=16
该数值匹配到计算公式中mbrc

5、ORACLE10g的成本计算公式

cost = (#SRDs * SREADTIM +
          #MRDs * MREADTIM +
          #CPUCycles/cpuspeed)/SREADTIM
       = #SRDs +                            --因为是表扫描，所以该值为0
         #MRDs * MREADTIM/SREADTIM +        -- IO cost
         #CPUCycles/(cpuspeed * SREADTIM)   -- CPU cost

6、手工计算IO成本，看是否与步骤2中IO成本实际值匹配

手工计算：
      cost_io= #MRDs * MREADTIM/SREADTIM，所得数值取最大整数，另外隐含参数_table_scan_cost_plus_one=true，所以cost_io需要添加1。所以10g实际公式为：
    cost_io=ceiling(#MRDs * MREADTIM/SREADTIM) +1
 
其中：
    #MRDs=#Blks/mbrc=10146/16
    MREADTIM=IOSEEKTIM + mbrc*db_block_size/IOTFRSPEED
                       = 10+ 16*8k/4k=42
   SREADTIM=IOSEEKTIM + db_block_size/IOTFRSPEED
                       =10+8k/4k=12

   cost_io=ceiling(10146/16*42/12)+1=ceiling(2219.4375)+1=2220+1=2221

计算值与步骤2中实际IO成本cost_io=2221一致。

7、手工计算CPU成本

     cpu成本=#CPUCycles/(cpuspeed * SREADTIM)

    其中
         #CPUCycles等于10053跟踪文件中cost_cpu=73954130，
         cpuspeed=795.134MHz=795134Hz
         SREADTIM=12
    所以
       cpu成本=73954130/(795134*12)=7.75

8、总的成本计算值
     cost=cost_io+ cpu成本=2221+7.75=2228.75

以上的计算值与实际所获得的成本值完全一致。

备注：来源于Jonathan Lewis的《CBO基础》第二章节tablescan。

 

===============================================================================

 

OPTIMIZER_INDEX_COST_ADJ参数与成本计算：
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右
对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，
可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.
更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

本文通过实验对该参数的使用作出探讨和说明.

我们看到optimizer_index_cost_adj的缺省值为100.

 

[oracle@jumper udump]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL>

创建测试表:

 

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ind_owner on t(owner);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

 

我们分别观察一下全表扫描和索引访问的成本:

 

SQL> set autotrace traceonly


SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)



SQL> select /*+ index(t ind_owner) */ * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

 

Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估.

在比较的时候，Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较.这个转换需要一个转换因子.
就是optimizer_index_cost_adj:

optimizer_index_cost_adj * (Index Scan Cost) = 等价的 Full Scan Cost

这个 等价的 Full Scan Cost 就是来和全表扫描成本进行比较的.

而这个转换因子的临界值实际上就是Full Scan Cost 和 Index Scan Cost的比值.

即:

optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

 

 

 

SQL> set autotrace off
SQL> select (14/16)*100 from dual;

(14/16)*100
-----------
87.5

1 row selected.

我们通过调整optimizer_index_cost_adj来看一下执行计划的变化:

 

SQL> set autotrace traceonly

SQL> alter session set optimizer_index_cost_adj = 87;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=14 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

此时使用索引成本较低.等价全表扫描成本为:

87% * (Index Scan Cost) < Full Scan Cost

此时Oracle选择了索引.

SQL> alter session set optimizer_index_cost_adj = 88;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)

此时使用索引成本较高.等价全表扫描成本为:

88% * (Index Scan Cost) > Full Scan Cost

所以Oracle选择了全表扫描.