一套Linux上的10.2.0.1系统出现ORA-00600:[15570]内部错误，日志如下:
Sat Jun 5 11:33:17 2010 Memory Notification: Library Cache Object loaded into SGA Heap size 2190K exceeds notification threshold (2048K) KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw== Sat Jun 5 14:57:25 2010 Thread 1 advanced to log sequence 16540 Current log# 3 seq# 16540 mem# 0: /ora_data/mantas/redo03.log Sat Jun 5 14:58:37 2010 Errors in file /opt/oracle/admin/mantas/udump/mantas_ora_10803.trc: ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], [] Sat Jun 5 14:58:37 2010 Errors in file /opt/oracle/admin/mantas/udump/mantas_ora_10903.trc: ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], [] Sat Jun 5 14:58:39 2010 Errors in file /opt/oracle/admin/mantas/udump/mantas_ora_10801.trc: ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], []
##23393_trc.rtf## /opt/oracle/admin/mantas/udump/mantas_ora_23393.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /opt/oracle System name: Linux Node name: mandbdev.ssfcu.inet Release: 2.6.9-55.ELsmp Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007 Machine: i686 Instance name: mantas Redo thread mounted by this instance: 1 Oracle process number: 32 Unix process pid: 23393, image: [email protected] *** SERVICE NAME:(mantas.ssfcu.inet) 2010-06-05 09:17:10.438 *** SESSION ID:(540.115) 2010-06-05 09:17:10.438 *** 2010-06-05 09:17:10.438 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], [] Current SQL statement for this session: SELECT t94.ACCT_INTRL_ID, t94.FO_TRXN_SEQ_ID, t94.TRXN_EXCTN_DT, t94.CASH_TRXN_ACTVY_RISK_NB, t94.CUST_INTRL_ID, t94.CUST_EFCTV_RISK_NB, t94.TRXN_BASE_AM, t94.D_HR_TRXN_AM, t94.D_LRF_TRXN_AM FROM (-- Only one part of UNION ALL will be executed -- threshold 'N' define which of them -- 'N' is parameter that indicates what account are included for customer focus to monitor : -- Y - cover only accounts for which a customer plays a primary role -- N - cover account over which a customer has a discretion -- PR-39791 singhan dated 03/23/09 -- Replaced (BUS_DAY_AGE = 0 and bus_day_fl='Y' ) with Clndr_Day_Age = 0 -- Included "Exclude Cash Transaction Report Exemption Accounts" filter using coalesce (a.CASH_RPT_EXMPT_FL, ' ')<>'Y' -- Date: 1st Feb 2010 : PR 42362 : Exclude cancelled and canceling transactions -- tr.CXL_PAIR_TRXN_INTRL_ID is null SELECT t.TRXN_BASE_AM , c.CUST_INTRL_ID , t.CASH_TRXN_ACTVY_RISK_NB , t.FO_TRXN_SEQ_ID , t.TRXN_EXCTN_DT , a.ACCT_INTRL_ID , CASE WHEN t.TRXN_BASE_AM-TRUNC(t.TRXN_BASE_AM,-4)=0 THEN t.TRXN_BASE_AM WHEN t.TRXN_ACTVY_AM-TRUNC(t.TRXN_ACTVY_AM,-4)=0 THEN t.TRXN_BASE_AM ELSE 0 END as d_LRF_TRXN_AM , c.CUST_EFCTV_RISK_NB , CASE WHEN t.CASH_TRXN_NTITY_RISK_NB>0 or t.CASH_TRXN_ACTVY_RISK_NB>0 THEN t.TRXN_BASE_AM ELSE 0 END D_HR_TRXN_AM FROM BUSINESS.CASH_TRXN t inner join BUSINESS.ACCT a on a.ACCT_INTRL_ID = t.ACCT_INTRL_ID inner join BUSINESS.CUST c on c.CUST_INTRL_ID=a.PRMRY_CUST_INTRL_ID WHERE -- Exclude cancelled and canceling transactions -- PR 42362 t.CXL_PAIR_TRXN_INTRL_ID is null --Exclude Exempted Customers and c.CUST_EFCTV_RISK_NB <> -2 -- Cover customers either from all jurisdiction or from Incl_Jurisdictions_Lst only and ('Y'='Y' or c.JRSDCN_CD in ('BUS')) -- Include Retail Customer Accounts Only and a.MANTAS_ACCT_HOLDR_TYPE_CD='CR' -- Cover only specific accounts and a.MANTAS_ACCT_BUS_TYPE_CD in ('RBK', 'RBR') -- and t.MANTAS_TRXN_PRDCT_CD in ('CURRENCY') and t.MANTAS_TRXN_PURP_CD = 'GENERAL' -- Cover either all transaction or only form the Incl_Trans_Src_Lst and ('Y'='Y' or t.SRC_SYS_CD in ('Inactive')) -- PR-39791 start -- Exclude Cash Transaction Report Exemption Accounts and coalesce (a.CASH_RPT_EXMPT_FL, ' ')<>'Y' and t.TRXN_EXCTN_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.TRXN_EXCTN_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL') and t.DATA_DUMP_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.DATA_DUMP_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL') -- PR-39791 start -- Parameter that indicates what account are included for customer focus to monitor : -- Y - cover only accounts for which a customer plays a primary role -- N - cover account over which a customer has a discretion and 'N' = 'Y' ----------------------------------------- UNION ALL ------------------------------------------ SELECT t.TRXN_BASE_AM , c.CUST_INTRL_ID , t.CASH_TRXN_ACTVY_RISK_NB , t.FO_TRXN_SEQ_ID , t.TRXN_EXCTN_DT , a.ACCT_INTRL_ID , CASE WHEN t.TRXN_BASE_AM-TRUNC(t.TRXN_BASE_AM,-4)=0 THEN t.TRXN_BASE_AM WHEN t.TRXN_ACTVY_AM-TRUNC(t.TRXN_ACTVY_AM,-4)=0 THEN t.TRXN_BASE_AM ELSE 0 END as d_LRF_TRXN_AM , c.CUST_EFCTV_RISK_NB , CASE WHEN t.CASH_TRXN_NTITY_RISK_NB>0 or t.CASH_TRXN_ACTVY_RISK_NB>0 THEN t.TRXN_BASE_AM ELSE 0 END d_HR_TRXN_AM FROM BUSINESS.CASH_TRXN t inner join BUSINESS.ACCT a on t.acct_intrl_id = a.acct_intrl_id inner join BUSINESS.CUST_ACCT ca on a.acct_intrl_id = ca.acct_intrl_id inner join BUSINESS.CUST c on ca.CUST_INTRL_ID = c.CUST_INTRL_ID inner join BUSINESS.CUST_ACCT_ROLE car on ca.cust_acct_role_cd = car.cust_acct_role_cd WHERE -- Exclude cancelled and canceling transactions -- PR 42362 t.CXL_PAIR_TRXN_INTRL_ID is null --Exclude Exempted Customers and c.CUST_EFCTV_RISK_NB <> -2 -- Cover customers either from all jurisdiction or from Incl_Jurisdictions_Lst only and ('Y'='Y' or c.JRSDCN_CD in ('BUS')) -- Include Retail Customer Accounts Only and a.MANTAS_ACCT_HOLDR_TYPE_CD='CR' -- Cover only specific accounts and a.MANTAS_ACCT_BUS_TYPE_CD in ('RBK', 'RBR') -- and t.MANTAS_TRXN_PRDCT_CD in ('CURRENCY') and t.MANTAS_TRXN_PURP_CD = 'GENERAL' -- Cover either all transaction or only form the Incl_Trans_Src_Lst and ('Y'='Y' or t.SRC_SYS_CD in ('Inactive')) -- PR-39791 start -- Exclude Cash Transaction Report Exemption Accounts and coalesce (a.CASH_RPT_EXMPT_FL, ' ')<>'Y' and t.TRXN_EXCTN_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.TRXN_EXCTN_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL') and t.DATA_DUMP_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.DATA_DUMP_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL') -- PR-39791 end -- Parameter that indicates what account are included for customer focus to monitor : -- Y - cover only accounts for which a customer plays a primary role -- N - cover account over which a customer has a discretion and 'N' = 'N' -- Account Customer Role will be viewed as disretionary and (car.trdng_auth_fl = 'Y' or car.wdrwl_auth_fl = 'Y' or car.poa_fl = 'Y') ) t94, RULE_MATCHER_TEMP910701_0 t106 WHERE t94.CUST_INTRL_ID = t106.FOCAL_ENTITY_ID ORDER BY t94.FO_TRXN_SEQ_ID ASC CALL STACK -------------- ksedst <- ksedmp <- ksfdmp <- kgeriv <- kgeasi <- qerpxFetch <- opifch2 <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- main <- libc_start_main PROCESS STATE ------------------- SO: 0x3ed2a160, type: 4, owner: 0x3f7e28f4, flag: INIT/-/-/0x00 (session) sid: 540 trans: (nil), creator: 0x3f7e28f4, flag: (8000041) USR/- BSY/-/-/-/-/- DID: 0001-0020-00000265, short-term DID: 0000-0000-00000000 txn branch: (nil) oct: 3, prv: 0, sql: 0x36e5e194, psql: 0x36c493d8, user: 65/KDD_ALG O/S info: user: mantas, term: , ospid: 26740, machine: manappdev program: mantas@manappdev (TNS V1-V3) application name: mantas@manappdev (TNS V1-V3), hash value=0 last wait for 'PX Deq: Execute Reply' blocking sess=0x(nil) seq=1052 wait_time=150 seconds since wait started=0 sleeptime/senderid=c8, passes=1, =0 Dumping Session Wait History for 'PX Deq: Execute Reply' count=1 wait_time=150 sleeptime/senderid=c8, passes=1, =0 for 'PX Deq: Execute Reply' count=1 wait_time=21 sleeptime/senderid=c8, passes=2, =0 for 'PX Deq: Execute Reply' count=1 wait_time=2 sleeptime/senderid=c8, passes=1, =0 for 'PX Deq: Execute Reply' count=1 wait_time=3 sleeptime/senderid=a, passes=1, =0 for 'PX Deq: Execute Reply' count=1 wait_time=165 sleeptime/senderid=c8, passes=1, =0 for 'PX Deq Credit: send blkd' count=1 wait_time=1918048 sleeptime/senderid=10010002, passes=1, qref=0 for 'db file sequential read' count=1 wait_time=4045 file#=1e, block#=5681, blocks=1 for 'db file sequential read' count=1 wait_time=13290 file#=1e, block#=7a99, blocks=1 for 'db file sequential read' count=1 wait_time=5331 file#=1e, block#=c4, blocks=1 for 'PX Deq: Execute Reply' count=1 wait_time=21 sleeptime/senderid=c8, passes=1, =0 temporary object counter: 0 ============ Plan Table ============ ---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop | ---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+ | 0 | SELECT STATEMENT | | | | 67 | | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10004 | 12 | 1884 | 67 | 00:00:01 |:Q1004| P->S |QC (ORDER) | | | | 3 | SORT ORDER BY | | 12 | 1884 | 67 | 00:00:01 |:Q1004| PCWP | | | | | 4 | PX RECEIVE | | 12 | 1884 | 66 | 00:00:01 |:Q1004| PCWP | | | | | 5 | PX SEND RANGE | :TQ10003 | 12 | 1884 | 66 | 00:00:01 |:Q1003| P->P |RANGE | | | | 6 | BUFFER SORT | | 12 | 1884 | | |:Q1003| PCWP | | | | | 7 | NESTED LOOPS | | 12 | 1884 | 66 | 00:00:01 |:Q1003| PCWP | | | | | 8 | BUFFER SORT | | | | | |:Q1003| PCWC | | | | | 9 | PX RECEIVE | | | | | |:Q1003| PCWP | | | | | 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P |BROADCAST | | | | 11 | TABLE ACCESS FULL | RULE_MATCHER_TEMP910701_0| 82 | 820 | 3 | 00:00:01 | | | | | | | 12 | VIEW | | 1 | 147 | | |:Q1003| PCWP | | | | | 13 | UNION ALL PUSHED PREDICATE | | | | | |:Q1003| PCWP | | | | | 14 | FILTER | | | | | |:Q1003| PCWC | | | | | 15 | TABLE ACCESS BY LOCAL INDEX ROWID | CASH_TRXN | 1 | 79 | 3 | 00:00:01 |:Q1003| PCWC | | | | | 16 | NESTED LOOPS | | 1 | 140 | 7 | 00:00:01 |:Q1003| PCWP | | | | | 17 | BUFFER SORT | | | | | |:Q1003| PCWC | | | | | 18 | PX RECEIVE | | | | | |:Q1003| PCWP | | | | | 19 | PX SEND BROADCAST | :TQ10001 | | | | | | S->P |BROADCAST | | | | 20 | NESTED LOOPS | | 1 | 61 | 3 | 00:00:01 | | | | | | | 21 | TABLE ACCESS BY INDEX ROWID | CUST | 1 | 27 | 1 | 00:00:01 | | | | | | | 22 | INDEX UNIQUE SCAN | PK_CUST | 1 | | 1 | 00:00:01 | | | | | | | 23 | TABLE ACCESS BY INDEX ROWID | ACCT | 1 | 34 | 2 | 00:00:01 | | | | | | | 24 | INDEX RANGE SCAN | ACCT_CUST_IDX | 4 | | 1 | 00:00:01 | | | | | | | 25 | PX PARTITION RANGE ITERATOR | | 1 | | 2 | 00:00:01 |:Q1003| PCWC | | KEY | KEY | | 26 | INDEX RANGE SCAN | CASH_ACCTID_IDX | 1 | | 2 | 00:00:01 |:Q1003| PCWP | | KEY | KEY | | 27 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 |:Q1003| PCWP | | | | | 28 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 |:Q1003| PCWP | | | | | 29 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | | | 30 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | | | 31 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | | | 32 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | | | 33 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | | | 34 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | | | 35 | TABLE ACCESS BY LOCAL INDEX ROWID | CASH_TRXN | 1 | 79 | 3 | 00:00:01 |:Q1003| PCWC | | 1 | 1 | | 36 | NESTED LOOPS | | 1 | 173 | 18 | 00:00:01 |:Q1003| PCWP | | | | | 37 | BUFFER SORT | | | | | |:Q1003| PCWC | | | | | 38 | PX RECEIVE | | | | | |:Q1003| PCWP | | | | | 39 | PX SEND BROADCAST | :TQ10002 | | | | | | S->P |BROADCAST | | | | 40 | NESTED LOOPS | | 5 | 470 | 5 | 00:00:01 | | | | | | | 41 | NESTED LOOPS | | 5 | 350 | 4 | 00:00:01 | | | | | | | 42 | NESTED LOOPS | | 5 | 290 | 2 | 00:00:01 | | | | | | | 43 | TABLE ACCESS BY INDEX ROWID | CUST | 1 | 27 | 1 | 00:00:01 | | | | | | | 44 | INDEX UNIQUE SCAN | PK_CUST | 1 | | 1 | 00:00:01 | | | | | | | 45 | INDEX RANGE SCAN | PK_CUST_ACCT | 5 | 155 | 1 | 00:00:01 | | | | | | | 46 | TABLE ACCESS BY INDEX ROWID | CUST_ACCT_ROLE | 1 | 12 | 1 | 00:00:01 | | | | | | | 47 | INDEX UNIQUE SCAN | PK_CUST_ACCT_ROLE | 1 | | 1 | 00:00:01 | | | | | | | 48 | TABLE ACCESS BY INDEX ROWID | ACCT | 1 | 24 | 1 | 00:00:01 | | | | | | | 49 | INDEX UNIQUE SCAN | PK_ACCT | 1 | | 1 | 00:00:01 | | | | | | | 50 | PX PARTITION RANGE ITERATOR | | 1 | | 2 | 00:00:01 |:Q1003| PCWC | | KEY | KEY | | 51 | INDEX RANGE SCAN | CASH_ACCTID_IDX | 1 | | 2 | 00:00:01 |:Q1003| PCWP | | KEY | KEY | | 52 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 |:Q1003| PCWP | | | | | 53 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 |:Q1003| PCWP | | | | | 54 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | | | 55 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | | | 56 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | | | 57 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | | | 58 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | | | 59 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | | ---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
该问题提交了SR，MOS给出的回复:”This is high likely caused by bug 4611578,bug 4611578 is complete fixed in 8397251.Parallel Execution plan shows a UNION ALL under a nested loop row source that is the bug pattern”。在使用并行的情况下存在UNION ALL PUSHED PREDICATE以合并某些由NESTED LOOP获得的数据源是引发该4611578 bug的典型情况。 Oracle GCS建议通过修改PUSHED PREDICATE的相关隐式参数来避免谓词前推的发生:
Please try if one of the following workaround help: - alter session set "_optimizer_push_pred_cost_based" = false; or - alter session set "_push_join_union_view" = false; or - use hint NO_PUSH_PRED) I can get backport for bug 8397251 on top of 10.2.0.4 10.2.0.5 See Doc 209768.1 and there section 5.2.2 The parameter _push_join_union_view chan switch off a optimization for queries with UNION ALL and joins, so all queries with this pattern can be affected, the better workaound is here to switch off parallelism.
设置隐式参数_push_join_union_view为false将禁用union all和join操作的pushed predicate特性，可能会影响到系统中其他SQL的性能；MOS认为最好的workaround方式是取消查询的并行度。这个case最后通过加入NO_PARALLEL提示解决了。
Leave a Reply