对于使用Oracle数据库的应用系统，有时会出现运行得好好的SQL，性能突然变差。特别是对于OLTP类型系统执行频繁的核心SQL，如果出现性能问题，通常会影响整个数据库的性能，进而影响整个系统的正常运行。对于个别的SQL，比如较少使用的查询报表之类的SQL，如果出现问题，通常只影响少部分功能模块，而不会影响整个系统。

　　那么应该怎么样保持SQL性能的稳定性？

　　SQL的性能变差，通常是在SQL语句重新进行了解析，解析时使用了错误的执行计划出现的。下列情况是SQL会重新解析的原因：

　　1. SQL语句没有使用绑定变量，这样SQL每次执行都要解析。
        2. SQL长时间没有执行，被刷出SHARED POOL，再次执行时需要重新解析。
        3. 在SQL引用的对象（表、视图等）上执行了DDL操作，甚至是结构发生了变化，比如建了一个索引。
        4. 对SQL引用的对象进行了权限更改。
        5. 重新分析（收集统计信息）了SQL引用的表和索引，或者表和索引统计信息被删除。
        6. 修改了与性能相关的部分参数。
        7. 刷新了共享池。
        8. 当然重启数据库也会使所有SQL全部重新解析。

　　SQL重新解析后，跟以前相比，性能突然变差，通常是下列原因：

　　1. 表和索引的优化统计信息被删除，或者重新收集后统计信息不准确。重新收集统计信息通常是由于收集策略（方法）不正确引起。比如对分区表使用analyze命令而不是用dbms_stats包、收集统计信息时采样比例过小等等。Oracle优化器严重依赖于统计信息，如果统计信息有问题，则很容易导致SQL不能使用正确的执行计划。

　　2. SQL绑定变量窥探(bind peeking)，同时绑定变量对应的列上有直方图；或者绑定变量的值变化范围过大、分区数据分布极不均匀：

　　1） 绑定变量的列上有直方图：

　　假如表orders存储所有的订单，state列有3种不同的值：0表示未处理，1表示处理成功完成，2表示处理失败。State列上有一个索引，表中绝大部分数据的state列为1，0和2占少数。有下面的SQL： 

　　这里:b1是变量，在大多数情况下这个值为0，则应该使用索引，但是如果SQL被重新解析，而第一次执行时应用传给变量b1值为1，则不会使用索引，采用全表扫描的方式来访问表。对于绑定变量的SQL，只在第一次执行时才会进行绑定变量窥探，并以此确定执行计划，该SQL后续执行时全部按这个执行计划。这样在后续执行时，b1变量传入的值为0的时候，仍然是第一次执行时产生的执行计划，即使用的是全表扫描，这样会导致性能很差。

　　2） 绑定变量的值变化范围过大：

　　同样假如orders表有一列created_date表示一笔订单的下单时间，orders表里面存储了最近1年的数据，有如下的SQL：

　　假如大多数情况下，应用传入的b1变量值为最近几天内的日期值，那么SQL使用的是created_date列上的索引，而如果b1变量值为5个月之前的一个值，那么就会使用全表扫描。与上面描述的直方图引起的问题一样，如果SQL第1次执行时传入的变量值引起的是全表扫描，那么将该SQL后续执行时都使用了全表扫描，从而影响了性能。

　　3） 分区数据量不均匀：

　　对于范围和列表分区，可能存在各个分区之间数据量极不均匀的情况下。比如分区表orders按地区area进行了分区，P1分区只有几千行，而P2分区有200万行数据。同时假如有一列product_id，其上有一个本地分区索引，有如下的SQL：

　　这条SQL由于有area条件，因此会使用分区排除。如果第1 次执行时应用传给b1变量的值正好落在P1分区上，很可能导致SQL采用全表扫描访问，如前面所描述的，导致SQL后续执行时全部使用了全表扫描。

　　3. 其他原因，比如表做了类似于MOVE操作之后，索引不可用，对索引进行了更改。当然这种情况是属于维护不当引起的问题，不在本文讨论的范围。

　　综上所述，SQL语句性能突然变差，主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况，而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。

　　为保持SQL性能或者说是执行计划的稳定性，需要从以下几个方面着手：

　　1. 规划好优化统计信息的收集策略。对于Oracle 10g来说，默认的策略能够满足大部分需求，但是默认的收集策略会过多地收集列上的直方图。由于绑定变量与直方图固有的矛盾，为保持性能稳定，对使用绑定变量的列，不收集列上的直方图；对的确需要收集直方图的列，在SQL中该列上的条件就不要用绑定变量。统计信息收集策略，可以考虑对大部分表，使用系统默认的收集策略，而对于有问题的，可以用DBMS_STATS.LOCK_STATS锁定表的统计信息，避免系统自动收集该表的统计信息，然后编写脚本来定制地收集表的统计信息。脚本中类似如下： 

　　2. 修改SQL语句，使用HINT，使SQL语句按HINT指定的执行计划进行执行。这需要修改应用，同时需要逐条SQL语句进行，加上测试和发布，时间较长，成本较高，风险也较大。

　　3. 修改隐含参数” _optim_peek_user_binds”为FALSE，修改这个参数可能会引起性能问题（这里讨论的是稳定性问题）。

　　4. 使用OUTLINE。对于曾经出现过执行计划突然变差的SQL语句，可以使用OUTLINE来加固其执行计划。在10g中DBMS_OUTLN.CREATE_OUTLINE可以根据已有的执行正常的SQL游标来创建OUTLINE。如果事先对所有频繁执行的核心SQL使用OUTLINE加固执行计划，将最大可能地避免SQL语句性能突然变差。

　　注：DBMS_OUTLN可以通过$ORACLE_HOME/rdbms/admin/dbmsol.sql脚本来安装。

　　5. 使用SQL Profile。SQL Profile是Oracle 10g之后的新功能，此处不再介绍，请参考相应的文档。

　　除此之外，可以调整一些参数避免潜在的问题，比如将”_btree_bitmap_plans”参数设置为FALSE（这个参数请参考互联网上的文章或Oracle文档）。

　　而在实际工作中，通过使用定制的统计信息收集策略，以及在部分系统上使用OUTLINE，系统基本上不会出现已有的SQL性能突然变差的情况。当然也有维护人员操作不当引起的SQL性能突然变差，比如建了某个索引而没有收集统计信息，导致SQL使用了新建的索引，而该索引并不适合于那条SQL；维护人员意外删除了表个索引的统计信息。
 

免责声明：本网站（http://www.ciotimes.com/）内容主要来自原创、合作媒体供稿和第三方投稿，凡在本网站出现的信息，均仅供参考。本网站将尽力确保所提供信息的准确性及可靠性，但不保证有关资料的准确性及可靠性，读者在使用前请进一步核实，并对任何自主决定的行为负责。本网站对有关资料所引致的错误、不确或遗漏，概不负任何法律责任。
本网站刊载的所有内容（包括但不仅限文字、图片、LOGO、音频、视频、软件、程序等）版权归原作者所有。任何单位或个人认为本网站中的内容可能涉嫌侵犯其知识产权或存在不实内容时，请及时通知本站，予以删除。