This is tuning

在上次存储调整之后平稳度过了数月之后，这次客户又来紧急电话，从上周三下午开始数据库服务器I/O负载忽然陡升到100%，开一张发票要40分钟，各大厅纳税人开始骂娘。。。每个月的征期有一周，而纳税人通常都憋到最后几天才开始出动，也就是这一周的最后两天将是系统负载的最高峰，而上周三仅仅是刚进入征期而已。在被变幻莫测的雷雨天气延误了3个小时之后，飞机从首都机场起飞了，即将到达目的地的时候，忽然遇到暴雨天气，飞机在剧烈颤动了良久之后，忽然间好似过山车一样急降，这个急降大概持续了3秒左右，心都被扔到了嗓子眼，全机舱忽然之间鸦雀无声，等急降结束了好一阵儿逐渐平稳以后，才听到周遭各式各样的大吐气。爽啊。降落目的地，客户来接机，然后请吃饭，这么隆重的待遇是因为总局，省局，市局，分局的各级领导都到了，如果征期挺不过去，领导会很生气，后果会很严重。回到酒店，打开刚拿到的周三下午的statspack report，心里面一块石头落地了，非常幸运，太明显的性能问题了。cache buffer chains latch free占据90%以上的等待时间，buffer gets top SQL中一条select distinct的语句在一张报表中占据了60%以上，另外一张居然占据了143.2%，每次执行这个SQL都会有8,725,441.7的buffer gets，在半个小时里面一共执行了150次左右。虽然不知道相关表的数据量和执行计划，但是很明显这就是罪魁祸首，于是安心睡觉。今天上午到客户处，直接告诉客户90%的原因是由于这条SQL语句，然后连上服务器，开始tuning。两张表，A表1679679条记录，B表839889条记录。 SELECT DISTINCT a.PH FROM a WHERE not exists (select 1 from b where a.PH = b.PH) order by a.PH; 就是这么简单的语句，执行计划是对A表索引的index fast full scan，然后根据B表的index range scan做filter，最后sort返回结果。执行计划看上去问题不大，执行一次大概需要20秒左右，但是对于将近200万记录的index fast full scan已经是不小的IO，再做循环filter，更可怕的是，每一笔开票之前都需要有这样一个操作，典型的hot block问题。跟开发商沟通之后，知道这是两张同步记录表，也就是每次业务开始会在A表中insert一条数据，然后开始同步，同步成功之后再在B表中insert一条数据，之后每次业务再开始就先找一下在A表中已经insert了的但是在B表中还不存在的（在B表中不存在就是表示还没同步），很明显业务是不应该如此设计的，在一张表中做标志位也比两张表联合查询只增不减的同步记录要好。但是现在讨论业务已经没有实际意义了。同步成功之后的记录是没有用处的，可惜没有一个默认的删除策略，于是开始设想删除A表的无用数据，按照执行计划，B表的数据量跟执行效率关系不大，只需要删除A表数据就行。写了一个存储过程，每删除10000条记录commit一次。在还没有开始删除的时候，客户的电话已经开始络绎不绝了，各个大厅报客户端死机一样慢，有些纳税人出去转了一趟回来发票还没开出来。。。。删除同步记录的存储过程运行了20分钟，最后剩下了将近40000条记录，此时各个大厅已经报发票可以开出来了，大概每张发票需要开将近10分钟。这仍然不是我想要的结果，10分钟？不可能这么慢的，再次运行statspack，居然。。。又出现另外一条跟这两个表相关的SQL，FT，还有不一样的写法。。。这次是加了条件的，结果直接在A表中full table scan了，40000条记录的FTS仍然是不小的开销，而且仍然存在hot block，所以仍然是latch free。加hint，测试了一下，速度激增，但是开发商说，这是前台程序，没有办法现在立刻更改立刻发布。那就只能再加索引了，根据SQL添加了一个normal index，再次执行SQL，飞一样的速度啊。创建完索引，刚把操作步骤记录下来，客户那边忽然欢歌笑语，3秒钟！客户扭头问我，你做了什么？现在只要3秒钟就完成业务了。我说，呵呵，加了一个索引。 40分钟->10分钟->3秒钟。工作圆满完成。This is tuning。