一個SQL語句引發的ORA-00600錯誤排查（二）

繼昨天一個SQL語句導致的ORA-00600錯誤之後，我給出了背景和初步的分析結果，今天來給出我的結論，當然說明原因不是我的本意，還有反思。
首先語句類似這樣的形式：
MERGE INTO (SELECT * FROM TEST_SERVER_LOG WHERE BUY_TIME>=TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS')
                               AND BUY_TIME<to_date(:2 ,'yyyy-mm-dd="" hh24:mi:ss')=""                               ="" and="" put_date="TO_DATE(:3" ,'yyyy-mm-dd'))="" t                    USING(SELECT
                           TO_CHAR(:4 ) AS SN,
                           TO_NUMBER(:5 ) AS GROUP_ID,
                          。。。
                           TO_NUMBER(:23 ) AS CLIENT_STYLE,
                           TO_CHAR(:24 ) AS GAME_TYPE
                         FROM DUAL) A
                   ON (T.SN=A.SN)
                   WHEN NOT MATCHED THEN
                   INSERT(T.SN,T.GROUP_ID,T.SERVER_IP,。。。)
                   VALUES(A.SN,A.GROUP_ID,A.SERVER_IP。。。)
執行後會報出ORA-00600，我在初步的分析之後排除了繫結變數的個數，表中資料量大的可能因素。
在經過一番周折之後，發現問題可能出在這個語句的結構上。
當然我換一個方式來說明，我可以隨便建立一個表，然後模擬這個ORA-00600的錯誤。
建立測試表test_bug
SQL> create table test_bug as select * from dba_objects where rownum<1;
Table created.
然後使用如下的語句嘗試生成執行計劃。
SQL> explain plan for merge into (select * from test_bug where object_type='TABLE') t
       using (select :1 object_id,:2 object_name,:3 objet_type from dual) a
       on(t.object_id=a.object_id)
       where not matched then
       insert into test_bug(object_id,object_name,object_type) values(a.object_id,a.object_name,a.object_type);
explain plan for merge into (select * from test_bug where object_type='TABLE') t
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcsfbdnp:1], [1], [], [4], [], [],
[], [], [], [], [], []
當然了我無意中埋下了幾個探針，如果你看到語句哪裡有問題，後續分析就會明白了。
這個語句裡的問題我是可以保持了(select * from test_bug where object_type='TABLE') t 這個子查詢，丟擲了ORA-00600的錯誤，那麼我再簡化一番如何，簡化為（select*from test_bug）t這個子查詢，同樣還是會丟擲一樣的錯誤。
explain plan for merge into (select * from test_bug ) t
       using (select :1 object_id,:2 object_name,:3 objet_type from dual) a
       on(t.object_id=a.object_id)
       where not matched then
       insert into test_bug(object_id,object_name,object_type) values(a.object_id,a.object_name,a.object_type);
explain plan for merge into (select * from test_bug ) t
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcsfbdnp:1], [1], [], [4], [], [],
[], [], [], [], [], []
我們來回過頭來翻翻舊賬，剛剛的語句的問題在如下的幾個地方，在解析的時候都沒有丟擲錯誤，可見問題是出在這些之前，那麼推理只有test_bug相關的子查詢了。


修復這幾個語法之後，使用下面的方式就沒有問題了。
explain plan for merge into test_bug t
       using (select :1 object_id,:2 object_name,:3 object_type from dual) a
       on(t.object_id=a.object_id)
       when not matched then
       insert (object_id,object_name,object_type) values(a.object_id,a.object_name,a.object_type);
明白了問題的原因，我們來反思一下，其實我們縮寫的merge語句都是merge into table_xx using(xxxx) on (xxx)的形式
在table_xx的地方加入子查詢，可能會讓我們在聯想到一些語句中使用子查詢的DML方式，但是在merge語句中，這個地方就是大忌，所幸的是這個問題目前的測試沒有發現對線上環境產生嚴重的影響，但是需要引以為戒。
而對於merge語句的更多反思，如下：
1.我所從事的一些調優工作中，對於merge的優化很難下手，因為這雖然是一個語句，但是有多重執行路徑，執行計劃沒法確定，使用調優工具優化也給不出建議。
2.在10g的版本中，如果scheduler中使用PL/SQL塊，包含有merge語句，使用dbms_metadata.get_ddl竟然無法檢視到完整的DDL資訊
3.如果你想檢視到DDL的資訊，如果通過解析dmp的方式，就會發現DMP對於這個語句的處理很是特殊，可能又會讓你失望了。
這些問題會或多或少困擾到你，而我印象很深的案例，則是第1項中列舉的，優化類的困擾。我舉一個例子。
下面是我在一次系統監控中發現的一個效能問題，CPU使用異常，而經過分析發現瓶頸來源於資料庫層面的一個SQL語句。

看到的語句類似這樣的形式：

MERGE INTO UC_OPENPLATFORM_USER t
                   USING (SELECT count(*) CNT from UC_OPENPLATFORM_USER where
USER_ID=:1 and PLATFORM=:2) tw
                   ON (tw.CNT>0)
                   WHEN MATCHED THEN UPDATE SET t.NAME=:3, t.UPDATE_DATE=SYSDATE
where USER_ID=:4 and PLATFORM=:5
                   WHEN NOT MATCHED THEN INSERT(USER_ID, PLATFORM, NAME,
CREATE_DATE, UPDATE_DATE) VALUES(:6, :7, :8, SYSDATE, SYSDATE)
檢視執行計劃發現裡面存在大量的全表掃描，資源消耗極高。
而這個語句的邏輯其實仔細看看還能明白，就是在插入一條記錄前看看錶中是否含有，如果沒有就插入，否則更新，但是裡面使用了count(*)的方式處理，過濾條件存在一些潛在的問題，而優化方式就是簡化這種邏輯。改為如下的方式：

MERGE INTO UC_OPENPLATFORM_USER t

                   USING (SELECT :1 USER_ID,:2 PLATFORM from DUAL) tw

                   ON (tw.USER_ID=T.USER_ID and tw.PLATFORM=t.PLATFORM)

                   WHEN MATCHED THEN UPDATE SET t.NAME=:3, t.UPDATE_DATE=SYSDATE

where USER_ID=:4 and PLATFORM=:5

                   WHEN NOT MATCHED THEN INSERT(USER_ID, PLATFORM, NAME,

CREATE_DATE, UPDATE_DATE) VALUES(:6, :7, :8, SYSDATE, SYSDATE)       

而對ORA-00600的這個問題，其實也可以進一步反思，這個merge使用只有一個場景，其實可以考慮使用INSERT語句來實現。
很多的事情都有兩面性，merge語句就是如此，而且是一種特殊的存在，我依然記得很久之前的一次技術爭論中，有人說道：判斷一個技術的優劣，也需要看待，到底是它帶來的問題更多還是解決的問題更多？   
確實如此。 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2122253/，如需轉載，請註明出處，否則將追究法律責任。