理解谓词对cpu cost的影响
这个实验脚本来自cost-based oracle,动手实验一次加深印象 

SQL> create table t1(
  2  v1,
  3  n1,
  4  n2
  5  )
  6  as
  7  select
  8  to_char(mod(rownum,20)),
  9  rownum,
 10  mod(rownum,20)
 11  from
 12  all_objects
 13  where
 14  rownum <= 3000
 15  ;

表已创建。

SQL> select count(distinct v1) from t1;

COUNT(DISTINCTV1)
-----------------
               20~~~~~~~~~~~~~~~~~~~~V1 有20个不同值 char类 ,每个数对应150行

SQL> select count(distinct n1) from t1;

COUNT(DISTINCTN1)
-----------------
             3000~~~~~~~~~~~~~~~~~~~~~N1 有3000个不同值 NUMBER类 表示 是唯一的

SQL> select count(distinct n2) from t1;

COUNT(DISTINCTN2)
-----------------
               20~~~~~~~~~~~~~~~~~~~~N2 有20个不同值 NUMBER类 ,每个数对应150行

总共3000 rows

下面4个查询中 前3个使用+ ordered_predicates HINTS 强制按所写的predication顺序 从左到右
最后一个 让CBO优化器自己考虑 注意

  _pred_move_around                   = true (predication 移动 oracle自动排序predication)

  _optimizer_cost_model               = choose(基本使用CPU_COSTING 而不使用8I 的传统计算(只算IO))

SQL> exec dbms_stats.gather_table_stats('SYS','T1');

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> ed
已写入 file afiedt.buf

  1  select
  2  /*+ ordered_predicates */
  3  v1, n2, n1
  4  from
  5  t1
  6  where
  7  v1 = 1
  8  and n2 = 18
  9* and n1 = 998
 10  /

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)

SQL> ed
已写入 file afiedt.buf

  1  select
  2  /*+ordered_predicates */
  3  v1, n2, n1
  4  from
  5  t1
  6  where
  7  n1 = 998
  8  and n2 = 18
  9* and v1 = 1
SQL> /

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)

SQL> ed
已写入 file afiedt.buf

  1  select
  2  /*+ ordered_predicates */
  3  v1, n2, n1
  4  from
  5  t1
  6  where
  7  v1 = '1'
  8  and n2 = 18
  9* and n1 = 998
SQL> /

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("V1"='1' AND "N2"=18 AND "N1"=998)

SQL> ed
已写入 file afiedt.buf

  1  select
  2  v1, n2, n1
  3  from
  4  t1
  5  where
  6  v1 = 1
  7  and n2 = 18
  8* and n1 = 998
SQL> /

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)

看TRACE

使用
predication  顺序- filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)

select
/*+ ordered_predicates */
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998

  Access Path: TableScan
    Cost:  3.25  Resp: 3.25  Degree: 0
      Cost_io: 3.00  Cost_cpu: 1070604****************
      Resp_io: 3.00  Resp_cpu: 1070604
  Best:: AccessPath: TableScan
         Cost: 3.25  Degree: 1  Resp: 3.25  Card: 0.00  Bytes: 0

分析:总共3000行中oracle首先对v1 = 1 进行了3000次转换(to_number) 又在表里3000行中 让 V1与一个数值进行比较产生了 150行(3000/20) CPU进行了3000次比较操作
又在这150行中 n2与一个数值比较 产生8行(150/20) CPU进行150次比较操作, 最后在这8行中 N1与一个数值比较(N1 唯一) CPU进行8次比较操作,CPU一共进行了 3158次数值比较

+3000次转换操作

****************

使用
predication  顺序- filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)

select
/*+ordered_predicates */
v1, n2, n1
from
t1
where
n1 = 998
and n2 = 18
and v1 = 1

Access Path: TableScan
    Cost:  3.18  Resp: 3.18  Degree: 0
      Cost_io: 3.00  Cost_cpu: 762786************
      Resp_io: 3.00  Resp_cpu: 762786
  Best:: AccessPath: TableScan
         Cost: 3.18  Degree: 1  Resp: 3.18  Card: 0.00  Bytes: 0

分析:总共3000行中oracle 让N1与一个数值 进行了3000次比较 产生 1行(3000/3000) CPU进行3000次比较操作 ,对这一行 N2与一个数比较一次 产生1行 CPU进行了1次比较操作,

对这一行 V1与一个数比较一次CPU进行1次比较操作, 且V1转换一次
所以为3002次比较操作+1次转换

这个是最节约CPU成本的

******************************
使用
predication  顺序- filter("V1"='1' AND "N2"=18 AND "N1"=998)
select
/*+ ordered_predicates */
v1, n2, n1
from
t1
where
v1 = '1'
and n2 = 18
and n1 = 998

  Access Path: TableScan
    Cost:  3.18  Resp: 3.18  Degree: 0
      Cost_io: 3.00  Cost_cpu: 770604**********************
      Resp_io: 3.00  Resp_cpu: 770604
  Best:: AccessPath: TableScan
         Cost: 3.18  Degree: 1  Resp: 3.18  Card: 0.00  Bytes: 0
分析:总共3000行中 oracle  让V1与一个值比较 产生150行 CPU进行了3000次比较操作,150行中 让N2与一个值比较cpu进行比较操作150次产生8行,又在这8行中 让N1与一个数值比

较 CPU比较8次产生1行
注意没有进行转换3000次对V1
所以ORACLE CPU进行了3158次比较操作  

SQL> select 1070604-770604 from dual;

1070604-770604
--------------
        300000

3000次转换 CPU COST 多了 300000 这样可以得出 一次to_number隐式转换 需要100次 CPU操作(300000/3000=100)

**************************
oracle optimizer自动选  顺序- filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)

select
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998
Access Path: TableScan
    Cost:  3.18  Resp: 3.18  Degree: 0
      Cost_io: 3.00  Cost_cpu: 762786*********************
      Resp_io: 3.00  Resp_cpu: 762786
  Best:: AccessPath: TableScan
         Cost: 3.18  Degree: 1  Resp: 3.18  Card: 0.00  Bytes: 0

分析:最后这个ORACLE 采用了第2个SELECT 所使用的PREDICATION 顺序 没有按照 指定的顺序 oracle进行COST 评估后采用了这个顺序
  _pred_move_around                   = true 由于这个参数 让ORACLE自动对PREDICATION 选择合适的顺序

SQL> alter system set "_pred_move_around"=false;************实验改下

系统已更改。
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL>  select
  2   v1, n2, n1
  3   from
  4   t1
  5   where
  6   v1 = 1
  7   and n2 = 18
  8   and n1 = 998
  9  /

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)~~~~~~~~~~~~~~~~雪特还是自己变化了

_pred_move_around              FALSE                     FALSE     FALSE      FALSE

enables predicate move-around

10GR2中好象没起作用