怎样保持Oracle数据库SQL性能的稳定性.docVIP

怎样保持Oracle数据库SQL性能的稳定性 有客户遇到SQL性能不稳定，突然变差导致系统性能出现严重问题的情况。对于大型的系统来说，SQL性能不稳定，有时突然变差，这是常常遇到的问题。这也是一些DBA的挑战。 　　对于使用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： 　　1 select * from orders where state=:b1 　　这里:b1是变量，在大多数情况下这个值为0，则应该使用索引，但是如果SQL被重新解析，而第一次执行时应用传给变量b1值为1，则不会使用索引，采用全表扫描的方式来访问表。对于绑定变量的SQL，只在第一次执行时才会进行绑定变量窥探，并以此确定执行计划，该SQL后续执行时全部按这个执行计划。这样在后续执行时，b1变量传入的值为0的时候，仍然是第一次执行时产生的执行计划，即使用的是全表扫描，这样会导致性能很差。 　　2） 绑定变量的值变化范围过大： 　　同样假如orders表有一列created_date表示一笔订单的下单时间，orders表里面存储了最近1年的数据，有如下的SQL： 　　1 Select * from orders where created_date =:b1; 　　假如大多数情况下，应用传入的b1变量值为最近几天内的日期值，那么SQL使用的是created_date列上的索引，而如果b1变量值为5个月之前的一个值，那么就会使用全表扫描。与上面描述的直方图引起的问题一样，如果SQL第1次执行时传入的变量值引起的是全表扫描，那么将该SQL后续执行时都使用了全表扫描，从而影响了性能。 　　3） 分区数据量不均匀： 　　对于范围和列表分区，可能存在各个分区之间数据量极不均匀的情况下。比如分区表orders按地区area进行了分区，P1分区只有几千行，而P2分区有200万行数据。同时假如有一列product_id，其上有一个本地分区索引，有如下的SQL： 　　1 select * from orders where area=:b1 and product_id =:b2 　　这条SQL由于有area条件，因此会使用分区排除。如果第1 次执行时应用传给b1变量的值正好落在P1分区上，很可能导致SQL采用全表扫描访问，如前面所描述的，导致SQL后续执行时全部使用了全表扫描。 　　3. 其他原因，比如表做了类似于MOVE操作之后，索引不可用，对索引进行了更改。当然这种情况是属于维护不当引起的问题，不在本文讨论的范围。 　　综上所述，SQL语句性能突然变差，主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况，而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。