ORA-600(ktrgcm_3)錯誤

客戶10.2.0.5 RAC資料庫出現ORA-600[ktrgcm_3]錯誤。

[@more@]

錯誤資訊如下：

Sun Jul 07 01:06:00 EAT 2013
Errors in file /oracle/admin/orcl/udump/orcl1_ora_28947.trc:
ORA-00600: internal error code, arguments: [ktrgcm_3], [], [], [], [], [], [], []

詳細TRACE如下：

*** ACTION NAME:() 2013-07-07 00:49:57.500
*** MODULE NAME:(g@orcl (TNS V1-V3)) 2013-07-07 00:49:57.500
*** SERVICE NAME:(orcl) 2013-07-07 00:49:57.500
*** SESSION ID:(3332.36419) 2013-07-07 00:49:57.500
*** 2013-07-07 00:49:57.500
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktrgcm_3], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO T_B_T_IN (TRADE_ID,SUBSCRIBE_ID,BPM_ID,TRADE_TYPE_CODE,IN_MODE_CODE,PRIORITY,SUBSCRIBE_STATE, NEXT_DEAL_TAG,PRODUCT_ID,BRAND_CODE,USER_ID,CUST_ID,USECUST_ID,ACCT_ID,USER_DIFF_CODE, NET_TYPE_CODE,SERIAL_NUMBER,CUST_NAME,ACCEPT_DATE,ACCEPT_MONTH,TRADE_STAFF_ID, TRADE_DEPART_ID,TRADE_CITY_CODE,TRADE_EPARCHY_CODE,TERM_IP,EPARCHY_CODE,CITY_CODE, OLCOM_TAG,EXEC_TIME,FINISH_DATE,OPER_FEE,FOREGIFT,ADVANCE_PAY,INVOICE_NO,FEE_STATE, FEE_TIME,FEE_STAFF_ID,CANCEL_TAG,CANCEL_DATE,CANCEL_STAFF_ID,CANCEL_DEPART_ID, CANCEL_CITY_CODE,CANCEL_EPARCHY_CODE,CHECK_TYPE_CODE,CHK_TAG,AUDIT_TAG, AUDIT_BATCH_NO,ACTOR_NAME,ACTOR_CERTTYPEID,ACTOR_PHONE,ACTOR_CERTNUM, CONTACT,CONTACT_PHONE,CONTACT_ADDRESS,REMARK,IF_MAINTENANCE, PROVINCE_CODE,TRADE_PROVINCE_CODE,CANCEL_PROVINCE_CODE,RSRV_TAG1) SELECT A.TRADE_ID,SUBSCRIBE_ID,BPM_ID,TRADE_TYPE_CODE,IN_MODE_CODE,PRIORITY,SUBSCRIBE_STATE, NEXT_DEAL_TAG,A.PRODUCT_ID,NVL(D.BRAND_CODE,A.BRAND_CODE), A.USER_ID,A.CUST_ID,A.USECUST_ID,ACCT_ID,A.USER_DIFF_CODE, DECODE(A.PRODUCT_ID,'-1','99',NVL(D.NET_TYPE_CODE,A.NET_TYPE_CODE)),A.SERIAL_NUMBER,CUST_NAME,ACCEPT_DATE,A.ACCEPT_MONTH,TRADE_STAFF_ID, TRADE_DEPART_ID,TRADE_CITY_CODE,TRADE_EPARCHY_CODE,TERM_IP,A.EPARCHY_CODE,A.CITY_CODE, OLCOM_TAG,EXEC_TIME,FINISH_DATE,OPER_FEE,FOREGIFT,ADVANCE_PAY,INVOICE_NO,FEE_STATE, FEE_TIME,FEE_STAFF_ID, CASE WHEN B.TRADE_ID_A IS NOT NULL AND A.CANCEL_DATE IS NULL THEN DECODE(A.CANCEL_TAG,'0','4',A.CANCEL_TAG) ELSE CASE WHEN B.TRADE_ID_A IS NOT NULL AND TRUNC(A.ACCEPT_DATE,'DD')= :B2 AND A.ACCEPT_DATE < :B1 AND A.TRADE_TYPE_CODE NOT IN ('116','616') AND A.NEXT_DEAL_TAG IN (SELECT VALUEC1 FROM TD_SD_COMMPARA WHERE PARAM_TYPE_CODE ='NEXT_DEAL_TAG') AND EXISTS (SELECT X.TRADE_ID FROM (SELECT B.TRADE_ID TRADE_ID FROM TF_B_TRADE_BATDEAL A,TF_BH_TRADE B WHERE A.TRADE_ID = B.TRADE_ID AND A.CANCEL_TAG = B.CANCEL_TAG AND A.STATE ='2' AND A.B_UPDATE_DATE >= :B2 AND A.B_UPDATE_DATE < :B1 AND A.BATCH_OPER_TYPE IN ('CREATEPREUSER','CREATEUSER','DISCNTCHG','ADDGRPMEMBER','BATREMOVEMEMBER','PRODUCTCHG','STATISTICS','BATCHARGEFEE','SCOREADJUST','CANCELPREUSER') MINUS SELECT TRADE_ID FROM T_B_T_IN ) X WHERE A.TRADE_ID = X.TRADE_ID)
----- PL/SQL Call Stack -----
object line object
handle number name
c00000142951a9f8 434 procedure U_S_1.P_S_D_T_IN
c0000013baacb568 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+64 call ksedst1() 000000000 ? 000000001 ?
ksedmp()+2176 call ksedst() 000000000 ?
C000000000000D20 ?
4000000004066EE0 ?
000000000 ? 000000000 ?
000000000 ?
ksfdmp()+112 call ksedmp() 000000003 ?
9FFFFFFFFFFE8EB0 ?
60000000000BA288 ?
9FFFFFFFFFFE9480 ?
C000000000000999 ?
40000000040AEF50 ?
kgerinv()+304 call ksfdmp() 9FFFFFFFFFFE9A10 ?
000000003 ?
9FFFFFFFFFFE9490 ?
60000000000BA288 ?
C000000000000612 ?
4000000009903790 ?
kgeasnmierr()+144 call kgerinv() 60000000000318D0 ?
4000000001AF87F0 ?
6000000000032988 ?
4000000001AF87F0 ?
9FFFFFFFFFFE9A50 ?
$cold_ktrgcm()+1188 call kgeasnmierr() 60000000000318D0 ?
8 60000000003139F8 ?
6000000000313A08 ?
6000000000032D00 ?
000000000 ? 000000000 ?
C0000014D3B02A54 ?
C0000014BC6E8AEC ?
ktrget()+1088 call $cold_ktrgcm() 9FFFFFFFBF349F60 ?
60000000000BA288 ?
C000000000000C1D ?
4000000002FB1560 ?
00003811B ?
kdirfrs()+8160 call ktrget() 9FFFFFFFBF349F60 ?
000000000 ?
C00000089B458014 ?
qerixFetchFastFullS call kdirfrs() 9FFFFFFFBF349F58 ?
can()+7504 60000000000BA288 ?
4000000003057DA0 ?
00003825F ?
9FFFFFFFBF34A070 ?
00003821F ? 000000000 ?
C000000000001430 ?
qergiFetch()+816 call qerixFetchFastFullS C0000013CB1A0CA0 ?
can() 4000000001CC4CE0 ?
9FFFFFFFFFFEB750 ?
000007FFF ? 000007FFF ?
C0000013CB1A0CA0 ?
rwsfcd()+256 call qergiFetch() C0000013CB1A08F8 ?
4000000001CC4CE0 ?
9FFFFFFFFFFEB750 ?
000007FFF ?
qerhjFetch()+912 call rwsfcd() 9FFFFFFFBF34A698 ?
4000000001CC4CE0 ?
9FFFFFFFFFFEB750 ?
000007FFF ?
qerflFetch()+336 call qerhjFetch() C0000013CB1A0740 ?
4000000001CCEC70 ?
9FFFFFFFFFFEB820 ?
000007FFF ?
qersoFetch()+2080 call qerflFetch() C0000013CB1A05B0 ?
4000000001CCEED0 ?
C000001426EB7FA8 ?
000007FFF ?
60000000000BA288 ?
qerseFetch()+400 call qersoFetch() C000001426EB7FA8 ?
4000000001CCEBF0 ?
9FFFFFFFFFFEB880 ?
000007FFF ?
qervwFetch()+368 call qerseFetch() C000001426EB7AE8 ?
9FFFFFFFFFFEB8AC ?
9FFFFFFFFFFEB8AC ?
9FFFFFFFBF3DC5DA ?
rwsfcd()+256 call qervwFetch() C000001426EB79C8 ?
4000000001CC4CF0 ?
9FFFFFFFFFFEB8D0 ?
C000001426EB79C0 ?
qerhjFetch()+1152 call rwsfcd() 9FFFFFFFBF3DC5F8 ?
4000000001CC4CF0 ?
9FFFFFFFFFFEB8D0 ?
000007FFF ?
rwsfcd()+256 call qerhjFetch() C000001426EB7710 ?
4000000001CC4D00 ?
9FFFFFFFFFFEB9A0 ?
000007FFF ?
qerhjFetch()+1728 call rwsfcd() 9FFFFFFFBF3DC800 ?
4000000001CC4D00 ?
9FFFFFFFFFFEB9A0 ?
000007FFF ?
60000000000BA288 ?
qerjoFetch()+1168 call qerhjFetch() C0000013AB146D68 ?
000000000 ? 000000000 ?
000000001 ?
60000000000BA288 ?
C0000000000012AB ?
rwsfcd()+256 call qerjoFetch() C000001388B4B060 ?
4000000001CCEC20 ?
9FFFFFFFFFFEBA80 ?
000007FFF ?
qerhjFetch()+1728 call rwsfcd() 9FFFFFFFBF3D7CA8 ?
4000000001CC4D00 ?
9FFFFFFFFFFEBAB0 ?
000007FFF ?
60000000000BA288 ?
qerjoFetch()+1168 call qerhjFetch() C0000013FF4C97D0 ?
000000000 ? 000000000 ?
000000001 ?
60000000000BA288 ?
C0000000000012AB ?
qerflFetch()+336 call qerjoFetch() C000001380167980 ?
4000000001CCEC70 ?
9FFFFFFFFFFEBBC0 ?
000000001 ?
rwsfcd()+256 call qerflFetch() C0000013801676A0 ?
000000000 ? 000000000 ?
000000001 ?
insfch()+304 call rwsfcd() 9FFFFFFFBF3DAEF8 ?
000000000 ? 000000000 ?
000000001 ?
insdrv()+912 call insfch() 9FFFFFFFB9F46F60 ?
C0000000000019BB ?
60000000000BA288 ?
40000000031630F0 ?
00003821D ?
60000000000C6FE0 ?
60000000000C6F18 ?
4000000001C116A0 ?
inscovexe()+1408 call insdrv() 9FFFFFFFFFFEC390 ?
9FFFFFFFFFFEBBF0 ?
60000000000BA288 ?
40000000031B6160 ?
00003825B ?
9FFFFFFFFFFEBBF4 ?
insExecStmtExecIniE call inscovexe() C0000013E1A08250 ?
ngine()+176 C0000013C3B02F08 ?
9FFFFFFFFFFECB40 ?
insexe()+1040 call insExecStmtExecIniE C0000013E1A08250 ?
ngine() C0000013C3B02F08 ?
9FFFFFFFFFFED3D0 ?
60000000000BA288 ?
C000000000000FA5 ?
4000000003166C80 ?
opiexe()+7760 call insexe() C0000013BE5AB8C0 ?
9FFFFFFFFFFED3D0 ?
9FFFFFFFFFFECB50 ?
60000000000BA288 ?
9FFFFFFFFFFED0D0 ?
C0000000000025D3 ?
4000000002F87610 ?
00003821B ?
opipls()+3680 call opiexe() 9FFFFFFFFFFEED70 ?
4000000003119420 ?
60000000000C6CE0 ?
9FFFFFFFFFFED0F0 ?
60000000000BA288 ?
00002DE7D ?
C00000000000224C ?
001121800 ?
opiodr()+2144 call opipls() 9FFFFFFFFFFEF680 ?
4000000002F58A60 ?
00002E0B7 ?
9FFFFFFFFFFEEDD0 ?
60000000000BA288 ?
000000001 ?
rpidrus()+368 call opiodr() 000000066 ? 000000006 ?
4000000001C14510 ?
0000046C0 ?
9FFFFFFFFFFEF690 ?
60000000000BA288 ?
skgmstack()+288 call rpidrus() 9FFFFFFFFFFF1DD0 ?
9FFFFFFFFFFF1810 ?
60000000000BA288 ?
9FFFFFFFFFFF1D90 ?
C000000000000716 ?
4000000002F92F80 ?
00002E07F ?
9FFFFFFFFFFF1870 ?
rpidru()+240 call skgmstack() 9FFFFFFFFFFF1DF0 ?
6000000000030F50 ?
00000F618 ?
4000000001CCBAD0 ?
9FFFFFFFFFFF1DD0 ?
rpiswu2()+976 call rpidru() 00000057B ?
C000000000001026 ?
C000000000001026 ?
4000000002F8FD10 ?
9FFFFFFFFFFF1DE8 ?
9FFFFFFFFFFF2C10 ?
rpidrv()+2352 call rpiswu2() 9FFFFFFFFFFF2B00 ?
4000000002F92360 ?
00002F933 ?
9FFFFFFFFFFF2590 ?
60000000000329A8 ?
C000000000001ABD ?
4000000000F32248 ?
60000000000C6CE0 ?
psddr0()+464 call rpidrv() 000000008 ? 000000066 ?
9FFFFFFFFFFF3710 ?
000000038 ?
9FFFFFFFFFFF2B30 ?
60000000000BA288 ?
psdnal()+736 call psddr0() 000000008 ? 000000066 ?
9FFFFFFFFFFF3710 ?
000000030 ?
pevm_EXECC()+832 call psdnal() 9FFFFFFFFFFF54E0 ?
C0000014FF9104E0 ?
C0000000000011AA ?
9FFFFFFFBF3D3060 ?
C00000142951A9F8 ?
4000000003159470 ?
00002C835 ?
pfrinstr_EXECC()+16 call pevm_EXECC() 9FFFFFFFBF3D9F40 ?
0 9FFFFFFFBF3D30D0 ?
000000020 ?
pfrrun_no_tool()+19 call pfrinstr_EXECC() 9FFFFFFFBF3D9F40 ?
2 C0000013A9EE034C ?
9FFFFFFFBF3D9FA8 ?
pfrrun()+1376 call pfrrun_no_tool() 9FFFFFFFBF3D9F40 ?
000002001 ?
9FFFFFFFBF3D9FA8 ?
60000000000BA288 ?
C000000000000A1C ?
4000000003180540 ?
9FFFFFFFBF3DA390 ?
9FFFFFFFBF3DA008 ?
plsql_run()+1328 call pfrrun() 9FFFFFFFFFFF3798 ?
9FFFFFFFFFFF3780 ?
60000000000BA288 ?
9FFFFFFFFFFF4380 ?
9FFFFFFFFFFF4380 ?
C000000000000E23 ?
4000000002C52D50 ?

