何时Oracle使用绑定变量性能反而更差

文章作者 100test 发表时间 2007:03:14 13:59:11
来源 100Test.Com百考试题网

当我在做培训时，在解释绑定变量的好处时，大家都比较容易理解。但是，对于并不是任何时候绑定变量都是最优的。这一点很多人不是和理解。下面就讨论一下在什么时候会出现绑定变量会使性能变差。

扫描成本和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> 0delete from plan_table. 0 rows 0deleted. SQL> SQL> explain plan for 0select /* index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V. Explained. SQL> 0select 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 . Query Plan_Table ----------------------------------------------------- SELECT STATEMENT Cost=113 TABLE ACCESS BY INDEX ROWID T_PEEKING INDEX RANGE SCAN T_PEEKING_IDX1 SQL>

再看全表扫描的代价是多少：

SQL> 0delete from plan_table. 3 rows 0deleted. SQL> SQL> explain plan for 0select /* full(a)*/ * from T_PEEKING a where b = :V. Explained. SQL> SQL> 0select 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 . Query Plan_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> 0delete from plan_table. 2 rows 0deleted. SQL> SQL> explain plan for 0select * from T_PEEKING a where b = :V. Explained. SQL> SQL> 0select 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. Query Plan_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> 0delete from plan_table. 2 rows 0deleted. SQL> SQL> explain plan for 0select * from T_PEEKING a where b = :V. Explained. SQL> SQL> 0select 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. Query Plan_Table --------------------------------------------------------- SELECT STATEMENT Cost=75 TABLE ACCESS BY INDEX ROWID T_PEEKING INDEX RANGE SCAN T_PEEKING_IDX1