绑定变量与执行计划的“陷阱”

2019独角兽企业重金招聘Python工程师标准>>>

补充：

SIMILAR废弃声明：

根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项，原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了，使用SIMILAR选项可能引发额外的version_count过多（子游标过多）或cursor pin s on X等待事件。在11g中Oracle官方已经不再推荐使用SIMILAR选项，对于已经升级到11g的仍在使用cursor_sharing=’SIMILAR’的用户，建议尽早修改应用做到绑定变量，这样可以最稳妥的将cusror_sharing设置为EXACT，对于无法做到绑定变量的应用那么FORCE还会是一个和好的折中选择。在版本12g中我们将不再看到SIMILAR选项。http://www.askmaclean.com/archives/cursor_sharing-similar%E5%B0%86%E8%A2%AB%E5%BA%9F%E5%BC%83.html

    正常情况下，Oracle会对第一次执行的SQL语句进行硬解析，之后的这个SQL语句将重用第一次解析时的执行计划，但是这里面有一个潜在的执行风险。当我们给绑定变量的两个值返回的结果集差异非常大时（数据倾斜），（比如：x=1只有一条结果返回，而x=99有将近50000条记录返回），对于这两个值应该选择不同的执行计划，即x=1应该选择索引，而x=99应该选择全表扫描。
 

    但是，不幸的是，CBO模式下，这个绑定变量的SQL执行了2次解析，第一次执行了硬解析，第二次执行了软解析。第一次用x=1的SQL语句进行到了硬解析，确立的使用索引的执行计划；而第二次使用x=99的SQL语句进行了软解析，由于共享池中已经有了这个绑定变量SQL的执行计划，因此第二次SQL语句的执行选择使用索引的执行计划。事实上，我们知道x=99时，选择全表扫描的效率比选择索引要更高。
 

这里需要注意，使用绑定变量的前提是，这一定是一个OLTP系统，只有OLTP系统需要绑定变量，才能有效降低对成千上万SQL语句做解析的代价。而数据倾斜通常发生在OLAP系统中，在海量数据场景下（通常是OLTP系统数据量的几十或上百倍），数据倾斜是经常发生的，因此OLAP系统不建议对SQL进行绑定变量。
 

    在现实生产环境中，很多数据库兼有OLTP和OLAP两种数据特点，比如：白天数据量生成速度快，并发用户又很多；晚上数据规模已经已经接近千万级，并发用户量降低。这时候DBA只能根据实际数据情况来选择一种符合当时业务量要求的执行计划。所以，绑定变量不是万能的，存在一个潜在的风险。
 

    Oracle给出了cursor_sharing的三个参数值。默认EXACT是最优的，但是它的前提是，应用系统要有严格的绑定变量要求，来达到最优的SQL重用。即只有高效的绑定变量，EXACT才是最优的。

    SIMILAR、FORCE是ORACLE提供的降低系统大量SQL解析的补救方式，但是它会不加区分或略加区分的对谓词强制绑定变量，可能导致SQL执行计划的错误。

 

Cursor_sharing - SQL重用参数：

 

值1：EXACT：SQL语句必须完全一样，才可以共享游标，在共享池中被重用，否则将作为新SQL语句进行一次硬解析。在OLTP系统中，如果绑定变量的效果不太好，EXACT将会增加Oracle对SQL的硬解析，消耗更多的系统资源。

值2：SIMILAR：Oracle会将SQL语句中的谓词条件使用同一的“SYS_B_0”替代，使两条谓词不同的SQL看起来像一条SQL，但在共享池中仍然是按照2条SQL来存放执行计划。如果CBO发现被绑定变量的谓词还有其他执行计划可以选择时，如果谓词条件的值，与共享池中谓词一样，则重用之前的SQL语句；如果谓词条件的值，与共享池中的谓词不同，则创建一个子游标，进行一次硬解析，并提交共享池。

值3：FORCE：Oracle会将SQL语句中的谓词条件使用同一的“SYS_B_0”替代，只做一次硬解析，之后所有的SQL东重用第一个SQL的执行计划。也许，仅仅是也许，在OLTP系统中可能会带来性能的提升。因为OLTP系统中SQL的执行计划基本相同，不会因为谓词的条件不同而改变执行计划。

对于OLTP系统，如果绑定变量情况不好，可以通过设置这个参数来缓解问题。如果SQL的执行计划几乎趋向一致，可以考虑FORCE；如果SQL的执行计划可能因为谓词条件值的不同而改变，那么应该考虑SIMILAR。

在OLAP系统，这个参数应该设置为EXACT，并且不应该使用绑定变量，因为在OLAP系统中，SQL硬解析花费的代价几乎可以忽略不计，而CBO通过硬解析获得执行计划才是OLAP数据库最需要关注的。

摘自《Oracle10g 性能分析与优化思路》 

转载于:https://my.oschina.net/blacklands/blog/864027