基於CBO優化器謂詞選擇率的計算方法

azzotest發表於2015-10-30

選擇率selectivity是Oracle的CBO中比較重要的一個概念。和selectivity經常一起提到的一個概念是cardinality，其實這兩個概念的本質上是一樣的。selectivity是指一個SQL操作的得出結果集佔原來結果集的百分比，而cardinality就是指一個SQL操作的得出結果集的行數，CBO是通過selectivity來計算cardinality的，也就是說cardinality=selectivity*原結果集行數。建立一個測試表

create table t1 as
select
trunc(dbms_random.value(1,13)) month_no
from all_objects
where rownum< =1200;

sys@JINGYONG> select high_value,low_value,num_distinct,num_nulls,density
  2  from dba_tab_col_statistics where owner='SYS' and table_name='t1'
  3  and column_name='MONTH_NO';

HIGH_VALUE  LOW_VALUE    NUM_DISTINCT  NUM_NULLS    DENSITY
C10D        C102         12            0            .083333333

sys@JINGYONG> select blocks,num_rows from user_tables where table_name='t1
';

    BLOCKS   NUM_ROWS
---------- ----------
         2       1200

sys@JINGYONG> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     40

sys@JINGYONG> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

選擇率=所需的空間除以總可用空間通過計算(user_tab_col_statistics.high_value - user_tab_col_statistics.low_value)可以得出,上面的例子中整個區間大小為11,當計算得到的是11,那麼我們就知道其中肯定有錯誤.因為我們知道,上例中有12個離散的月份,但優化器採用的演算法卻將這些資料看作連續的變化資料,並且總區間大小為11. 單列謂詞的選擇率示例1 select count(*) from t1 where month_no>8

sys@JINGYONG> select count(*) from t1 where month_no>8;

  COUNT(*)
----------
       396

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6u62fruy6276s, child number 0
-------------------------------------
select count(*) from t1 where month_no>8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL|       T1 |   436 |  1308 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter("MONTH_NO">8)

當where month_no>8:這是一個無邊界(一側沒有邊界)的開(不包含8)區間. 選擇率=(high_value-limit)/(high_value-low_value)=(12-8)/(12-1)=4/11 基數=1200*4/11=436.363636 成本是2 全表掃描的成本計算公式: 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 成本指的是花費在塊讀取上的時間,加上花在多塊讀取上的時間,再加上所需的cpu處理的時間,然後將總和除以單塊讀取所花費的時間,也就是說,成本是語句的預計執行時間的總和,以單塊讀取時間單元的形式來表示. 如果oracle收集了作業系統統計資訊，那麼CBO採用工作量統計模式計算代價如果oracle沒有收集作業系統統計資訊，那麼CBO採用非工作量統計模式計算代價我們現在處於“非工作量統計模式”

sys@JINGYONG> select pname,pval1 from aux_stats$;

PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   0
CPUSPEEDNW                       1149.062
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

#SRds=0,因為是全表掃描，單塊讀為0，全都使用的是多塊讀 #MRds=表的塊數/多塊讀引數=2/40=0.05 mreadtim=ioseektim+(db_file_multiblock_count*db_block_size/iotftspeed)=10+(40*8192/4096)=90 sreadtim=ioseektim+(db_block_size/iotfrspeed)=10+(8192/4096)=12 CPUCycles 等於 PLAN_TABLE裡面的CPU_COST

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>8;

已解釋。

sys@JINGYONG> select cpu_cost from plan_table;

  CPU_COST
----------
    254243

cpuspeed 等於 CPUSPEEDNW= 1149.062 COST=(0*12/12)+(0.05*90/12)+(254243/1149.062/12/1000)(毫秒換算成秒)= 0+0.375+0.01843=0.39343 0.375是IO成本 0.01843是CPU成本手工計算出來的COST取最接近的整數等於1和我們看到的2有差別這是由於隱含引數_table_scan_cost_plus_one引數造成的

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y
  2    WHERE x.inst_id = USERENV ('Instance')
  3     AND y.inst_id = USERENV ('Instance')
  4     AND x.indx = y.indx
  5     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

根據該引數的描述，在table full scan和index fast full scan的時候會將cost+1 即 1+1=2; 示例2 select count(*) from t1 where month_no>=8 這是一個無邊界的閉(包含8)區間,因此需要對演算法進行調整.具體調整過程為必須包括閉合值(此處即為8)所在的行--也就是要增加一個1/num_distinct(要注意的是oracle8i使用的是density而不是1/num_distinct,但是,如果沒有獲取統計資訊或者沒有直方圖可用,那麼無須注意這一差別) 選擇率=(high_value-limit)/(high_value-low_value)+1/num_distinct=4/11+1/12 基數=1200*(4/11+1/12)=536.363636

