分类： Oracle
2008-04-22 19:41:19
我的另外一篇关于Oracle数据库11g新的SQL性能分析器（SPA）工具的文章探讨了用于SQL语句捕获和性能分析统计的技术，这样，在环境改变之前，能更正确地预报环境改变对SQL语句性能的影响，虽然这对每个Oracle DBA而言都是一套有价值的工具包，但任何资深的DBA都会告诉你这只成功了一半。
当一个有效的（和极好的）SQL语句执行计划突然变得无效时会出现一个更隐蔽的问题：SQL语句会被重新解析，但实际上新执行计划的结果比原来的计划更糟糕，有下面几个原因会引发这一事件的发生：
◆游标老化最后从库缓存中被清除出去，因为这需要它的SQL语句被硬解析，游标可能碰巧使用了效率更低的执行计划。
◆当优化器统计重新计算游标的依赖对象时游标也可能是无效的，或当游标依赖的某个对象被修改了。
◆还有其他一些原因，不外乎改变SQL语句计划的稳定性：优化器版本的改变，初始化参数的修改，甚至为SQL语句创建新的SQL配置文件（Oracle10g后）。
SQL计划管理：简短的历史介绍
每个DBA面对的最大挑战是不但要能识别哪条SQL语句正糟糕地在执行，而且要为SQL语句找到最佳的执行计划，并使这些计划对所有语句都是可用的并能从中受益，这些解决方案的发展成为了一个有趣的历史。
第一阶段：存储概要（stored outlines）
Oracle 8i提供了一个捕获SQL语句执行计划并保存为存储概要的机制，一个存储概要实际上是一条由DBA或开发者优化过的存储在OUTLN方案中的SQL语句（有时是通过工具进行优化的，但大多数时候是靠不断的试验和对比得出的），当一条SQL语句被执行且它的执行计划与存储概要完全匹配，Oracle就使用存储概要替换使用的SQL语句，存储概要也可以分阶段执行，或限制到仅那些匹配的会话才能执行。
这提出了Oracle 8i术语：计划稳定性，它可能是有用的，也有一些确定，最严重的是存储概要计划随时间的推移变得越来越过时和陈旧了，因为数据分配或优化统计在不停改变，实际上存储概要倾向于强制一条语句使用一个效率可能相当低的计划，而一个新的效率更好的计划可能只需要简单的重新解析该语句即可。
第二阶段：SQL配置文件（SQL profiles）
Oracle数据库10g使用了一个叫做SQL配置文件的新方法弥补了存储概要的缺点，DBA可以使用SQL调整顾问（STA）或SQL访问顾问（SAA）来识别可以得到更好性能的SQL语句，这些语句可以保存在SQL调整集、一个AWR快照或保存在当前的库缓存中，一旦识别出调整候选者，这些顾问程序就开始分析捕获到的语句以期获得更好的性能，然后生成专用的语句扩展（就叫做SQL配置文件）并重写SQL语句以在执行期间获取更佳的性能。
与存储概要类似，一个SQL配置文件提供了使用更好的执行计划的能力（如果这个执行计划是可用的），SQL配置文件也可以象存储概要一样分阶段执行，或限制到对特定会话才能执行该SQL配置文件，但是大多数重要的改进超过了存储概要，至少有两方面：（a）自我调整的能力保障了SQL配置文件能提供最好的执行计划，（b）检查SQL配置文件不再有效的能力（因此ADDM建议需要生成一个新的SQL配置文件）。
第三阶段：SQL计划管理
Oracle11g实现了一个新特色，叫做SQL计划管理（SPM），它通过一个简单而优雅的方法克服了异常的SQL执行计划回退，一旦一个用户会话启用了自动捕获SQL计划基线，基于成本的优化器（CBO）记录该会话执行的每条SQL语句到SQL管理库（SMB），它存储了SQL语句文本，存储概要，绑定变量和它的兼容环境，它将这些作为一个SQL计划的基线。
因为这是该语句第一次执行，Oracle11g会评估存储的执行计划哪个是最佳的，在下次执行同样的SQL语句过程中，SQL计划管理程序程序就很清楚了，在该语句的第二次执行时，基于成本的优化器（CBO）会与当前存储在SMB中的执行计划进行比较，如果CBO发现了一个新的计划，首先会将该新计划添加到计划历史中，然后评估该新计划看其效率是否比当前SMB中的执行计划更好。
如果新的执行计划可以提升SQL语句的性能，SQL计划管理程序（SPM）就将该计划标记为该语句执行的最佳选择，当DBA没有手动修改初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES的默认值TRUE时，现在该语句执行过程中CBO会使用新的执行计划，换句话说，因为使用了新的计划而语句的实际性能倒退了的话，CBO将会从SMB中选择一个认可的成本更低的计划，SPM将在SMB中存储新的执行计划，因为它可能在进来成为最好的执行计划选择。
