SQL性能变差的原因

绝地灬酷狼 2022-09-19 04:28 195阅读 0赞

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][SQL 1] 使用的是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][] 语句性能突然变差，主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况，而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。

[Oracle]: http://gz.easthome.com/com_oracle.aspx?Id=22
[SQL]: http://www.easthome.com/public/zt/c_detail.aspx?thematicid=21&courseid=303
[SQL 1]: http://sh.easthome.com/public/zt/c_detail.aspx?thematicid=21&courseid=233