sys@JINGYONG> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     40

sys@JINGYONG> select count(*) from t1 where month_no>=8;

  COUNT(*)
----------
       497

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g748ttx5rv2p1, child number 0
-------------------------------------
select count(*) from t1 where month_no>=8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   536 |  1608 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter("MONTH_NO">=8)

示例3 select count(*) from t1 where month_no<8 選擇率=(limit-low_value)/(high_value-low_value)=(8-1)/(12-1)=7/11 基數=1200*7/11=763.636364

sys@JINGYONG> select count(*) from t1 where month_no<8;

  COUNT(*)
----------
       703

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
SQL_ID  cpvw8yxstbtng, child number 0
-------------------------------------
select count(*) from t1 where month_no<8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   764 |  2292 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter("MONTH_NO"<8)

示例4 select count(*) from t1 where month_no< =8 選擇率=(limit-low_value)/(high_value-low_value)+1/num_distinct=(8-1)/(12-1)+1/12 基數=1200*(7/11+1/12)=863.636364

sys@JINGYONG> select count(*) from t1 where month_no< =8;

  COUNT(*)
----------
       804

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  buhw0y52jy3nr, child number 0
-------------------------------------
select count(*) from t1 where month_no< =8

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   864 |  2592 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter("MONTH_NO"<=8)

示例5 select count(*) from t1 where month_no between 6 and 9 兩邊都是有界的閉區間它其實與 select count(*) from t1 where month_no>=6 and month_no< =9是等價的.它們給出了兩個閉合值.因此要對演算法進行兩次調整. 選擇率=(high_limit-low_limit)/(high_value-low_value)+1/num_distinct+1/num_distinct=(9-6)/(12-1)+1/12+1/12 基數=1200*(3/11+1/6)=527.272727

sys@JINGYONG> select count(*) from t1 where month_no between 6 and 9;

  COUNT(*)
----------
       421

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  83ud81y133kxm, child number 0
-------------------------------------
select count(*) from t1 where month_no between 6 and 9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   527 |  1581 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

sys@JINGYONG> select count(*) from t1 where month_no>=6 and month_no< =9;

  COUNT(*)
----------
       421

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  74gjsbjjdv97k, child number 0
-------------------------------------
select count(*) from t1 where month_no>=6 and month_no< =9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   527 |  1581 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter(("MONTH_NO">=6 AND "MONTH_NO"< =9))

示例7 select count(*) from t1 where month_no>=6 and month_no<9 選擇率=(high_limit-low_limit)/(high_value-low_value)+1/num_distinct=(9-6)/(12-1)+1/12 基數=1200*(3/11+1/12)=427.272727

sys@JINGYONG> select count(*) from t1 where month_no>=6 and month_no<9;

  COUNT(*)
----------
       328

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  5w0n5237vpngb, child number 0
-------------------------------------
select count(*) from t1 where month_no>=6 and month_no<9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   427 |  1281 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter(("MONTH_NO">=6 AND "MONTH_NO"<9))

示例8 select count(*) from t1 where month_no>6 and month_no< =9 選擇率=(high_limit-low_limit)/(high_value-low_value)+1/num_distinct=(9-6)/(12-1)+1/12 基數=1200*(3/11+1/12)=427.272727

sys@JINGYONG> select count(*) from t1 where month_no>6 and month_no< =9;

  COUNT(*)
----------
       303

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  bbd2wxjratndg, child number 0
-------------------------------------
select count(*) from t1 where month_no>6 and month_no< =9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   427 |  1281 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter(("MONTH_NO">6 AND "MONTH_NO"< =9))

示例9 select count(*) from t1 where month_no>6 and month_no<9 選擇率=(high_limit-low_limit)/(high_value-low_value)=(9-6)/(12-1) 基數=1200*(3/11)=327.272727

sys@JINGYONG> select count(*) from t1 where month_no>6 and month_no<9;

  COUNT(*)
----------
       210

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  6axnk33swbtpw, child number 0
-------------------------------------
select count(*) from t1 where month_no>6 and month_no<9

Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1 |   327 |   981 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter(("MONTH_NO">6 AND "MONTH_NO"<9))

對於使用繫結變數的謂詞優化器就無法知道任何變數的值也不知道繫結變數的型別,因此,這種情況下優化器就會使用一個固定的選擇率來產生執行計劃.例如:

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>:b1;

已解釋。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

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

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

   2 - filter("MONTH_NO">TO_NUMBER(:B1))

sys@JINGYONG> explain plan for select count(*) from t1 where month_no between :b1 and :b2;

已解釋。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

