一次夜维SQL的性能优化

最近单位搬家，从国家会议中心，搬往空气清新的顺义后沙峪，搬迁之前的完结上线中，碰见了一些棘手的问题，有一些值得借鉴的地方。

这是一个夜维程序的优化。这个夜维的目的，是每日删除30+张表历史数据，其中的主要矛盾，是一张5000万的表，以下仅针对这张表的优化，做下介绍，大致经历了几个阶段，

阶段一：

顺序删除每张表，例如表A和B，B为A表子表，由于表有主外键关系，因此需要先删B表，再要删除A，删除条件是从A表检索出历史过期的数据对应的记录id，用B表p_id和A表id关联，执行删除，id字段是A表主键，使用序列赋值，p_id、id和c_date均有索引定义，A表总数据量2000万，A表每日待删除数据量为200万，B表总数据量5000万，B表每日待删除数据量约为800万，为了减小UNDO和REDO压力，需要批量提交，SQL类似如下，

delete from B where B.p_id in (select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd')) and rownum < ?;

一次删除10000条(?值为10000)，由于c_date(只有日期无时间，只保存10天)区分度低，因此子查询用了全表扫描，删除B表需要执行200次SQL语句，即200次20000万A表的全表扫描，业务量初期数据有限，A表数据量处于百万级，机器配置较高，因此没有问题，但随着数据量的增加，执行时间变久，毋庸置疑。

阶段二：

由于业务量增加，数据库积累的数据有一定量，导致夜维执行时间越来越久，需要进行优化。

首先子查询全表扫描，不可避免，为了提升效率，一种思路就是少做事。200次2000万A表的全表扫描操作，是否可以避免？

既然每次需要删除的是，2000万中的200万，可以先将这200万存入中间表，即使全表扫描，只扫描200万，要比扫描2000万数据要强些，中间表C只有一个字段，用于存储待删除的id标记，

create table C (id number);

 

insert into C select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

然后用中间表，和B表关联，

delete from B where B.p_id in (select id from C) and rownum <= ?;

?值为10000，代表每次删除1万。同时从运行同事了解，夜维执行期间，数据库负载不高，因此可以充分利用资源，数据库服务器80C128G，应用开启多线程，除了主子表外，其他表实现并发删除操作。

阶段三：

随着业务量逐渐增加，上面的机制仍不能满足要求，而且有几次夜维执行时间，甚至超了20小时，奇怪的是，夜维某些天正常，可能5、6个小时就能完成，某些天就会出现超长，甚至有一次第二天即将执行，然而第一天夜维还未完成，为了不影响执行，手工kill了旧进程。

回来再看这条SQL，其中子查询返回的记录，大约200万左右数据，B表和子查询关联，得到所有符合条件的记录，大约800万，即1:4的关系，1条C表的id值，对应B表4条记录，为了批量提交，每次只删除这800万中的1万，

delete from B where B.p_id in (select id from C) and rownum <= ?;

这几次超长执行，从数据库层面看，反映的现象就是物理读超高，例如之前这条SQL物理读，值是3000，这几次值就是10000。

由于子查询肯定全表扫描，每次执行，都要读取200万数据，第一次执行SQL语句，就需要从磁盘文件读取，放入buffer cache，此时消耗物理读，若这个时间段内，对于数据库缓存消耗高，例如其他大表的频繁加载，就会增加buffer cache的age out刷出操作，进而可能出现，第二次执行这条SQL语句，这200万数据部分、甚至全部，需要从磁盘再次读取，如果待删除800万，一次删除1万记录，就需要执行800次，极端情况，就需要重复加载800次200万数据，平均下来，单次物理读高，就可想而知了。

为了缓解，打算这么调整，为C表增加pkid字段，用于存储rownum，如下示例，id仍是待删除的条件值，pkid则为A表id对应的rownum，其目的就是为了，C表每个id都对应一个编号，且这个编号是有序递增，

create table C (id number, pkid number);

 

create index idx_c_01 on c(pkid);

 

insert into C select id, rownum from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

删除B表的时候，首先程序中循环，以id为条件，一次检索1万记录，例如第一次是"where pkid > 0 and pkid < 10001"，第二次是"where pkid > 10000 and pkid < 20001"，即将B表每次删除1万条，批量删除的逻辑，推至内层循环，

delete from B where B.p_id in (select id from C where pkid > ? and pkid < ?);

按照业务评估，C表1个id，对应B表4条记录，因此子查询1万，B表删除4万，虽然一次批量删除较之前，有所增加，但看着是可控，而且可以避免，每次读取C表所有200万数据。

阶段四：

可是这种修改，当晚执行，就出现了问题，夜维日志报错，ORA-01555，

从alert日志中，确认就是这条SQL，导致了这个ORA-01555错误，

ORA-01555 caused by SQL statement below (SQL ID: xxxxxxxxxx, Query Duration=11500 sec, SCN: 0x0001.f10b2hk7):

delete from B where B.p_id in (select id from C where pkid > :1 and pkid < :2);

ORA-01555错误，快照太旧，是Oracle一个非常经典的错误号，简单一句话介绍，我觉得就是“DML语句需要用UNDO记录的数据找到前镜像时，该记录在UNDO中已经被覆盖，导致无法利用UNDO中的记录完成一致性读”，我曾写了一篇小文介绍(http://blog.csdn.net/bisal/article/details/18187635)。

再看SQL语句，说明执行delete操作，时间太久，导致期间使用的UNDO前镜像，已经被其他事务覆盖了，因此直接报错ORA-01555。而且怀疑，这条SQL语句，可能没有一次执行成功的，由于使用了绑定变量，缓存未被刷新，检索出来，报错SQL使用的绑定变量值，正是第一次执行需要的0-10000，

(提取方法可参考《 一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法 》)

要了解为什么执行慢，就需要看一下，SQL语句的执行计划，此处屏蔽了表名，解释一下，

(1) 首先对表B执行全表扫描。

(2) 对表A执行了索引扫描。

(3) 然后以(1)结果集为驱动表，和(2)结果集进行NESTED LOOPS SEMI连接操作。

SQL执行慢原因基本清楚了，表B有5000万的数据，表A总计200万数据，1次检索1万数据，相当于执行200次5000万数据的全表扫描再和1万数据进行NESTED LOOPS SEMI表连接操作，进而删除B表数据。

这比800次扫描200万的数据，有过之而无不及，不报ORA-01555的错误才怪，

delete from B where B.p_id in (select id from C) and rownum <= ?;

问题来了，B表的p_id字段有索引，查看统计信息，无论是表，还是索引，都是每晚22:00，由自动采集任务更新了，夜维执行时间，每日00:30开始执行，可以说每次用的，都是最新的统计信息，这次调整，原义是限制内层数据量，为了减少数据，然后利用B表索引，为何没用上p_id索引？

难道子查询1次1万，有些过了？

用二分法尝试，0-5000、0-2500、...、甚至使用0-10，都比较慢，没用索引。

碰巧测试了下，1910000-1920000区间，这条SQL执行迅速，看其对应的执行计划，正是我们需要的，

(1) 索引范围扫描表C。

(2) 索引范围扫描表B。

(3) (1)和(2)进行NESTED LOOPS连接操作。

为何这一区间，就可以使用B表的索引，0-10000区间就不能使用？

有的同事提问：

“C表id如果排序，和B表中顺序一致的话，会不会有影响？就是在插入C表id前，按照id排下序。”

此时看下pkid=1910000-1920000对应的C表id记录，可以发现，基本都是有序，而且间距较小，例如

1000001

1000003

1000010

1000011

...

再看一下pkid=1-10000区间，有些是无序的，而且差值较大，例如，

1000021

1000210

1000020

1001000

1000002

...

之前我们说了，C表的id来自于A表的主键序列，意味着有序递增，换句话说，id越近的记录数，就越可能位于同一个数据块，id越远的记录数，就越可能不在同一个数据块，区别就是，例如前者读取两个记录，可能只需要1次IO，后者可能需要2次IO，这很像索引的聚簇因子，即索引键值对应的数据记录，在数据块中存储的越有序，clustering factor的值越低，计算索引扫描的成本值，就会越低，此时认为索引扫描更高效，

C表中id列越有序，对应于表B记录，就越可能位于相同数据块，消耗更小IO操作，因此此时的焦点，就在于如何让C表id有序？

之前C表数据用如下语句，

insert into C select id, rownum from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

由于从表A检索，未指定任何order by排序，因此默认会按照数据，在数据块中的排序顺序，进行读取，无法保证有序。此时我们增加order by，让其按照id顺序进行读取，就可以保证表C中id有序，

insert into C select p.*, rownum from (select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd') order by id) p;

细心的朋友可能注意到，order by id是否主键，对于rownum取值的顺序，可能会有影响。

删除语句不变，

delete from B where B.p_id in (select id from C where pkid > ? and pkid < ?);

但此时任何区间，都可以按照上面，正确的执行计划，进行删除操作，

这两天执行夜维，基本保持2.5小时左右用时，这张总计5000万数据量的B表，800万/日删除用时，45分钟左右，一下从主要矛盾，变为次要矛盾了。

问题解决过程，属于团队的智慧，感谢开发团队的山山、运维团队的力伟、运行团队的健哥、亚伟和albert兄。

总结：

1. 有人曾说，好架构不是设计出来的，而是演进出来的，对于某些数据库开发来说，同样适用，不同的方案在不同阶段，适用程度不同，例如本文示例。

2. 但是从某一方面来讲，这种性能隐患，又是可以设计，可以避免，比如大表的全表扫描，如果开始不考虑，毋庸置疑，就是会随着数据量的增加，产生影响，可以看出，逻辑设计，以及SQL审核，在数据库开发工作中的重要。

3. 出现SQL性能问题，首先要看的就是执行计划，当然你要知道，如何找出真实的执行计划，如何找出绑定变量值，可能还需要看10053的trace文件，这些常用知识点，可能未必记得，但用的时候知道从何检索，Oracle官方文档、Google等等，就可以了，之前曾写过一些小文，仅供参考，

一个执行计划异常变更的案例 - 前传

一个执行计划异常变更的案例 - 外传之绑定变量窥探

一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

rolling invalidation对子游标产生的影响

一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法

一个执行计划异常变更的案例 - 外传之AWR

一个执行计划异常变更的案例 - 外传之ASH

一个执行计划异常变更的案例 - 外传之SQL AWR

一个执行计划异常变更的案例 - 外传之直方图

一个执行计划异常变更的案例 - 外传之SQL Profile(上)

一个执行计划异常变更的案例 - 外传之SQL Profile(下)

一个执行计划异常变更的案例 - 正传