1、SQL语句执行计划变化的原因分析与应对策略 For ORACLE 10g,2012.01,一、研究目的,目前，ORACLE 10g以其高效的数据处理能力仍然是国内外业务系统后台数据库的首选之一。因为对诸如金融行业等对实时联机业务的处理能力非常重视的业务系统来说，数据库的数据处理能力是最关键的因素之一。以银行业为例，当今国内银行核心系统在高峰时期的交易量约达到每分钟3000笔左右，而在数据库中每笔交易的平均响应时间则需控制在0.2秒以内，从这些数字可以看出银行核心系统对数据处理能力的要求之高。因此，使用ORACLE 10g作为后台数据库的业务系统往往会对SQL的执行效率相当关注，从而另SQL语句
2、的效率优化成为了每个数据库系统管理人员的重要任务之一。从而，通过对SQL执行效率的分析，掌握SQL执行计划改变的根本原因并对此采取对策是数据库管理员维护好数据库，保证业务系统稳定运行的关键之一。,二、数据库应用的SQL效率分析,对于数据库应用而言，一个SQL语句传送到数据库引擎，需要经过几个主要的处理才能最终完成，它是解析，执行和获取。获取操作会由于数据逻辑的不同而不同，也没有特别多的优化方法，但对于前两个处理而言，是否可以高效的解析，解析之后是否能得到快速执行就决定了应用程序是否可以被高效处理 。,二、数据库应用的SQL效率分析续,以下是两种通常的优化处理：解析的优化使用嵌入变量减少硬解析硬
3、解析在SQL语句第一次被数据库解析时发生，它包含语法，权限，对象属性等进行检查，然后生成最快的执行计划。通常硬解析是大量消耗CPU的操作，应当尽可能的避免。嵌入变量的使用将不同的常量写入嵌入变量缓冲中，在SQL语句的多次执行中保持哈希值不变，避免了重新的解析。,执行的优化基于成本的CBO经过精确计算得到最低成本的执行计划对于优化器而言，它在拿到一个SQL的哈希值并结合访问变量，通过对对象属性，索引特性，分区等因素（统计信息）的分析确定各种可能的访问路径，排序方式或关联方式等，并最终形成一个最小执行成本的执行计划。,二、数据库应用的SQL效率分析续,通常而言，只需按照上文中的两个方面来做，应用的
4、效果会非常的好，然而在实际进行数据库SQL优化的过程中，当这两个方面的优化措施相互作用时，却可能产生一些意想不到的情景产生多版本的执行计划，在一些高访问负荷，数据量巨大且分布复杂的业务系统中甚至可能引发突然性的性能下降。因此，为使业务系统长期稳定运行，以上问题是必须避免的。换言之，找到多版本执行计划的产生原因并避免由于嵌入变量的处理而导致的执行计划非正常改变将成为维护好业务系统的关键因素。,三、多版本执行计划的产生原因,多版本执行计划产生的关键硬解析由于执行计划的生成包含在硬解析中，如果一个SQL在第一次解析后再没有硬解析发生，那么可以确定，它的执行计划一定也是不变的，所以减少触发硬解析的操作
5、也可以大大减少不同执行计划产生的可能性。数据库中触发SQL语句硬解析的因素首次SQL访问与SQL相关对象的DDL操作与SQL相关对象的权限变化与SQL相关对象的统计信息变化会话或数据库级的优化器参数改变游标过期或共享池刷新,三、多版本执行计划的产生原因续,多版本执行计划产生的主要原因由此可见，由于统计信息与优化器设置是执行计划的决定性因素，故造成多版本执行计划产生的根本原因是由于统计信息或优化器参数设置的改变，导致在下次触发硬解析时生成了与之前相比不同的执行计划。,四、嵌入变量处理对执行计划的影响,显然，正常情况下执行计划的改变对业务系统的运行是有利无害的，比如由于业务量的不断增长导致后台数据
6、库记录数的增多，在重新进行相关表的表分析后触发数据库更新统计信息从而造成在下一次硬解析时执行计划的改变（此时更新的执行计划对于大幅增加记录数将使得SQL的执行效率更佳）。然而，在使用嵌入式变量时，由于BINDPEEKING的特性，可能在极端情况下会生成非常不理想的执行计划从而对业务系统产生影响。,四、嵌入变量处理对执行计划的影响续,什么是BINDPEEKING当优化器遇到用户定义的嵌入变量时，它会做进一步的处理尝试获取更精确的执行计划，这个特性称为BINDPEEKING。当该特性打开时，对于包含嵌入变量的SQL语句，优化器进行执行计划的评估和计算时会使用嵌入变量的值与相应的统计信息进行对比，这
7、样的优势是可以用嵌入变量的值获得与其更精确匹配的执行计划。相反，如果该特性是关闭的，优化器就只能根据可能性的概率来选择不同的访问路径或方法，所产生的执行计划不如该特性打开时精确。,四、嵌入变量处理对执行计划的影响续,极端情况的发生对于优化器而言，在一次硬解析中分析所有的嵌入变量组合，并得到适合大多数的执行计划是非常困难的，在逻辑上也是不可能的，即使能够做到，这个代价对于硬解析而言也过高了。优化器可以做到的是通过 BINDPEEKING 技术产生与其更精确匹配的执行计划（最快的执行计划），但这个执行计划却未必适合后来访问的嵌入变量组合。所以就产生了一种极端的可能性：当某次硬解析被触发后，优化器要
8、根据第一次给出的嵌入变量组合来确定一个执行计划，当执行计划确定后，它对于这个变量而言是极端好，而此后，数据库将一直使用这个执行计划直到下一次硬解析被触发。但对于随后更多的嵌入变量组合而言它却可能成为极端差的执行计划，最终导致业务系统收到影响。这就是因嵌入变量的处理而造成执行计划非正常改变的根本原因。,五、应对策略,在设计阶段确定最优执行计划A.根据数据分布特性得到最优执行计划：在设计阶段各个数据表的数据特点都是可预知的，从这些特征出发确定一个执行计划就最为准确，同时也避免了频繁统计收集。这个执行计划需要与具体的数据无关，不求最快，但求最优，即适合大多数情况。从技术上而言，可以使用嵌入变量的均值
9、（而非理想或极端值）来创建执行计划。如果结合优化器指令（HINT）可以更精确的固定一个最优的执行计划，或者缩小BINDPEEKING所带来的执行计划变化的范围，减少BINDPEEKING带来的副作用。 如果很好的应用HINT和嵌入变量，BINDPEEKING就不会影响到应用的正常运行。B.适当使用常量与变量的组合：对于数据中的极端值（最大，最小等）可以使用常量，由数据库自行选定最快执行计划，对于其它值则使用HINT来固定一个折中的执行计划。,五、应对策略续,在应用发布后强制数据库使用最优的执行计划A.使用HINT完全固定执行计划：屏蔽统计信息变化等因素对执行计划的影响，这种方法对HINT的使用
10、要求较高，但仅影响单个应用，不会对整个系统产生其他副作用，适用于应用较为复杂的系统。B.不使用HINT，固定统计信息并关闭BIND-PEEKING特性：这样数据库会根据数据概率分布自动获取一个折中的执行计划，避免了某些极端情况发生。这种方法不需要修改应用代码，适用于已经发布无法修改的应用。但该改动是一个数据库全局性的改动，可能会对数据库产生负面影响。因此，该方法适用于应用比较单一、风险可控的系统，或修改应用存在困难的情况。,五、应对策略续,出现问题时的应急解决方案（以实际业务系统的例子说明）1.找出引起问题的关键SQL在Oracle数据库的执行计划历史。此例中经分析后发现，此关键SQL的执行计
11、划在近期频繁变化，共有6种不同的执行方式与之对应，我们把各自的执行次数逻辑读io等待CPU时间执行时间进行对比：PLAN_HASH_VALUE EXEC_SUM GETS_AVG IOWAIT_AVG CPU_AVG ELAP_AVG- - - - - -267901271 1116 4129936 104518 185493469 191939077 2667863071 24 353 277406 39489 3106412893608670 1170 888583 363355 57591747 909996443231199102 1402 508418 171859 22097784
12、 29853920 3318325031 538 46 29749 3678 346453928782998 2 2161 703458 184358 864604,五、应对策略续,出现问题时的应急解决方案续2.在历史执行计划中找到引起问题和相对最佳的执行计划。从前面的对比情况可以看出，此例中执行计划267901271是引起整体性能下降的原因，它的平均逻辑读平均CPU时间平均等待时间均8倍于其它的执行计划。而执行计划3231199102就整体而言，各方面的特征都较为平衡，并且经过了1402次的运行检验，可以认为是适合于绝大多数数据的最优的执行计划。相比之下对于其它执行计划虽然其逻辑读CPU等较
13、低，但执行次数极少，或平均值过低。在打开自动统计收集的情况下，这些执行计划可能仅适用于某些数据分布，应该不是最优的执行计划。,五、应对策略续,出现问题时的应急解决方案续3.找到各历史执行计划版本所对应的变量值。找到各历史执行计划对应的变量值，在此例中该SQL语句的变量值对应于不同的地区号：业务模块（SQL_ID） 执行计划 嵌入变量组合 执行计划gz3vqqgggar54 267901271 B1:3010 较差2667863071 B1:2604 偏小（运行次数少）2893608670 B1:0809 较好3231199102 B1:0912 好3318325031 B1:3500 偏小（运
14、行次数少）3928782998 B1:3400 偏小（运行次数少）,五、应对策略续,出现问题时的应急解决方案续4.通过变量值及相关信息判断问题根源，并重新生成执行计划加以固定。在此例中，我们通过了解该业务的数据分布特性可知，3010地区的业务与其它区域差异较大 数据量非常小，而相对比较好的执行计划所对应的地区的业务较为接近。由此可以判断该问题的根本原因应该是由BIND PEEKING特性所引起的。最后利用相对最优的执行计划通过HINT加以固定，并重新触发硬解析使之被使用。,六、总结,随着各类业务系统的飞速发展，其数据量和数据复杂性也不断增加，从而引起的各类由于SQL执行效率不佳所导致的性能问题也日趋严重。对于数据库开发人员，必须掌握各类业务系统的数据特点。尽量的在开发阶段就避免今后产生SQL执行计划不佳的现象。对于数据库维护人员，要充分掌握SQL执行计划变化的各类原因，并能及时处理因执行计划变差所导致的各类性能问题，确保业务系统的正常运行。,谢 谢！,
