SQL语句执行计划变化的原因分析与应对策略-For Oracle 10g.pptVIP

SQL语句执行计划变化的原因分析与应对策略 — For ORACLE 10g 2012.01 一、研究目的 　　目前，ORACLE 10g以其高效的数据处理能力仍然是国内外业务系统后台数据库的首选之一。因为对诸如金融行业等对实时联机业务的处理能力非常重视的业务系统来说，数据库的数据处理能力是最关键的因素之一。以银行业为例，当今国内银行核心系统在高峰时期的交易量约达到每分钟3000笔左右，而在数据库中每笔交易的平均响应时间则需控制在0.2秒以内，从这些数字可以看出银行核心系统对数据处理能力的要求之高。 　　因此，使用ORACLE 10g作为后台数据库的业务系统往往会对SQL的执行效率相当关注，从而另SQL语句的效率优化成为了每个数据库系统管理人员的重要任务之一。 　　从而，通过对SQL执行效率的分析，掌握SQL执行计划改变的根本原因并对此采取对策是数据库管理员维护好数据库，保证业务系统稳定运行的关键之一。 二、数据库应用的SQL效率分析 　　对于数据库应用而言，一个SQL语句传送到数据库引擎，需要经过几个主要的处理才能最终完成，它是解析，执行和获取。获取操作会由于数据逻辑的不同而不同，也没有特别多的优化方法，但对于前两个处理而言，是否可以高效的解析，解析之后是否能得到快速执行就决定了应用程序是否可以被高效处理 。 二、数据库应用的SQL效率分析－续 　以下是两种通常的优化处理： 解析的优化－使用嵌入变量减少硬解析 　　 　　硬解析在SQL语句第一次被数据库解析时发生，它包含语法，权限，对象属性等进行检查，然后生成最快的执行计划。通常硬解析是大量消耗CPU的操作，应当尽可能的避免。嵌入变量的使用将不同的常量写入嵌入变量缓冲中，在SQL语句的多次执行中保持哈希值不变，避免了重新的解析。 执行的优化－基于成本的CBO经过精确计算得到最低成本的执行计划 　　 　　对于优化器而言，它在拿到一个SQL的哈希值并结合访问变量，通过对对象属性，索引特性，分区等因素（统计信息）的分析确定各种可能的访问路径，排序方式或关联方式等，并最终形成一个最小执行成本的执行计划。 二、数据库应用的SQL效率分析－续 　　通常而言，只需按照上文中的两个方面来做，应用的效果会非常的好，然而在实际进行数据库SQL优化的过程中，当这两个方面的优化措施相互作用时，却可能产生一些意想不到的情景—产生多版本的执行计划，在一些高访问负荷，数据量巨大且分布复杂的业务系统中甚至可能引发突然性的性能下降。 因此，为使业务系统长期稳定运行，以上问题是必须避免的。换言之，找到多版本执行计划的产生原因并避免由于嵌入变量的处理而导致的执行计划非正常改变将成为维护好业务系统的关键因素。 三、多版本执行计划的产生原因 多版本执行计划产生的关键－硬解析 　　由于执行计划的生成包含在硬解析中，如果一个SQL在第一次解析后再没有硬解析发生，那么可以确定，它的执行计划一定也是不变的，所以减少触发硬解析的操作也可以大大减少不同执行计划产生的可能性。 数据库中触发SQL语句硬解析的因素 　　首次SQL访问 　　与SQL相关对象的DDL操作 　　与SQL相关对象的权限变化 　　与SQL相关对象的统计信息变化 　　会话或数据库级的优化器参数改变 　　游标过期或共享池刷新 三、多版本执行计划的产生原因－续 多版本执行计划产生的主要原因 由此可见，由于统计信息与优化器设置是执行计划的决定性因素，故造成多版本执行计划产生的根本原因是由于统计信息或优化器参数设置的改变，导致在下次触发硬解析时生成了与之前相比不同的执行计划。 四、嵌入变量处理对执行计划的影响 　　显然，正常情况下执行计划的改变对业务系统的运行是有利无害的，比如由于业务量的不断增长导致后台数据库记录数的增多，在重新进行相关表的表分析后触发数据库更新统计信息从而造成在下一次硬解析时执行计划的改变（此时更新的执行计划对于大幅增加记录数将使得SQL的执行效率更佳）。 　　然而，在使用嵌入式变量时，由于BIND－PEEKING的特性，可能在极端情况下会生成非常不理想的执行计划从而对业务系统产生影响。 四、嵌入变量处理对执行计划的影响－续 什么是BIND－PEEKING 　　当优化器遇到用户定义的嵌入变量时，它会做进一步的处理尝试获取更精确的执行计划，这个特性称为BIND－PEEKING。当该特性打开时，对于包含嵌入变量的SQL语句，优化器进行执行计划的评估和计算时会使用嵌入变量的值与相应的统计信息进