绑定变量窥测与执行计划

绑定变量窥测打乱执行计划[@more@] 开发人员发过来一句 sql 及其执行计划和统计信息，该 sql 执行相当频繁，而且每次的逻辑读非常的高，对数据库性能产生较大的影响，叫我分析原因。通过查看其执行计划，发现其效率相当低，优化器预估的值与执行过程中实际值相关距大。通过进一步分析发现是由于三个原因造成的：一是错误使用嵌套循环；二是创建复合索引时字段顺序不恰当；三是 sql 语句使用绑定变量造成的，原因就在于绑定变量窥测。本文通过实验来探讨一下绑定变量窥测对执行计划的影响。

版本信息：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

创建测试表和索引：

SQL> create table test

2 as select rownum id ,a.* from all_objects a;

表已创建。

SQL> ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (id);

表已更改。

收集测试表的统计信息：

SQL> edit

已写入 file afiedt.buf

1 BEGIN

2 dbms_stats.gather_table_stats(

3 ownname => user,

4 tabname => 'TEST',

5 estimate_percent => 100,

6 method_opt => 'for all columns size 1'

7 );

8* END;

SQL> /

PL/SQL 过程已成功完成。

查看测试表id字段值的分布情况：

SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM TEST;

COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)

---------- ----------------- ---------- ----------

11109 11109 1 11109

SQL> variable id number;

SQL> execute :id := 5;

PL/SQL 过程已成功完成。

SQL> select count(object_name) from test where id < :id;

COUNT(OBJECT_NAME)

------------------

4

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

select count(object_name) from test where id < :id

Plan hash value: 885655847

-------------------------------------

| Id | Operation | Name |

-------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 2 | INDEX RANGE SCAN| TEST_PK |

-------------------------------------

通过查看执行计划，发现此时的执行计划是高效的。继续进行如下测试：

SQL> execute :id := 10000;

PL/SQL 过程已成功完成。

SQL> select count(object_name) from test where id < :id;

COUNT(OBJECT_NAME)

------------------

9999

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

select count(object_name) from test where id < :id

Plan hash value: 885655847

-------------------------------------

| Id | Operation | Name |

-------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 2 | INDEX RANGE SCAN| TEST_PK |

-------------------------------------

发现这个时候还走索引，显得已经不合适了，执行计划显得效率低下。接下来反过来进行测试：

SQL> alter system flush shared_pool;

系统已更改。

SQL> execute :id := 10000;

PL/SQL 过程已成功完成。

SQL> select count(object_name) from test where id < :id;

COUNT(OBJECT_NAME)

------------------

9999

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

select count(object_name) from test where id < :id

Plan hash value: 1830331429

-----------------------------------------

| Id | Operation | Name |

-----------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 2 | INDEX FAST FULL SCAN| TEST_PK |

-----------------------------------------

这个时候执行计划走INDEX FAST FULL SCAN是合适的，接下来：

SQL> execute :id := 10;

PL/SQL 过程已成功完成。

SQL> select count(object_name) from test where id < :id;

COUNT(OBJECT_NAME)

------------------

9

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

select count(object_name) from test where id < :id

Plan hash value: 1830331429

-----------------------------------------

| Id | Operation | Name |

-----------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 2 | INDEX FAST FULL SCAN| TEST_PK |

-----------------------------------------

这个时候执行计划再走INDEX FAST FULL SCAN就不合适了，此时用INDEX RANGE SCAN更好。

可以通过

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic peeked_bin

ds'));

来查看执行计划是否使用了绑定变量窥测。例如：

SQL> execute :id := 10000;

PL/SQL 过程已成功完成。

SQL> select count(object_name) from test where id < :id;

COUNT(OBJECT_NAME)

------------------

9999

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic peeked_bin

ds'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

select count(object_name) from test where id < :id

Plan hash value: 1830331429

-----------------------------------------

| Id | Operation | Name |

-----------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 2 | INDEX FAST FULL SCAN| TEST_PK |

-----------------------------------------

Peeked Binds (identified by position):

--------------------------------------

1 - :ID (NUMBER): 10000

通过以上执行计划的显示，其使用了绑定变量窥测。

需要注意的是，只要游标在库缓存中且可以共享，不管其效率如何，就可以重用。

通过以上分析和实验可知，在10gR2中，如果使用了绑定变量，则在生成执行计划时只能碰运气。DBA能做的似乎不太多，只能根据每个系统的特点，权衡利弊得失，选择使用与不使用绑定变量，禁用与不禁用绑定变量窥测。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9523925/viewspace-1032580/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9523925/viewspace-1032580/