0

ORA-00600: [ktrgcm_3]

老杨 2019-03-25
127
摘要:客户10.2.0.5RAC数据库出现ORA-600[ktrgcm_3]错误。错误信息如下:SunJul0701:06:00EAT2013E...

问题描述

客户10.2.0.5 RAC数据库出现ORA-600[ktrgcm_3]错误。
错误信息如下:

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')<TRUNC(A.CANCEL_DATE,'DD') THEN DECODE(A.CANCEL_TAG,'3','0',A.CANCEL_TAG) ELSE CASE WHEN TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN DECODE(A.CANCEL_TAG,'3','0',A.CANCEL_TAG) ELSE DECODE(A.CANCEL_TAG,'1','0','4','0',A.CANCEL_TAG) END END END CANCEL_TAG, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_DATE END CANCEL_DATE, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_STAFF_ID END CANCEL_STAFF_ID, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_DEPART_ID END CANCEL_DEPART_ID, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_CITY_CODE END CANCEL_CITY_CODE, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_EPARCHY_CODE END 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,A.REMARK,IF_MAINTENANCE, F_SYS_GETVALUE('get_province_code',NULL,DECODE(LENGTH(A.EPARCHY_CODE),4,A.EPARCHY_CODE,A.TRADE_EPARCHY_CODE),NULL,NULL) PROVINCE_CODE, F_SYS_GETVALUE('get_province_code',NULL,A.TRADE_EPARCHY_CODE,NULL,NULL) TRADE_PROVINCE_CODE, F_SYS_GETVALUE('get_province_code',NULL,A.CANCEL_EPARCHY_CODE,NULL,NULL) CANCEL_PROVINCE_CODE, CASE WHEN C.NEW_STATE_CODE='H' THEN '9130' ELSE CASE WHEN C.OLD_STATE_CODE='H' THEN '9140' END END FROM TF_BH_TRADE A, (SELECT TRADE_ID_A,TRADE_ID_B FROM TF_B_TRADE_REL WHERE RELATION_TYPE_CODE='02' AND STATE IN ('0','A') )B,TF_B_TRADE_STATE_TRANS C,TD_B_PRODUCT D WHERE A.TRADE_ID=B.TRADE_ID_B(+) AND A.TRADE_ID=C.TRADE_ID(+) AND A.PRODUCT_ID = D.PRODUCT_ID(+) AND A.ACCEPT_MONTH = :B3 AND A.ACCEPT_DATE >= :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的描述完全一致。
观察错误语句的执行计划:

*** 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')<TRUNC(A.CANCEL_DATE,'DD') THEN DECODE(A.CANCEL_TAG,'3','0',A.CANCEL_TAG) ELSE CASE WHEN TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN DECODE(A.CANCEL_TAG,'3','0',A.CANCEL_TAG) ELSE DECODE(A.CANCEL_TAG,'1','0','4','0',A.CANCEL_TAG) END END END CANCEL_TAG, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_DATE END CANCEL_DATE, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_STAFF_ID END CANCEL_STAFF_ID, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_DEPART_ID END CANCEL_DEPART_ID, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_CITY_CODE END CANCEL_CITY_CODE, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_EPARCHY_CODE END 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,A.REMARK,IF_MAINTENANCE, F_SYS_GETVALUE('get_province_code',NULL,DECODE(LENGTH(A.EPARCHY_CODE),4,A.EPARCHY_CODE,A.TRADE_EPARCHY_CODE),NULL,NULL) PROVINCE_CODE, F_SYS_GETVALUE('get_province_code',NULL,A.TRADE_EPARCHY_CODE,NULL,NULL) TRADE_PROVINCE_CODE, F_SYS_GETVALUE('get_province_code',NULL,A.CANCEL_EPARCHY_CODE,NULL,NULL) CANCEL_PROVINCE_CODE, CASE WHEN C.NEW_STATE_CODE='H' THEN '9130' ELSE CASE WHEN C.OLD_STATE_CODE='H' THEN '9140' END END FROM TF_BH_TRADE A, (SELECT TRADE_ID_A,TRADE_ID_B FROM TF_B_TRADE_REL WHERE RELATION_TYPE_CODE='02' AND STATE IN ('0','A') )B,TF_B_TRADE_STATE_TRANS C,TD_B_PRODUCT D WHERE A.TRADE_ID=B.TRADE_ID_B(+) AND A.TRADE_ID=C.TRADE_ID(+) AND A.PRODUCT_ID = D.PRODUCT_ID(+) AND A.ACCEPT_MONTH = :B3 AND A.ACCEPT_DATE >= :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 ?============
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的方式,这种方式会带来性能的影响,如果这个错误频繁发生,也可以考虑进行设置。

「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...