根據MOS文件，ORA-600[ktrgcm_3]存在三個已知bug，其中和Bug 7225204 - OERI [ktrgcm_3] can occur [ID 7225204.8]的描述非常相似，當前報錯的SQL語句就是INSERT SELECT語句，且INSERT對應的表在SELECT中也會出現，這與BUG的描述完全一致。

觀察錯誤語句的執行計劃：

============
Plan Table
============
---------------------------------------------------------------------+----------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------+----------------------
| 0 | INSERT STATEMENT | | | | 618K |
| 1 | FILTER | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 345 | 618K |
| 3 | HASH JOIN OUTER | | 1 | 326 | 618K |
| 4 | NESTED LOOPS OUTER | | 1 | 301 | 618K |
| 5 | HASH JOIN OUTER | | 1 | 282 | 618K |
| 6 | HASH JOIN SEMI | | 1 | 263 | 618K |
| 7 | PARTITION RANGE SINGLE | | 16 | 4000 | 4 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| TF_BH_TRADE | 16 | 4000 | 4 |
| 9 | INDEX RANGE SCAN | IDX_TF_BH_TRADE_ACC| 1 | | 3 |
| 10 | VIEW | | 13K | 169K | 618K |
| 11 | MINUS | | | | |
| 12 | SORT UNIQUE | | 13K | 534K | |
| 13 | FILTER | | | | |
| 14 | HASH JOIN | | 13K | 534K | 617K |
| 15 | TABLE ACCESS FULL | TF_B_TRADE_BATDEAL | 13K | 377K | 441K |
| 16 | PARTITION RANGE ALL | | 172M | 2063M | 174K |
| 17 | INDEX FAST FULL SCAN | PK_TF_BH_TRADE | 172M | 2063M | 174K |
| 18 | SORT UNIQUE | | 260K | 2599K | |
| 19 | INDEX FAST FULL SCAN | IDX_T_B_T_ID | 260K | 2599K | 231 |
| 20 | TABLE ACCESS FULL | TF_B_T_STATE_TRANS | 1 | 19 | 2 |
| 21 | TABLE ACCESS BY INDEX ROWID | TD_B_PRODUCT | 1 | 19 | 2 |
| 22 | INDEX UNIQUE SCAN | PK_TD_B_PRODUCT | 1 | | 1 |
| 23 | TABLE ACCESS FULL | TF_B_TRADE_REL | 116K | 2888K | 169 |
| 24 | TABLE ACCESS BY INDEX ROWID | TD_SD_COMMPARA | 8 | 152 | 2 |
| 25 | INDEX RANGE SCAN | PK_TD_SD_COMMPARA | 8 | | 1 |
---------------------------------------------------------------------+----------------------

