基於CBO最佳化器謂詞選擇率的計算方法
選擇率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" 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(:B1) AND "MONTH_NO" 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(:B1) AND "MONTH_NO" 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(: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/26015009/viewspace-1071186/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於CBO優化器謂詞選擇率的計算方法優化
- 【cbo計算公式】Join 選擇率(六)公式
- 【cbo計算公式】單表選擇率(二)公式
- CBO中基數（cardinality）、可選擇率（selectivity）的計算公式公式
- 【cbo計算公式】Frequenccy 直方圖選擇率(三)公式直方圖
- 【cbo計算公式】Height-balance直方圖選擇率(四)公式直方圖
- cbo心得(選擇率,基數,直方圖)(一)直方圖
- cbo心得(選擇率,基數,直方圖)(二)直方圖
- cbo心得(選擇率,基數,直方圖)(三)直方圖
- cbo心得(選擇率,基數,直方圖)(四)直方圖
- cbo心得(選擇率,基數,直方圖)(五)直方圖
- CBO Cost Formulas基於成本優化器的成本計算公式大全ORM優化公式
- 執行計劃-4：謂詞的選擇時機與使用細節
- oracle基於cbo成本計算方式說明Oracle
- 【GreatSQL最佳化器-02】索引和Sargable謂詞SQL索引
- 基於 iView 的樹選擇器元件View元件
- 基於 Flutter 的CityPickers 城市選擇器Flutter
- oracle實驗記錄 (oracle單表選擇率與基數計算(1))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(2))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(3))Oracle
- CSS 選擇器權重計算規則CSS
- PostgreSQL DBA(12) - 統計資訊在計算選擇率上的應用#2SQL
- PostgreSQL DBA(11) - 統計資訊在計算選擇率上的應用#1SQL
- 基於Vue元件化的日期聯動選擇器Vue元件化
- CBO的查詢轉換（謂詞推入與子查詢展開（Subquery Unnesting））
- python 計算txt文字詞頻率Python
- 【cbo計算公式】CBO基本概念(一)公式
- CBO成本計算初探
- 謂詞下推：計算和儲存分開進行分析是低效的？
- 怎麼更好的選擇網站關鍵詞最佳化方式網站
- css 選擇器優先順序的計算過程CSS
- 【CBO】基於成本優化器的基本原則（二）優化
- 【CBO】基於成本優化器的基本原則（一）優化
- Oracle最佳化器（RBO與CBO）Oracle
- css 選擇器基礎CSS
- CSS基礎選擇器CSS
- Oracle的最佳化器的RBO和CBO方式Oracle
- 關於jQuery中的選擇器jQuery