知识点 | oracle中如何清理某条SQL的执行计划

Oracle在传统的OLTP(在线事务处理)类系统中，强烈推荐使用绑定变量，这样可以有效的减少硬解析从而增加系统的并发处理能力。甚至在有些老旧系统，由于在开始开发阶段缺乏认识没有使用到绑定变量，后期并发量增长且无法改造程序时，运维DBA还会不得已去设置cursor_sharing=force来强制使用系统的绑定变量（这是一个万不得已的方案，并不是最佳实践）。

虽然使用绑定变量给OLTP系统带来了巨大的好处，但也同时带来一些棘手的问题，最典型的就是由于SQL文本中包含绑定变量，优化器无法知道绑定变量代表的具体值，只能使用默认的可选择率，这就可能导致由于无法准确判断值的可选择率而造成选择错误的执行计划。Oracle在9i时代就有了针对这个问题的解决方案，即绑定变量窥探（bind peeking）特性。开启该特性的情况下，当遇到有绑定变量的SQL，在其第一次硬解析时，优化器会窥探真实的值从而准确判断可选择率（selectivity），最终选择正确的执行计划。可是该特性同时又引入另一个棘手的问题，因为在第一次硬解析之后就都是软/软软解析，所以也就不会再次窥探绑定变量的真实值，而如果该值所在字段本身数值比例就分布不均，就极可能导致性能问题（尤其是如果第一次窥探的值代表了少数情况，那问题就会更加严重），所以一直以来，虽然Oracle默认是开启这个特性的，但很多的客户生产环境最佳实践都将这个特性给关闭了。

直到Oracle 11g的时代，才推出了acs(adaptive_cursor_sharing)特性，配合bind peeking才算真正意义上解决了这个问题。不过也不够完美，因为acs特性本身也的确会增加额外的硬解析，且会导致child cursor增多，从而软解析扫描chain的时间变长，同时对shared pool空间需求也增加，且早期bug较多，即使Oracle默认也是开启这个特性的，很多客户生产环境也是将其关闭的。

在这种背景下，建议是当遇到在数据倾斜的列上使用绑定变量的情况，应该及时与开发沟通，能否在这类数据分布严重倾斜的列上不用绑定变量，若该列上的值很多，不用绑定变量可能导致大量的硬解析的话，还可在应用发出SQL前，先判断其传入的值，是否是非典型值，若是非典型值，使用非绑定变量的SQL；若是典型值，则使用绑定变量的语句。

如果是不能改应用的情况呢？我目前能想到的是要么牺牲非典型值的执行效率（防止非典型值先被窥探导致更严重的性能后果，可以按典型值的执行计划绑定）；要么是干脆尝试同时打开bind peeking和acs特性，实际测试验证能否解决问题同时不引起其他性能问题（如果是已经关闭这些特性的生产系统，开启还是要慎重测试后决定）。

首先确认bind_peeking和acs都是开启状态
–查询隐藏参数：

set linesize 333
col name for a35
col description for a66
col value for a30
SELECT   i.ksppinm name,  
   i.ksppdesc description,  
   CV.ksppstvl VALUE
FROM   sys.x$ksppi i, sys.x$ksppcv CV  
   WHERE   i.inst_id = USERENV ('Instance')  
   AND CV.inst_id = USERENV ('Instance')  
   AND i.indx = CV.indx  
   AND i.ksppinm LIKE '%&param%' 
ORDER BY   REPLACE (i.ksppinm, '_', '');  

--相关隐藏参数的默认值（表示bind_peeking和acs都是开启的）：
NAME                                DESCRIPTION                                                        VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_optim_peek_user_binds              enable peeking of user binds                                       TRUE
_optimizer_adaptive_cursor_sharing  optimizer adaptive cursor sharing                                  TRUE
_optimizer_extended_cursor_sharing  optimizer extended cursor sharing                                  UDO
_optimizer_extended_cursor_sharing_ optimizer extended cursor sharing for relational operators         SIMPLE
rel
复制

清理某条SQL的执行计划

--查询SQL的ADDRESS和HASH_VALUE
SQL> select sql_id, ADDRESS, HASH_VALUE from v$sqlarea where sql_id = '7mz2mhz0nq92n';

SQL_ID                     ADDRESS          HASH_VALUE
-------------------------- ---------------- ----------
7mz2mhz0nq92n              0000000087F34700 3242927188

--清理SQL的执行计划
SQL> exec sys.DBMS_SHARED_POOL.PURGE('0000000087F34700,3242927188','C');
复制

bind peeking和acs特性的关闭

–均为动态参数
–bind peeking（绑定变量窥探）
alter system set “_optim_peek_user_binds”=false;

–acs(adaptive cursor sharing)
alter system set “_optimizer_extended_cursor_sharing_rel”=NONE;
alter system set “_optimizer_extended_cursor_sharing”=NONE;
alter system set “_optimizer_adaptive_cursor_sharing”=false;

特别注意：如果bind peeking是关闭的，实际上acs也就不会起作用，比如只将_optim_peek_user_binds参数设置为false，不会用到acs特性，即使没有显示禁用掉acs对应的参数：

注意：在确认acs特性是否开启时，同时也要查询bind peek的设置情况。

【参考】
https://www.cnblogs.com/jyzhao/p/11415820.html