一條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. [email protected] 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);

SQL> insert into a values(1);

1 row created.

SQL> insert into a values(2);

SQL> insert into b values(1);

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

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

感覺這種表連接配接方式就是多餘的，因為minus之後的結果，表b中肯定是沒有比對的值，再一次關聯也實在是浪費。

然後回到原本的sql語句。

xxxx  (select * from TEST_vip_new minus select * from TEST_vip_new_bak

        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中的資料差别很小，為什麼不直接去增量的資料呢。帶着疑問感覺好像找到了問題的關鍵，然後把開發的同學叫上來一起讨論一番，其實對于我來說是比較好奇為什麼會寫出那樣的表關聯，當時是出于什麼特别的考慮。