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。

16 Comments Add yours

Fenng says:

July 9, 2007 at 11:46 pm

总遇到这样的客户，工作成绩可真容易上来，哈

木匠 says:

July 10, 2007 at 12:52 am

我2005年到呼和浩特地税/国税,焦作地税,郑州国税等税务局, 做过类似的工作,
中软开发内蒙古, 神州数码承包了河南国税, 河南一个本地软件公司做了河南地税(还有Dell的外围技术支持).
他们可能还记得我. 嘻嘻.

我经常开口闭口骂那些开发商, 有时关系搞僵了, 还得想办法调和.
就干脆连续给他们办了多次Oracle 开发培训, 也不知道他们学到多少…

木匠 says:

July 10, 2007 at 1:16 am

呵, 忘了问你是去的那个城市?

又想到另外一个办法 (Oracle 流行的一个词 “Workaround”),
需要你动手试一下, 用来验证.

每个表建立一个 MV log,

CREATE MATERIALIZED VIEW new_ph
PCTFREE 5 TABLESPACE data_auto
BUILD IMMEDIATE
REFRESH Fast ON DEMAND
ENABLE QUERY REWRITE AS
select a.PH
from a, b
where a.PH = b.PH (+)
and b.PH is null;

因为不能修改 Source SQL, 可能帮助不大.

如果基于原来的 SQL distinct, 可能不能 Fast Refresh.