--------------------------------------------------------------------------------

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |          |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:B1)< =TO_NUMBER(:B2))
   3 - filter("MONTH_NO">=TO_NUMBER(:B1) AND "MONTH_NO"< =TO_NUMBER(:B2))

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>=:b1
;

已解釋。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

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

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

   2 - filter("MONTH_NO">=TO_NUMBER(:B1))

已選擇14行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

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

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

   2 - filter("MONTH_NO"<to_number (:B1))

已選擇14行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no< =:b1
;

已解釋。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3337892515

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

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

   2 - filter("MONTH_NO"< =TO_NUMBER(:B1))

已選擇14行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>=:b1
 and month_no< =:b2;

已解釋。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

--------------------------------------------------------------------------------

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |          |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:B1)< =TO_NUMBER(:B2))
   3 - filter("MONTH_NO">=TO_NUMBER(:B1) AND "MONTH_NO"< =TO_NUMBER(:B2))

已選擇16行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>=:b1
 and month_no select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

--------------------------------------------------------------------------------

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |          |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:B1)<to_number (:B2))
   3 - filter("MONTH_NO">=TO_NUMBER(:B1) AND "MONTH_NO"<to_number (:B2))

已選擇16行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>:b1
and month_no select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

--------------------------------------------------------------------------------

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |          |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:B1)<to_number (:B2))
   3 - filter("MONTH_NO">TO_NUMBER(:B1) AND "MONTH_NO"<to_number (:B2))

已選擇16行。

sys@JINGYONG> explain plan for select count(*) from t1 where month_no>:b1
and month_no< =:b2;

已解釋。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 57893822

--------------------------------------------------------------------------------

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |          |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |

|*  2 |   FILTER            |          |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| T1 |   101 |   303 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:B1)<to_number (:B2))
   3 - filter("MONTH_NO">TO_NUMBER(:B1) AND "MONTH_NO"< =TO_NUMBER(:B2))

對於上面所有使用繫結變數的查詢其評估的基數都是101使用固定選擇率當查詢超出列的最低/最高界限測試的結果如下.其評估的基數是100使用固定選擇率

sys@JINGYONG> select count(*) from t1 where month_no between 14 and 17;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8baayn49yujkh, child number 0
-------------------------------------
select count(*) from t1 where month_no between 14 and 17

Plan hash value: 3337892515

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

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

   2 - filter(("MONTH_NO">=14 AND "MONTH_NO"< =17))

已選擇19行。

sys@JINGYONG> select count(*) from t1 where month_no between 18 and 21;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9agzhp783caa1, child number 0
-------------------------------------
select count(*) from t1 where month_no between 18 and 21

Plan hash value: 3337892515

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

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

   2 - filter(("MONTH_NO">=18 AND "MONTH_NO"< =21))

已選擇19行。

sys@JINGYONG> select count(*) from t1 where month_no between 24 and 27;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dksv39k0vx5sh, child number 0
-------------------------------------
select count(*) from t1 where month_no between 24 and 27

Plan hash value: 3337892515

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

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

   2 - filter(("MONTH_NO">=24 AND "MONTH_NO"< =27))

已選擇19行。

sys@JINGYONG> select count(*) from t1 where month_no between -11 and 0;

  COUNT(*)
----------
         0

sys@JINGYONG> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  drdqd21q24hzy, child number 0
-------------------------------------
select count(*) from t1 where month_no between -11 and 0

Plan hash value: 3337892515

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

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

   2 - filter(("MONTH_NO"< =0 AND "MONTH_NO">=(-11)))

已選擇19行。

雙謂詞選擇率為了能夠計算通用的聯合謂詞選擇率,需要用到以下3種基本公式,它們是以選擇率的形式給出的,而不是以基數的形式給出的.
(謂詞1 and 謂詞2)的選擇率=謂詞1的選擇率+謂詞2的選擇率
(謂詞1 or 謂詞2)的選擇率=謂詞1的選擇率+謂詞2的選擇率-(謂詞1 and 謂詞2)的選擇率
(not 謂詞1)的選擇率=1-謂詞1的選擇率,繫結變數不能使用這個公式進行計算
下面是一組概率論方面的公式
p(a and b)=p(a)*p(b)
p(a or b)=p(a)+p(b)-p(a and b)=p(a)+p(b)-p(a)*p(b)
p(not a)=1-p(a)
上面的聯合謂詞選擇率與概率論公式是等價的.

