何時Oracle使用綁定變量性能反而更差

2024-08-29 13:50:58

字體：大中小

來源：轉載

供稿：網友

當我在做培訓時，在解釋綁定變量的好處時，大家都比較輕易理解。但是，對于并不是任何時候綁定變量都是最優的。這一點很多人不是和理解。下面就討論一下在什么時候會出現綁定變量會使性能變差。掃描成本和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影響，非凡是上面的這種情況（即索引字段的集的勢非常高時）下，平均代價與實際掃描某個值代價相差非常遠。這種情況下，OPTIMIZER_INDEX_COST_ADJ對不使用綁定變量查詢影響就非常小（因為索引代價不是比全表掃描成本大很多就是小很多），不管掃描哪個值，不使用綁定變量將更加輕易選擇到合理的查詢計劃。綁定變量窺視在了解了參數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; 1000 rows selected. SQL>SQL> alter session set sql_trace = false; Session altered.

用Tkprof處理生成的trace文件。因為在存在綁定變量窺視時，autotrace或者explain plan可能不會顯示正確的查詢計劃，需要Tkprof來處理sql trace。

tkprof fuyuncat_ora_5352.trc aaa.txt

此時OPTIMIZER_INDEX_COST_ADJ是60，根據上面的結論，似乎查詢計劃應該選擇掃描索引。但是，這里給綁定變量賦了值"A"，這時，優化器會“窺視”到這個值，并且在計算掃描成本時按照這個值的成本來計算。因此，得出的查詢計劃是全表掃描，而不是掃描索引，靠Tkprof分析的結果：

select * from T_PEEKING a where b = :V call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 68 0.01 0.07 0 406 0 1000------- ------ -------- ---------- ---------- ---------- ---------- ----------total 70 0.01 0.08 0 406 0 1000 Misses in library cache during parse: 1Optimizer mode: CHOOSEParsing user id: SYS Rows Row Source Operation------- --------------------------------------------------- 1000 TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)

但是，綁定變量窺視對一條語句只會使用一次。就是說，在第一次解析語句時，將綁定變量值考慮進去計算成本生成查詢計劃。以后在執行該語句時都采用這個查詢計劃，而不再考慮以后綁定變量的值是什么了。

SQL> conn sys/sys as sysdbaConnected.SQL>SQL>SQL> set autot traceSQL>SQL> alter session set sql_trace = true; Session altered. SQL>SQL> var v char(1)SQL>SQL> exec :v := 'B'; PL/SQL procedure successfully completed. SQL>SQL> select * from T_PEEKING a where b = :V; 1000 rows selected. SQL>SQL> alter session set sql_trace = false; Session altered.

再用Tkprof分析生成的trace文件，看到盡管這里的值是"B"，選擇索引掃描會更優，但分析結果中查詢計劃還是使用全表掃描：

select * from T_PEEKING a where b = :V call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 0 340 0 2------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 0 340 0 2 Misses in library cache during parse: 0Optimizer mode: CHOOSEParsing user id: SYS Rows Row Source Operation------- --------------------------------------------------- 2 TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)

因此，這種情況下使用綁定變量也會導致無法選擇最優的查詢計劃。綜上所述，我們可以得出一個結論：在對建有索引的字段（包括字段集），且字段（集）的集的勢非常大時，使用綁定變量可能會導致查詢計劃錯誤，因而會使查詢效率非常低。

上一篇：在Linux系統下優化Oracle具體步驟

下一篇：Oracle 10g數據庫的安全性和身份管理