基于Oracle的SQL优化--学习（十二）

 自适应游标共享

    绑定变量窥探的副作用就在于，使用了绑定变量的目标 SQL 只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合于当前的情形．在 Oracle 10g及其后续的版本中， Oracle 会自动收集直方图统计信息，这意味着与之前的版本相比，在Oracle 10g 及其后续的版本中 Orade 有更大的概率会知道目标列实际数据的分布情况，也就是说绑定变量窥探的副作用将会更加明显。

    自适应游标共享可以让使用了绑定变量的目标 SQL 在启用了绑定变量窥探的前提条件下，不再只沿用之前硬解析时所产生的解析树和执行计划，也就是说自适应游标共享可以在启用了绑定变量窥探的前提条件下，让目标 SQL 在其可能的多个执行计划之间“自适应”地做出选择，而不再像之前那样必须得刻板地沿用该 SQL 硬解析时所产生的解析树和执行计划。

     Oracle 只需要在它认为目标 SQL 的执行计划可能发生变化时，触发该 SQL 再做一次硬解析就好了。因为一旦触发了硬解析这个动作， Oracle 就会将目标 SQL 重新再解析一遍，其中就包括对该 SQL 再做一次绑定变量窥探。显然，再做一次绑定变量窥探后所对应的执行计划就是当前情形下 CBO 认为的最优执行计划，这个执行计划很可能和该 SQL 硬解析时所产生的执行计划不一样了。也就是说，一个简单的适时触发目标 SQL 再做一次硬解析的动作就在一定程度上缓解了绑定变量窥探所带来的副作用。

    Oracle 会根据执行目标 SQL 时所对应的 runtime 统计信息（比如所耗费的逻辑读和 CPU 时间，对应结果集的行数等）的变化，以及当前传入的绑定变量输入值所在的谓词条件的可选择率，来综合判断是否需要触发目标 SQL 的硬解析动作。对于这一点，我们会在阐述自适应游标共享的整体执行流程时详细说明。

    自适应游标共享要做的第一件事就是所谓的扩展游标共享（ Extended Cursor Sharing ) ，而扩展游标共享做的主要事情就是将目标 SQL 所对应的 Child Cursor 标记为 Bind Sensitive 。所谓“ Bind Sensitive " ，就是指 oracle 觉得某个含绑定变量的目标 SQL 的执行计划可能会随着所传入的绑定变量输入值的变化而变化。当满足如下三个条件时，目标 SQL 所对应的 Child Cursor 就会被 oracle 标记为 Bind Sensitive 。

    （1）启用了绑定变量窥探。

    （2）该 SQL 使用了绑定变量（不管是该 SQL 自带的绑定变量，还是开启常规游标共享后系统产生的绑定变量）。 

    （3） 该 SQL 使用的是不安全的谓词条件（例如范围查询，目标列上有直方图统计信息的等值查询等）。
    自适应游标共享要做的第二件事情就是将目标 SQL 所对应的 Child Cursor 标记为 Bind Aware 。所谓“ Bind Aware " ，就是指 Oracle已经确定某个含绑定变量的目标 SQL 的执行计划会随着所传入的绑定变量输入值的变化而变化。当满足如下两个条件时，目标 SQL 所对应的 Child Curso ：就会被Oracle 标记为 Bind Aware . 

    （1）该 SQL 所对应的 Child Cursor 在之前已经被标记为 Bind Sensitive 。

    （2）该 SQL 在接下来连续两次执行时，所对应的 runtime 统计信息与该 SQL 之前硬解析时所对应的 runtime统计信息存在较大的差异。

    对于自适应游标共享而言，V$SQL中的列IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE分别用来表示ChildCursor是否是BindSensitive、BindAware和共享的。这里“共享”的含义是指存储在该ChildCursor中的解析树和执行计划是否能被重用，一个非共享的ChildCursor中存储的执行计划和解析树是不能被重用的，并且该ChildCursor也会在第一时间被ageout出SharedPool。
    与自适应游标共享相关的有两个重要视图，分别是V$SQL_CS_STATISTICS和V$SQL_CS_SELECTIVITY。
    V$SQL_CS_STATISTICS用于显示指定ChildCursor中存储的runtime统汁信息。V$SQL_CS_SELECTIVITY用于显示指定的、已经被标记为BindAware的ChildCursor中存储的含绑定变量的谓词条件所对应的可选择率的范围。当一个被标记为BindAware的ChildCursor所对应的目标SQL再次被执行时，Oracle就会比较当前传入的绑定变量值所在的谓词条件的可选择率，以及该SQL之前硬解析时同名谓词条件在V$SQL_CS_SELECTIVITY中对应的可选择率的范围，并以此来决定此时的执行是用硬解析还是软解析/软软解析。

    Oracle数据库中自适应游标共享的整体执行流程为如下所示。
    (1)当目标SQL第一次被执行时，Oracle会用硬解析，同时Oracle会根据一系列条件（如该SQL有没有使用绑定变量，参数CURSOR_SHAR〖NG的值是多少，绑定变量所在的列是否有直方图，该SQL的where条件是等值查询还是范围查询等）来判断是否将该SQL所对应的ChildCursor标记为BindSensitive。对于标记为BindSensitive的ChildCursor,Oracle会把执行该SQL时所对应的runtime统计信息额外地存储在该SQL所对应的ChildCursor中。
    (2)当目标SQL第二次被执行时，Oracle会用软解析，并且会重用该SQL第一次执行时所产生的ChildCursor中存储的解析树和执行计划。
    (3)当目标SQL第三次被执行时，如果该SQL所对应的ChildCursor已经被标记成了BindSensitive，同时Oracle在第二次和第三次执行该SQL时所记录的runtime统计信息和该SQL第一次硬解析时所记录的runtime统计信息均存在较大差异，则该SQL在第三次被执行时就会使用硬解析，Oracle此时会产生一个新的ChildCursor(这个新的ChildCursor会挂在原ParentCursor下），并且Oracle会把这个新的ChildCursor标记为BindAware。

    (4)对于标记为BindAware的ChildCursor所对应的目标SQL，当该SQL再次被执行时，Oracle就会根据当前传入的绑定变量值所对应的谓词条件的可选择率，来决定此时是用硬解析还是用软解析/软软解析。这里的判断原则是，如果当前传入的绑定变量值所在的谓词条件的可选择率处于该SQL之前硬解析时同名谓词条件在V$SQL_CS_STATISTICS中记录的可选择率的范围之内，则此时Oracle就会使用软解析/软软解析，并重用相关ChildCursor中存储的解析树和执行计划，反之则是硬解析。
    另外，如果是硬解析，且该次硬解析所产生的执行计划和原有ChildCursor中存储的执行计划相同，则Oracle此时除了会新生成一个ChildCursor之外，还会把存储相同执行计划的原有ChildCursor标记为非共享(原有ChildCursor在V$SQL中对应记录的列1S_SHAREABLE的值也会从Y变为N)，在把原有ChildCursor标记为非共享的同时，Oracle还会对新生成的ChildCursor执行一个Cursor合并的过程（这里Cursor合并的含义是指Oracle会合并存储相同执行计划的原有Child Cursor和新生成的Child Cursor）；如果是软解析/软软解析，则Oracle会重用相关Child Cursor中存储的解析树和执行计划。