转载

一条insert语句导致的性能问题分析（一)

今天早上开发找我看一个问题，说他们通过程序连接去查一个表的数据的时候，只查到了8条记录，这个情况着实比较反常，因为从业务上的数据情况来说，不可能只有8条。
但是开发没有太多的权限做线上环境的数据检查，就让我帮忙看一下。
语句大概是下面这样的形式。
select count(*) from TEST_VIP_LOG t where t.flag in(2,3) and insert_time >= to_date('2016-03-10','YYYY-MM-DD') and insert_time< to_date('2016-03-17','YYYY-MM-DD')
简单运行之后，发现返回的结果是2万多条记录。
当然我这边查询的结果还是有一定的可靠性的。所以开发的这个问题就自然落到了我的头上，为什么他们查看的数据只有8条，而我这边的数据却有2万多条，这个问题听起来确实有些蹊跷，但是都是事出有因，简单了解了一下事情的来龙去脉之后，原来他们是在早上八点程序自动连接去做的查询，我查询的时候已经到了快10点，这个时间点里，一切皆有可能，但是为什么短时间内会有这么大的数据变化呢，于是我查看了数据库的负载情况，发现在八点左右确实有一些DB time的提升，查看sql方面的变化，也确实发现有一个job在运行，而运行的过程中会涉及这个表TEST_VIP_LOG的数据变更。看起来问题似乎是有了一些眉目。但是当我查看锁的情况时，整个人都不好了。
$ sh showlock.sh
Current Locks
-------------
SID_SERIAL   ORACLE_USERN OBJECT_NAME               LOGON_TIME           SEC_WAIT OSUSER     MACHINE      PROGRAM              STAT STATUS     LOCK_ MODE_HELD
------------ ------------ ------------------------- ------------------- --------- ---------- ------------ -------------------- ---------- ---------- ----- ----------
2655,14247   SYS          TEST_VIP_LOG              2016-03-16 01:03:25         0 oracle     statg2.cyou. oracle@statg2.cyou.c WAITING        ACTIVE     DML   Row-X (SX)
可以看到有一个session还在active状态，而且相关的表正是test_vip_log,而且这个session是在凌晨1点登陆的，一直到了早上十点多还在运行。也就间接意味着运行了近10个小时。
关联了一下对应的session执行的语句，发现是一条insert语句,竟然运行了近10个小时。
$ sh showsessql.sh 2655,14247
SQL_ID                         SQL_TEXT
------------------------------ ------------------------------------------------------------
d1zs82wnrs52u                  INSERT INTO TEST_VIP_LOG(CN,GRADE,RANK,SCORE,FLAG,INSERT_TIM
                               E,OLD_RANK,SIGN,STATUS,TAG,OLD_SCORE) SELECT A.CN,A.GRADE,A.
                               RANK,A.SCORE,DECODE(SIGN(A.RANK-(NVL(B.RANK,-1))),1,2,-1,3,0
                               ,1), SYSDATE,(NVL(B.RANK,-1)),B.SIGN,B.FLAG,B.TAG,B.SCORE FR
                               OM ( SELECT * FROM TEST_VIP_NEW MINUS SELECT * FROM TEST_VIP_NEW_BAK
                               ) A LEFT JOIN TEST_VIP_NEW_BAK B ON A.CN=B.CN
然后就开始想这个语句是在几个月以前有一个需求变更，里面有两个表TEST_VIP_NEW和TEST_VIP_NEW_BAK做一些关联，然后把数据插入TEST_VIP_LOG，这个关联看起来还是比较奇怪的。
我们来简单看一看。
insert into TEST_vip_log(CN,GRADE,RANK,SCORE,FLAG,INSERT_TIME,OLD_RANK,sign,stat
us,TAG,OLD_SCORE)
        select a.cn,a.GRADE,a.RANK,a.SCORE,DECODE(sign(a.rank-(NVL(b.rank,-1))),1,2,-1
,3,0,1),
        sysdate,(NVL(b.rank,-1)),b.sign,b.flag,b.tag,b.score
        from
        (
             select * from TEST_vip_new minus select * from TEST_vip_new_bak
        ) a left join TEST_vip_new_bak b
        on a.cn=b.cn ;
首先test_vip_new会和test_vip_new_bak做一个minus操作，会以test_vip_new为基准匹配，然后得到的结果集再和test_vip_new_bak继续匹配，左连接匹配。
总体来看这个映射关系没有任何意义啊。可以做一个简单的测试来说明。两个表存在一个字段id，然后做匹配
SQL> create table a (id number);
Table created.

SQL> create table b (id number);
Table created.

SQL> insert into a values(1);
1 row created.

SQL> insert into a values(2);
1 row created.

SQL> insert into b values(1);
1 row created.

SQL> select * from a minus select * from b;
        ID
----------
         2
minus之后得到的结果是id=2的记录，然后再和表b映射，那么这种映射关系得到的结果是下面的形式。
SQL> select *from (select * from a minus select * from b) a left join b on a.id=b.id;
        ID         ID
---------- ----------
         2
感觉这种表连接方式就是多余的，因为minus之后的结果，表b中肯定是没有匹配的值，再一次关联也实在是浪费。
然后回到原本的sql语句。
xxxx (select * from TEST_vip_new minus select * from TEST_vip_new_bak
        ) a left join TEST_vip_new_bak b
        on a.cn=b.cn
这个表test_vip_new_bak反复关联，这个表的数据是怎么得来的呢，原来在job开始运行的时候就会重新初始化这个表的数据
execute immediate 'truncate table TEST_vip_new_bak';
insert /*+ append*/ into TEST_vip_new_bak select * from TEST_vip_new;
COMMIT;
按照目前的分析思路，可见test_vip_new里面的数据和test_vip_new_bak中的数据差别很小，为什么不直接去增量的数据呢。带着疑问感觉好像找到了问题的关键，然后把开发的同学叫上来一起讨论一番，其实对于我来说是比较好奇为什么会写出那样的表关联，当时是出于什么特别的考虑。

正文到此结束