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 thoughts on “This is tuning!

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

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

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

    又想到另外一个办法 (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.

  3. to Fenng
    呵呵,俺们销售已经开始帮我向客户要表扬信去了。

    to 木匠
    城市我就不说了,对于税务部门来说也算是个故障了。同步策略已经提请开发商去优化了,毕竟写程序的人在,源码就是可以修改的,呵呵。

    to eygle
    Life tuning is much difficult than oracle. 🙁

  4. to Thomas Zhang
    领导们很喜欢吃饭的,昨天晚上一顿饭吃了将近3个小时还不见散,我实在呆的无聊先行告退了,据说后来领导们又喝了一个小时,然后去卡拉OK了 🙂

  5. BINGO~
    真了解我哈
    反正我经常也看不懂里面在写什么
    留言不就是说话么 :p
    在哪说都一样
    我争取扣题,可是扣不到,也没办法

Leave a Reply

Your email address will not be published. Required fields are marked *