optimizer_index_cost_adj和optimizer_index_caching對CBO的影響
DB：10205
OS: HP UX B.11.11
目的：
測試optimizer_index_cost_adj和optimizer_index_caching對CBO生成執行計劃的影響，前者的取值範圍0-10000，預設為100；後者則為0-100，預設為0；
關於這兩個引數的資料比較少，只找到如下一些；
《oracle高效設計》大致記錄如下：optimizer_index_caching告訴資料庫buffer cache中索引塊的百分比，值越高則意味cache中的索引塊數越多，其相應的io cost越小，optimizer_index_cost_adj則相反；
由馮大輝等人翻譯的《oracle效能診斷藝術》則有著更詳細的描述，書中提供了兩個計算索引io代價公式
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
很直觀的描述了兩個引數對CBO的影響，且optimizer_index_cost_adj對CBO的影響非常大，而optimizer_index_caching則相對小很多
下面做試驗測試一下
設定三組測試案例，引數分別設定為
1 optimizer_index_cost_adj =100，optimizer_index_caching =0
2 optimizer_index_cost_adj =10，optimizer_index_caching =0
3 optimizer_index_cost_adj =10 ，optimizer_index_caching =100
建立測試表
SQL> create table tmp_t as select object_id,owner from dba_objects;
Table created.
SQL> select count(*) from tmp_t;
COUNT(*)
----------
41187
SQL> select count(*) from tmp_t where wner='PUBLIC';
COUNT(*)
----------
16036
SQL> create index tmp_index on tmp_t(owner);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TMP_T',estimate_percent => 100);
PL/SQL procedure successfully completed.
設定10053跟蹤
1
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
SQL> alter session set events '10053 trace name context forever, level 12';
Session altered.
SQL> set autotrace traceonly
SQL> select * from tmp_t where wner='PUBLIC';
16036 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1035233137
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16036 | 187K| 23 (14)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP_T | 16036 | 187K| 23 (14)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='PUBLIC')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1121 consistent gets
0 physical reads
0 redo size
354186 bytes sent via SQL*Net to client
12251 bytes received via SQL*Net from client
1071 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16036 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
跟蹤檔案
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats—使用的非工作量統計資訊
CPUSPEED: 199 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TMP_T Alias: TMP_T
#Rows: 41187 #Blks: 49 AvgRowLen: 12.00
Index Stats::
Index: TMP_INDEX Col#: 2
LVLS: 1 #LB: 52 #DK: 57 LB/K: 1.00 DB/K: 5.00 CLUF: 290.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#2): OWNER(VARCHAR2)
AvgLen: 8.00 NDV: 57 Nulls: 0 Density: 1.2140e-05
Histogram: Freq #Bkts: 57 UncompBkts: 41187 EndPtVals: 57
Table: TMP_T Alias: TMP_T
Card: Original: 41187 Rounded: 16036 Computed: 16036.00 Non Adjusted: 16036.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 23.42 Resp: 23.42 Degree: 0
Cost_io: 20.00 Cost_cpu: 9538541
Resp_io: 20.00 Resp_cpu: 9538541
Access Path: index (AllEqRange)
Index: TMP_INDEX
resc_io: 134.00 resc_cpu: 7239716
ix_sel: 0.38935 ix_sel_with_filters: 0.38935
Cost: 136.60 Resp: 136.60 Degree: 1
Best:: AccessPath: TableScan
Cost: 23.42 Degree: 1 Resp: 23.42 Card: 16036.00 Bytes: 0
當兩個引數設為預設值時，sql選擇了意料之中的全表掃描，依據上文提到的公式計算一下
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52 + 290) * 0.38935 ) * 1= 134.1
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
=(( 1 + 52 * 0.38935 ) * 1 + 290 * 0.38935 ) * 1 = 134.1
與CBO計算結果大致一樣
2
optimizer_index_cost_adj = 10
optimizer_index_caching = 0
將optimizer_index_cost_adj由100改為10，依據上述公式，indexScan的io cost應該為案例1中的10%
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever, level 12';
Session altered.
SQL> select * from tmp_t where wner='PUBLIC';
16036 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1018746618
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16036 | 187K| 14 (8)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_T | 16036 | 187K| 14 (8)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TMP_INDEX | 16036 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2192 consistent gets
0 physical reads
0 redo size
354186 bytes sent via SQL*Net to client
12251 bytes received via SQL*Net from client
1071 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16036 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
跟蹤檔案
Access Path: TableScan
Cost: 23.42 Resp: 23.42 Degree: 0
Cost_io: 20.00 Cost_cpu: 9538541
Resp_io: 20.00 Resp_cpu: 9538541
Access Path: index (AllEqRange)
Index: TMP_INDEX
resc_io: 134.00 resc_cpu: 7239716
ix_sel: 0.38935 ix_sel_with_filters: 0.38935
Cost: 13.66 Resp: 13.66 Degree: 1
Best:: AccessPath: IndexRange Index: TMP_INDEX
Cost: 13.66 Degree: 1 Resp: 13.66 Card: 16036.00 Bytes: 0
將引數代入公式
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52 + 290) * 0.38935 ) * 1/10= 13.4
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
=(( 1 + 52 * 0.38935 ) * 1 + 290 * 0.38935 ) * 1/10 = 13.4
實際結果和預料的一樣，indexScan的cost下降為原來的十分之一，此時低於tableScan，故CBO選擇了索引掃描
3
optimizer_index_cost_adj = 10
optimizer_index_caching = 100
Table Stats::
Table: TMP_T Alias: TMP_T
#Rows: 41187 #Blks: 49 AvgRowLen: 12.00
Index Stats::
Index: TMP_INDEX Col#: 2
LVLS: 1 #LB: 52 #DK: 57 LB/K: 1.00 DB/K: 5.00 CLUF: 290.00
…….
Access Path: TableScan
Cost: 23.42 Resp: 23.42 Degree: 0
Cost_io: 20.00 Cost_cpu: 9538541
Resp_io: 20.00 Resp_cpu: 9538541
Access Path: index (AllEqRange)
Index: TMP_INDEX
resc_io: 134.00 resc_cpu: 7239716
ix_sel: 0.38935 ix_sel_with_filters: 0.38935
Cost: 13.66 Resp: 13.66 Degree: 1
Best:: AccessPath: IndexRange Index: TMP_INDEX
Cost: 13.66 Degree: 1 Resp: 13.66 Card: 16036.00 Bytes: 0
選擇索引所用的cost根案例2的一樣，沒有發生變化
計算cost
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52 + 290) * 0.38935 ) * 1/10= 13.4
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
=(( 1 + 52 * 0.38935 ) * 0 + 290 * 0.38935 ) * 1/10 = 11.2
CBO在計算索引的io時，似乎更傾向於公式1，更改optimizer_index_caching對其影響微乎其微
其實更改這兩個引數唯一可能影響的是CBO生成執行計劃時到底選擇全表掃描還是索引掃描，並不能更改兩種訪問路徑的實際邏輯讀，因此使用時應該倍加小心才對，至少目前我還沒見過生產庫上的應用案例。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-736602/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 分支對程式碼效能的影響和優化優化
- 元明粉和純鹼對染色的影響VOM
- 海外代理IP地址對網站SEO的影響和作用網站
- ClubIntel：Y世代和Z世代對健身行業的影響Intel行業
- 聊聊虛擬化和容器對資料庫的影響資料庫
- GHA：loot boxes對兒童和年輕人的影響
- 語言對思維的影響
- Nologging對恢復的影響(一)
- unusable index對DML/QUERY的影響Index
- Nologging對恢復的影響(二)
- 網線的分類與對網速的影響 網線對網速影響大嗎？
- 浮動的盒子對img的影響
- Protein Agency：文化、年齡和技術對友誼的影響
- AI和機器學習對量化交易領域的影響AI機器學習
- 分析核親和性對高吞吐量的流的影響
- INDEX建立方式對SQL的影響IndexSQL
- 關於OPcache對Swoole影響的理解opcache
- 來電對播放音樂的影響
- python：super()對多繼承的影響Python繼承
- DB2 HADR對效能的影響DB2
- FAO：2023年災害對農業和糧食安全的影響
- Strategy Analytics：COVID-19對企業和物聯網的影響
- windows server許可權對tomcat的影響WindowsServerTomcat
- 虛擬記憶體對 OI 的影響記憶體
- VideaHealth：人工智慧對牙科的真正影響Idea人工智慧
- 網路延遲對事務的影響
- JVM 引數調整對 sortx 的影響JVM
- Mavrck：COVID-19對創作者的影響VR
- cluster factor對執行計劃的影響
- 淺談疫情對消費金融的影響
- namespace對axis解析xml請求的影響namespaceXML
- 終端環境對go程式的影響？Go
- margin為負值對佈局的影響
- Sailthru：Facebook醜聞對人們的影響AI
- 修改系統時間對oracle的影響Oracle
- 世界銀行：COVID-19對學校和教育的潛在影響
- WTTC：2019各國旅遊業對經濟的影響和趨勢
- 對11g和12c ocp有影響的重磅訊息！
- MySQL alter 新增列對dml影響MySql