《一個SQL語句引發的ORA-00600錯誤排查》要點：
本文介紹了一個SQL語句引發的ORA-00600錯誤排查，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

楊建榮,【DBAplus社群】聯合發起人.現就職于搜狐暢游,Oracle ACE-A、YEP成員,超7年數據庫開發和運維經驗,擅長電信數據業務、數據庫遷移和性能調優.持Oracle 10G OCP,OCM,MySQL OCP認證,《Oracle DBA工作筆記》作者.

Merge是從Oracle 9i就引入的功能,它是有別于其他DML中的一種特殊語句,類似于MySQL中的 insert into on duplicate key操作,而且Merge功能更豐富,可以同時對一個表中的數據進行insert和update操作,原本需要存儲過程實現的工作現在用一條SQL語句就可以做到.確實很有亮點,從我的感覺來看,它帶來的問題實在是不少,當然有不少是特定版本的特定問題.

比如最近有一個同事問我一個問題,他運行一個SQL語句拋出了ORA-00600的錯誤,想讓我幫忙分析一下,我了解了問題的大體情況之后,發現值得好好琢磨一番,因為只是客戶端調用拋出異常,沒有給服務器端帶來什么致命的影響,而且這個語句只是通過客戶端工具測試所用,還沒有正式部署,這樣就給我分析問題帶來了不少的時間和空間.

1問題描述

簡單溝通后,我得到了同事提供的SQL語句和執行環境(數據庫版本是11.2.0.3),語句類似下面的形式：

這樣一個語句看起來結構挺復雜,但是細看邏輯倒不復雜.最近處理了不少性能問題,很多都是和Merge的使用有關,這次是不是又中招了呢,帶著疑問來看這個語句,初步的感覺是綁定變量真是多.

這個問題帶給我的最大福利就是能夠復現,這一點真是非常難得,有很多的ORA-00600錯誤復現需要上下文環境觸發一定的條件才可以復現,可遇不可求,就像前些天社群的一篇文章《騰訊高級工程師：一道面試題引發的高并發性能調試思考》,需要動用大量的技巧來復現分析,可能是一個平均運行一百萬次才出現一次的bug.而這個問題讓我省了不少事,在alert日志中也發現了對應的trace文件,但是從里面得到的信息著實有限.

2復現問題

我沒有花太多時間在這個trace上,轉而嘗試去復現這個問題,首先我通過v$sql去數據庫中查看這個SQL語句,結果查找的很仔細,竟然沒有任何的信息,仿佛這個語句沒有執行過一般,所以這個語句在解析時就出了問題.

然后我切換到屬主用戶下,嘗試生成執行計劃.慶幸的是這個時候問題可以復現出來了.

由此可見,這個語句在SQL解析的時候就會拋出問題.對于這個報錯,在MetaLink上進行一番查找,發現相關的bug還真不少,鎖定了一個較為符合的bug.

這是和執行Merge相關的,出現的版本是11.2.0.3,查看里面的解釋,就是打補丁,沒有其它的解決方法.

其實對于ORA-00600的錯誤,就類似開發中的NULLPointerException,這類問題的邊界比較模糊,排查需要花費一些精力,有的時候還需要一些運氣.

3初步結論

我的初步感覺就是問題可能在兩個方面.

1. 一個是TEST_SERVER_LOG這個表數據量非常大,是否在Merge中有一定的影響導致
2. 語句中含有大量的綁定變量,是否綁定變量數過多導致了Merge的支持出現了問題

于是我朝著這個方向進行了分析和排查.我逐個替換了綁定變量,把它暫時替換為常量,發現錯誤依舊出現,只是錯誤的參數部分會有下標的變化.

直到我把整個using部分的綁定變量全部替換掉,竟然拋出了一個看起來不大相關的錯誤.

看這個問題,是字段不存在,仔細查看源表中的字段信息,發現確實如此,我是如獲至寶,好像定位到了問題的原因.

但是經過確認,原來這個表的屬主是另外一個,切換了開發同學使用的用戶之后,語句就可以正常解析了,問題的原因是什么呢?

我們可以通過下面這個語句來說明,在沒有綁定變量的情況下解析會正常通過.

所以這個時候就比較糾結,很多應用場景都是需要使用綁定變量的,所以盡管如此,這不是一個通用的解決方法.然后我嘗試在using子句中添加一個綁定變量,ORA-00600問題再次出現,可見二十多個綁定變量和一個綁定變量都會出現同樣的問題.

所以一個初步結論是,這個錯誤和綁定變量還是有一定的關系,但是具體原因還無法定位.
那么這個問題是否和數據量有關呢.經過確認,這個相關表TEST_SERVER_LOG的數據量情況如下,只有一行數據.

所以可以證明,這個bug和TEST_SERVER_LOG的數據量無關,綁定變量的部分還得進一步確認,目前暫時無法定位.

我們再來看看trace文件中的內容：

SQL運行時拋出的錯誤為

那么qcsfbdnp代表什么含義呢,可以在trace文件中找到一些答案.

