一条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;

----------

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

---------- ----------

感觉这种表连接方式就是多余的，因为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中的数据差别很小，为什么不直接去增量的数据呢。带着疑问感觉好像找到了问题的关键，然后把开发的同学叫上来一起讨论一番，其实对于我来说是比较好奇为什么会写出那样的表关联，当时是出于什么特别的考虑。