oracle實驗記錄 （predicate對cpu cost的影響）

fufuh2o發表於2009-09-01
Oracle


理解謂詞對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中好象沒起作用

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613681/，如需轉載，請註明出處，否則將追究法律責任。

相關文章