首先,我們來細緻研究如下的where子句:
select count(*) from T1 where month_no>8 or month_no<=8;
利用簡單的選擇率計算公式required range/total range可以得出謂詞1的選擇率為(12-8)/(12-1)=4/11=0.363636 同樣的.
謂詞2的選擇率為(8-1)/(12-1)+1/12=7/11+1/12=0.719696
(p1 or p2)的選擇率計算公式為p1的選擇率+p2的選擇率-(p1 and p2)的選擇率,因此,
聯合的選擇率為 0.363636+0.719696-(0.363636*0.719696)=0.8216
將這個選擇率乘以1200行,進行四捨五入後,得到結果後為986,很明顯,這個結果和人的直觀思維得到的結果不一樣. 當我們將多個謂詞應用到某個表時,需要確定測試的列之間是否存在依賴關係.如果存在依賴關係,優化器得到的選擇率將是錯誤的,基數也就是錯誤的,因此也就無法得到合適的執行計劃.

小結:
為了估計一組謂詞返回資料的行數,優化器首先計算選擇率(返回資料行數的分數),然後將其與輸入行數相乘.對於單個列上的單個謂詞，優化器將利用不同值的數目或density作為計算謂詞選擇率的基礎.對於單個列上基於的區間謂詞,優化器採用分數required range/total available range進行一些端點值調整的方式來計算謂詞選擇率.對於包含繫結變數的區間謂詞來說,優化器使用硬編碼常量作為選擇率優化器通過使用類似於計算獨立事件的聯合概率方式的公式來計算聯合謂詞的選擇率.如果列中包含的資料集並不是相互獨立的話,在選擇率的計算方面將會出現誤差,從而導致基數出現誤差.

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

基於CBO最佳化器謂詞選擇率的計算方法
2014-01-15
【cbo計算公式】Join 選擇率(六)
2018-08-19
公式
【cbo計算公式】單表選擇率(二)
2018-08-19
公式
CBO Cost Formulas基於成本優化器的成本計算公式大全
2017-11-12
ORM優化公式
CBO中基數（cardinality）、可選擇率（selectivity）的計算公式
2017-06-06
公式
【cbo計算公式】Frequenccy 直方圖選擇率(三)
2018-08-19
公式直方圖
【cbo計算公式】Height-balance直方圖選擇率(四)
2018-08-19
公式直方圖
cbo心得(選擇率,基數,直方圖)(一)
2008-08-10
直方圖
cbo心得(選擇率,基數,直方圖)(二)
2008-08-10
直方圖
cbo心得(選擇率,基數,直方圖)(三)
2008-08-10
直方圖
cbo心得(選擇率,基數,直方圖)(四)
2008-08-10
直方圖
cbo心得(選擇率,基數,直方圖)(五)
2008-08-10
直方圖
【CBO】基於成本優化器的基本原則（二）
2010-12-08
優化
【CBO】基於成本優化器的基本原則（一）
2010-11-25
優化
執行計劃-4：謂詞的選擇時機與使用細節
2019-11-06
優化擁有謂詞or的子查詢
2011-07-13
優化
oracle基於cbo成本計算方式說明
2016-11-02
Oracle
css 選擇器優先順序的計算過程
2016-03-17
CSS
基於Vue元件化的日期聯動選擇器
2018-11-28
Vue元件化
CSS 選擇器效能優化
2019-01-14
CSS優化
Spark SQL 效能優化再進一步 CBO 基於代價的優化
2018-09-26
SparkSQL優化
基於 iView 的樹選擇器元件
2018-05-04
View元件
Oracle優化器(RBO與CBO)
2013-11-23
Oracle優化
Oracle優化器的RBO和CBO方式
2011-07-09
Oracle優化
Oracle約束Constraint對於CBO優化器的作用
2013-12-22
OracleAI優化
Oracle的優化器的RBO和CBO方式
2009-03-13
Oracle優化
基於 Flutter 的CityPickers 城市選擇器
2019-02-13
Flutter
袋鼠雲數棧基於CBO在Spark SQL優化上的探索
2022-06-10
SparkSQL優化
oracle實驗記錄 (oracle單表選擇率與基數計算(1))
2009-09-09
Oracle
oracle實驗記錄 (oracle單表選擇率與基數計算(2))
2009-09-12
Oracle
oracle實驗記錄 (oracle單表選擇率與基數計算(3))
2009-09-14
Oracle
選擇優化選項的方案
2008-12-22
優化
【效能優化】CBO優化器兩個內建的假設
2010-08-26
優化
Oracle的優化器：RBO/CBO，RULE/CHOOSE/FIRST_ROWS/ALL_ROWS 名詞解釋
2011-08-17
Oracle優化
Oracle效能優化方法論的發展之一：基於區域性命中率分析的效能優化方法
2018-10-12
Oracle優化
IT優化級別的選擇
2011-06-30
優化
前端效能JQuery篇之選擇器優化
2017-11-10
前端jQuery優化
簡單介紹Oracle的RBO/CBO優化器
2010-12-07
Oracle優化

基於CBO優化器謂詞選擇率的計算方法

相關文章