有时SQL语句在没有明显原因的情况下，突然变得执行缓慢,SQL语句所引用的对象的统计信息，对象数据，SQL都没有改变。
SQL语句的游标由于某些原因生成了新的执行计划。出现这种情况的原因至少为以下一种:
.数据库重新启动
.由于任何原因游标没有被使用，并且因为LRU算法被aged out
.SQL语句所引用的任何对象的统计信息发生改变
.SQL语句所引用的任何对象的结构发生改变
.对SQL语句所引用的对象授予/回收权限
如果游标被pinned(正被使用)，那么清空共享池也不会将该游标删除。
可能造成SQL语句突然执行缓慢的原因一个可能主要的原因是使用了oracle 9i所引入的绑定变量窥视。使用这个功能，查询优化器将会在第一次调用一个游标时窥视用户定义绑定变量的值。这个功能能让优化器像使用literal值一样对待绑定变量的值来计算任何where子句条件的选择率。在后续调用游标时，不会执行绑定变量窥视，并且游标是共享,基于标准游标共享的标准，即使后续调用游标时使用不同的绑定变量值，还是会使用共享标。换句话说，这个游标被解析一次被多次使用。如果在SQL第一次被解析时所使用的绑定变量值不能代表大多数数据时，那么对于不同的绑定变量来说第一次生成的执行计划就很有可能不是最优的。
在SQL语句中使用绑定变量，并且游标是共享的，对不同的调用都将使用相同的执行计划。如果不同的调用使用不同的执行计划更有效，那么在SQL语句中使用绑定变量就不合适。
另外，绑定变量窥视已经被熟知在RAC的不同节点中会导致生成不同的执行计划，这是因为每个节点都有属于它自己的共享池。尽管相同的SQL，数据与统计信息，在SQL语句使用不同的绑定变量第一次在每个节点被解析时，对于每个节点将会选择不同的执行计划。
隐含参数_optim_peek_user_binds用来控制绑定变量窥视这个功能是否启用，它的缺省值是true。虽然禁用绑定变量窥视可以得到一个稳定的执行计划，但必须要了解对于所有绑定变量使用一种稳定的执行计划是否是最佳的选择。比如一个表有10000行记录并且col1列存在索引。
SELECT
FROM tablex
WHERE col1 BETWEEN :bind1 AND :bind2;
如果执行这个SQL，使用值123与124来从10000行记录中检索2行记录，那么使用索引绝对是最明智的选择。然而，如果使用绑定变量值123与9999来执行相同的SQL，那么查询将要检索表中绝大多数记录并且选择全表扫描应该是最合适的，但优化器不能意识到这一点，不会因此改变执行计划。
解决方法
要解决因绑定变量窥视而造成的SQL变慢的问题最好的方法是使用Oracle 11g引入的自适就游标共享，这个技术允许优化器对于不同的绑定变量值使用不同的执行计划。如果自适应游标不可以使用，一种可能的解决方法是修改应用程序并且对于上面的语句有两个单独的
模块/部分来表示，但这种改变(使用hint来修改)将会生成你预期的执行计划。因此编码绑定变量或可以使用cursor_sharing设置为similar或force是适合的，但必须要认识到绑定变量窥视基于在硬解析时提供给优化器的一组绑定变量值而生成非你预期的执行计划。使用hint与编码应用程序来允许使用合适版本的SQL或使用literal值来获得更优的执行计划。
为了禁用绑定变量窥视将_optim_peek_user_binds在spfile/pfile或会话中设置为false:
alter session set “_OPTIM_PEEK_USER_BINDS”=FALSE;
下面通过一个例子来理解绑定变量窥视
SQL> create table t1(t_id number(20),t_meal varchar2(20));
Table created.
SQL>begin
for i in 1 .. 79998 loop
insert into t1 values(i,'Mansaf');
end loop;
insert into t1 values(79999,'Kabab');
insert into t1 values(80000,'Pasta');
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> create index idx_t1 on t1(t_meal);
Index created.
SQL> exec dbms_stats.gather_table_stats
(ownname=>null,tabname=>'T1',estimate_percent=>100,method_opt=>'for all indexed columns
size auto',cascade=>true);
PL/SQL procedure successfully completed.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_sharing参数为exact，这意味着只要查询的literal值不同游标就不能共享
SQL> select count(*) from t1 where t_meal='Mansaf';
COUNT(*)
----------
79998
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 41wjq1qnk92wd, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
Plan hash value: 2101382132
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 79998 | 546K| 44 (5)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_MEAL"='Mansaf')
19 rows selected.
从上面的执计划可以看到执行计划使用了index fast full scan。
下面使用绑定变量来执行
SQL> var x varchar2(20)
SQL> exec :x:= 'Mansaf'
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where t_meal=:x;
COUNT(*)
----------
79998
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 42s7s1vzsrbwk, child number 0
-------------------------------------
select count(*) from t1 where t_meal=:x
Plan hash value: 2101382132
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 79998 | 546K| 44 (5)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_MEAL"=:X)
19 rows selected.
当使用绑定变量后语义相同的语句的SQL_ID发生了改变。执行计划是使用index fast full scan
SQL> exec :x:='Pasta';
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where t_meal=:x;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 42s7s1vzsrbwk, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
Plan hash value: 2101382132
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 1 | 7 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
19 rows selected.
当只检索一条记录时，因为查询语句相同，游标共享并且即使绑定变量值不同还是使用了第一次生成的执行计划，这时该执行计划对于这个绑定变量值来说不是最佳执行计划。
如果想要使游标失效并重新生成执行计划，有以下几种方法使游标失效。
1.执行alter system flush shared_pool;
2.删除或修改游标所引用对象的统计信息
3.对游标所引用的对象授予或回收相关权限
4.修改游标所引用对象
5.重启实例
6.使用dummy hint来改变语句的文本
这里使用dummy hint来改变语句的文本
SQL> select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID crggg37d7jmrg, child number 0
-------------------------------------
select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x
Plan hash value: 1970818898
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
19 rows selected.
从执行计划可以看到SQL_ID也发生了改变,确实重新生成了执行计划