報錯編碼是[qcsfbdnp:1], [7], [], [2], [], [],? 這個代表的含義在這個日志中可以看到是在SQL解析器的部分調用出現了問題.

所以經過上面的排除之后,我們需要重新審視這個語句,看看是否存在一些隱患.

4模擬復現問題

經過反復測試之后,我發現這個問題可以很容易復現.

當然我換一個方式來說明,我可以創建一個表,然后模擬這個ORA-00600的錯誤.

創建測試表test_bug

然后使用如下的語句嘗試生成執行計劃.

可能眼尖的朋友發現這個里面有些語法問題,這也是我無意中埋下了幾個探針,如果你看到語句哪里有問題,后續分析就會明白了.

這個語句里的問題我是可以保持了(select * from test_bug where object_type=’TABLE’) t 這個子查詢,拋出了ORA-00600的錯誤,我簡化一番如何,簡化為(select*from test_bug)t這個子查詢,同樣還是會拋出一樣的錯誤.

我們來回過頭來翻翻舊賬,剛剛的語句的問題在如下紅框的幾個地方,在解析的時候都沒有拋出錯誤,可見問題是出在這些之前,那么推理只有test_bug相關的子查詢了.

修復這幾個語法問題之后,使用下面的方式就沒有問題了,最后調用成功的語句如下,我們把子查詢替換為表test_bug

我們來反思一下,其實我們所寫的merge語句都是merge into table_xx using(xxxx) on (xxx)的形式.

在table_xx的地方加入子查詢,可能會讓我們在聯想到一些語句中使用子查詢的DML方式,但是在merge語句中,這個地方會有問題,所幸的是這個問題目前的測試沒有發現對線上環境產生嚴重的影響,需要引以為戒,不過問題還不能這么輕易下結論,我們繼續往下看.

5認知的反轉

如果只是像上面那么想,充其量只是一個攻略,而且你對Merge印象會更加糟糕.

難道是Merge有什么特別之處嗎,我們來看看Oracle的官方對于Merge語句的解釋.可以看到merge into后面可以是table或者是view.

我在上面的測試使用的是子查詢,那么試試view可不可行呢.

我們創建一個view.

create view test_view as select * from test_bug where object_type=’TABLE’;

我滴個神,這個問題確實就是這么任性,那么我們是不是可以得出一個結論,在merge into的后面只能使用table或者view,不能使用子查詢呢,還是不要過早下結論.

6持續反轉(二)

對于這個問題,我們也進行了小范圍的討論,兔子大師在看到我的這個問題之后,在11.2.0.4的版本中進行了測試,之前我碰到的那個問題在這個版本中就不復存在了,兔子威武.

測試復現的過程很簡單：

create table test_bug as select * from dba_objects where rownum<1;

解析生成執行計劃,可以看到這個時候就沒有問題了.

可見這個問題在11.2.0.4之前的版本會拋出ORA-00600,算是一個bug中的bug吧.

7簡單總結

通過上面波瀾起伏的測試和復現、驗證,我們可以得出這樣的一個結論：這個問題和表中的數據量無關,和是否使用子查詢也沒有關系,是在特定版本中出現的一個特定的問題,當然明白了原委,我們完全可以規避這個問題.

而且這個場景中Merge的使用有些不太到位,其實也可以考慮使用INSERT語句來實現.

8反思

對于Merge語句的更多反思,如下：

1. 我所從事的一些調優工作中,對于Merge的優化很難下手,因為這雖然是一個語句,但是有多重執行路徑,執行計劃沒法確定,使用調優工具大多也給不出建議.
2. 在10g的版本中,如果scheduler中使用PL/SQL塊,包含有Merge語句,使用dbms_metadata.get_ddl竟然無法查看到完整的DDL信息.
3. 如果你想查看到DDL的信息,如果通過解析dmp的方式,就會發現DMP對于這個語句的處理很是特殊,可能又會讓你失望了.

這些問題會或多或少困擾到你,而我印象很深的案例,則是第1項中列舉的,優化類的困擾.我舉一個例子.

下面是我在一次系統監控中發現的一個性能問題,CPU使用異常,而經過分析發現瓶頸來源于數據庫層面的一個SQL語句.

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

查看執行計劃發現里面存在大量的全表掃描,資源消耗極高.

而這個語句的邏輯其實仔細看看還能明白,就是在插入一條記錄前看看表中是否含有,如果沒有就插入,否則更新,但是里面使用了count(*)的方式處理,過濾條件存在一些潛在的問題,而優化方式就是簡化這種邏輯.改為如下的方式：

9總結

很多的事情都有兩面性,Merge語句就是如此,而且是一種特殊的存在,可能很多問題在特定版本中出現,在升級之后問題就會迎刃而解,而如果分析不當,可能我牢牢攥在手里的就是一個錯誤的結論,所以很多觀點碰撞還是會摩擦出不少的火花,大家共同從中受益.我依然記得很久之前的一次技術爭論中,有人說道：判斷一個技術的優劣,也需要看待,到底是它帶來的問題更多還是解決的問題更多?

確實如此.

文章出處：DBAplus社群

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4423.html