一条insert语句导致的性能问题分析（一)(r8笔记第40天)

今天早上开发找我看一个问题，说他们通过程序连接去查一个表的数据的时候，只查到了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中的数据差别很小，为什么不直接去增量的数据呢。带着疑 问感觉好像找到了问题的关键，然后把开发的同学叫上来一起讨论一番，其实对于我来说是比较好奇为什么会写出那样的表关联，当时是出于什么特别的考虑。