配合報錯函式資訊，不難確認，問題發生在對T_B_T_IN表的索引快速全掃描上。

但是這個錯誤影響10.2.0.4和11.1.0.7，Oracle在10.2.0.5中應該已經修正了這個錯誤。觀察文件Bug 6157713 - Wrong result from ORA_ROWSCN [ID 6157713.8]的描述，雖然版本資訊符合，但是當前並沒有呼叫ORA_ROWSCN偽列，不過這個BUG的描述中，又指向了上面7225204的bug。

最後檢查文件Bug 14076510 - ORA-600 [ktrgcm_3] in 10.2.0.5.3 - 10.2.0.5.7 [ID 14076510.8]，可以看到這個bug描述在10.2.0.5.3之後出現的ktrgcm_3錯誤的原因，根據三篇文章的描述，以及當前的現象，感覺三篇文件描述的應該是都一個bug，Oracle在處理自關聯查詢時引發了這個錯誤。而這個bug並沒有在10.2.0.5中被真正修復。

除了升級到10.2.0.5.8以及11.2外，如果可能，修改語句避免INSERT SELECT語句訪問插入表。此外Oracle還提供了設定隱含引數"_row_cr"為FALSE的方式，這種方式會帶來效能的影響，如果這個錯誤頻繁發生，也可以考慮進行設定。