什麼時候使用繫結變數效能反而更差
掃描成本和OPTIMIZER_INDEX_COST_ADJ
我們知道，在CBO模式下，Oracle會計算各個訪問路徑的代價，採用最小代價的訪問路徑作為語句的執行計劃。而對於索引的訪問代價的計算，需要根據一個系統引數OPTIMIZER_INDEX_COST_ADJ來轉換為與全表掃描代價等價的一個值。這是什麼意思呢？我們先稍微解釋一下這個引數：OPTIMIZER_INDEX_COST_ADJ。它的值是一個百分比，預設是100，取值範圍是1～10000。當估算索引掃描代價時，會將索引的原始代價值乘以這個百分比，將換算後的值作為與全表掃描代價比較的值。也就是說，當這個值為100時，計算出的索引掃描代價就是它的原始代價：
COST_COM = COST_ORG * OPTIMIZER_INDEX_COST_ADJ/100
看以下例子：
SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000)); Table created. SQL>SQL> create index T_PEEKING_IDX1 on T_PEEKING(b); Index created. SQL> begin 2 for i in 1..1000 loop 3 insert into T_PEEKING values (i, 'A', i); 4 end loop; 5 6 insert into T_PEEKING values (1001, 'B', 1001); 7 insert into T_PEEKING values (1002, 'B', 1002); 8 insert into T_PEEKING values (1003, 'C', 1003); 9 10 commit; 11 end; 12 / PL/SQL procedure successfully completed. 注意，我們給索引欄位B插入的值中只有3個distinct值，記錄數是1003，它的集的勢很高（1003/3）=334。關於集的勢的計算，可以參考我的另外一篇文件。
SQL>SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns; Table analyzed. SQL>我們看下索引掃描的代價是多少：
SQL> show parameter OPTIMIZER_INDEX_COST_ADJ NAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_index_cost_adj integer 100 SQL> delete from plan_table; 0 rows deleted. SQL> SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V; Explained. SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '|| 2 object_name||' '||decode(id, 0, 'Cost='||position) "Query 3 Plan_Table" 4 from plan_table 5 start with id = 0 6 connect by prior id = parent_id 7 ; QueryPlan_Table--------------------------------------------------------------------------------SELECT STATEMENT Cost=113 TABLE ACCESS BY INDEX ROWID T_PEEKING INDEX RANGE SCAN T_PEEKING_IDX1 SQL>
再看全表掃描的代價是多少：
SQL> delete from plan_table; 3 rows deleted. SQL>SQL> explain plan for select /*+full(a)*/ * from T_PEEKING a where b = :V; Explained. SQL>SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '|| 2 object_name||' '||decode(id, 0, 'Cost='||position) "Query 3 Plan_Table" 4 from plan_table 5 start with id = 0 6 connect by prior id = parent_id 7 ; QueryPlan_Table--------------------------------------------------------------------------------SELECT STATEMENT Cost=75 TABLE ACCESS FULL T_PEEKING SQL>
這時，我們可以計算得出讓最佳化器使用索引（無提示強制）的OPTIMIZER_INDEX_COST_ADJ值應該 < ROUND(COST_FTS/COST_IDX*100) = ROUND(75/113*100) = 66，而大於66則會使用全表掃描：
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=67; System altered. SQL>SQL> delete from plan_table; 2 rows deleted. SQL>SQL> explain plan for select * from T_PEEKING a where b = :V; Explained. SQL>SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '|| 2 object_name||' '||decode(id, 0, 'Cost='||position) "Query 3 Plan_Table" 4 from plan_table 5 start with id = 0 6 connect by prior id = parent_id; QueryPlan_Table--------------------------------------------------------------------------------SELECT STATEMENT Cost=75 TABLE ACCESS FULL T_PEEKING SQL>SQL>SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66; System altered. SQL>SQL> delete from plan_table; 2 rows deleted. SQL>SQL> explain plan for select * from T_PEEKING a where b = :V; Explained. SQL>SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '|| 2 object_name||' '||decode(id, 0, 'Cost='||position) "Query 3 Plan_Table" 4 from plan_table 5 start with id = 0 6 connect by prior id = parent_id; QueryPlan_Table--------------------------------------------------------------------------------SELECT STATEMENT Cost=75 TABLE ACCESS BY INDEX ROWID T_PEEKING INDEX RANGE SCAN T_PEEKING_IDX1可以看出，在使用繫結變數時，引數OPTIMIZER_INDEX_COST_ADJ對於是否選擇索引會有重要的影響。
這裡我們暫且不討論索引掃描的原始成本是如何計算得出的。但是有一點很重要，在使用繫結變數時，計算出的成本是平均成本。在我們上面的例子中，欄位B的值只有3個："A"、"B"、"C"，其中A最多，1003行中有1000行。因此，在索引上掃描值為A記錄的成本為1000/1003 * 索引全掃描成本 ≈索引全掃描成本，我們看下它的成本是多少：
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100; System altered. SQL>SQL> delete from plan_table; 2 rows deleted. SQL>SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'A'; Explained. SQL>SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '|| 2 object_name||' '||decode(id, 0, 'Cost='||position) "Query 3 Plan_Table" 4 from plan_table 5 start with id = 0 6 connect by prior id = parent_id; QueryPlan_Table--------------------------------------------------------------------------------SELECT STATEMENT Cost=336 TABLE ACCESS BY INDEX ROWID T_PEEKING INDEX RANGE SCAN T_PEEKING_IDX1
可以看到，它的成本是336。因此索引的平均成本是(336 * 1003/1000) / 3 ≈ 113，也就是使用繫結變數使的成本。而掃描其它兩個值"B"和"A"時代價就非常小。
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100; System altered. SQL>SQL> delete from plan_table; 3 rows deleted. SQL>SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'B'; Explained. SQL>SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '|| 2 object_name||' '||decode(id, 0, 'Cost='||position) "Query 3 Plan_Table" 4 from plan_table 5 start with id = 0 6 connect by prior id = parent_id; QueryPlan_Table--------------------------------------------------------------------------------SELECT STATEMENT Cost=2 TABLE ACCESS BY INDEX ROWID T_PEEKING INDEX RANGE SCAN T_PEEKING_IDX1
因為計算的成本是平均成本（相對實際掃描某個值的成本，平均成本更接近全表掃描成本），因此在建立查詢計劃時，使用繫結變數將更加容易受到引數影響，特別是上面的這種情況（即索引欄位的集的勢非常高時）下，平均代價與實際掃描某個值代價相差非常遠。這種情況下，OPTIMIZER_INDEX_COST_ADJ對不使用繫結變數查詢影響就非常小（因為索引代價不是比全表掃描成本大很多就是小很多），不管掃描哪個值，不使用繫結變數將更加容易選擇到合理的查詢計劃。
繫結變數窺視
在瞭解了引數的作用後。再瞭解一個對查詢計劃，特別是使用繫結變數時會產生重大影響的特性：繫結變數窺視（Bind Variables Peeking）。
繫結變數窺視是9i以後的一個新特性。它使CBO最佳化器在計算訪問代價時，將繫結變數傳入的值考慮進去，從而計算出更合理的成本（否則，將會計算平均成本）。看下面例子：
SQL> conn sys/sys as sysdbaConnected.SQL>SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60; System altered. SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns; Table analyzed. SQL>SQL> set autot traceSQL>SQL> alter session set sql_trace = true; Session altered. SQL>SQL> var v char(1)SQL>SQL> exec :v := 'A'; PL/SQL procedure successfully completed. SQL>SQL> select * from T_PEEKING a where b = :V;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1002888/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- react中什麼使用定義變數，需要使用useRef，什麼時候直接定義即可？React變數
- 在繫結變數下使用outline變數
- 到底什麼時候使用mqMQ
- C++ 靜態變數什麼時候完成初始化C++變數
- 如何在對in操作使用變數繫結(轉)變數
- C++中什麼時候用move，什麼時候用forward？C++Forward
- 改變自己，學好linux，什麼時候都不晚Linux
- python中什麼時候使用自定義類Python
- golang什麼時候應該把方法繫結在struct的值上而不是指標上？GolangStruct指標
- MySQL什麼時候會使用內部臨時表?MySql
- 什麼時候需要自動化什麼時候用自動化?
- MySQL高階特性——繫結變數MySql變數
- [20180930]in list與繫結變數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- Vue select 繫結動態變數Vue變數
- 什麼時候釋出
- 什麼時候能解脫
- 為什麼網站使用CDN加速後，網站訪問速度反而變慢了？網站
- [20180930]in list與繫結變數個數.txt變數
- [20210120]in list與繫結變數個數.txt變數
- V$sql查詢未使用繫結變數的語句SQL變數
- 區分import 什麼時候使用 花括號{ }Import
- 什麼時候採用socket通訊，什麼時候採用http通訊HTTP
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- 繫結變數窺視測試案例變數
- 什麼時候該用vuex?Vue
- beego 什麼時候支援grpcGoRPC
- 新版什麼時候釋出？
- python什麼時候縮排Python
- 什麼是遷移學習？什麼時候使用遷移學習？遷移學習
- 什麼時候使用這些Nodejs框架？ Express，Koa，Nest，NodeJS框架Express
- 天行健課堂 | 什麼時候使用SWOT分析？
- 什麼時候都不晚！風變程式設計帶你時刻學習！程式設計
- [20200326]繫結變數抓取與NULL值.txt變數Null
- 如何用FGA得到繫結變數的值變數
- [20220414]toad與繫結變數peek.txt變數
- Oracle中如何查詢未使用繫結變數的SQL語句？Oracle變數SQL
- session是什麼時候建立的Session
- Python的類什麼時候用Python