ORACLE ORA-00600之[kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1]

清風艾艾發表於2016-12-19
    近期的資料庫巡檢中,發現一oracle rac資料庫的一個節點發生ORA-00600 [kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1]告警,從trc檔案中定位到一條與分割槽表查詢相關的sql語句,本案
例的處理過程涉及到修改一個隱含引數_full_pwise_join_enabled,查詢MOS,與ORA-00600 [kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1]告警相關的BUG很多,主要有Bug13722997(參考13722997.8)、Bug17164360/17185558(參考17164360.8/17185558.8)、Bug9929660(參考9929660.8)詳細的處理分析過程如下。
    作業系統:RedHat RHEL6.6
    資料庫:Oracle 11.2.0.4 RAC

    節點orcl1的ORA600告警,告警資訊如下:
14/12/2016 09:20:27 Use ADRCI or Support Workbench to package the incident. 
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_64283/orcl1_ora_26060_i64283.trc 
ORA-00600: internal error code, arguments: [kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1], [], [], [], [], [] 
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_26060.trc (incident=64283): 
Wed Dec 14 09:20:27 2016 
See Note 411.1 at My Oracle Support for error and packaging details.
    orcl1_ora_26060_i64283.trc檔案核心錯誤資訊如下:
Dump file /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_64283/orcl1_ora_26060_i64283.trc 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
ORACLE_HOME = /u01/app/oracle/product/11.2.0 
System name: Linux 
Node name: rac01 
Release: 4.1.12-37.4.1.el6uek.x86_64 
Version: #2 SMP Tue May 17 07:23:38 PDT 2016 
Machine: x86_64 
Instance name: orcl1 
Redo thread mounted by this instance: 1 
Oracle process number: 35 
Unix process pid: 26060, image: oracle@rac01 
*** 2016-12-14 09:20:27.349 
*** SESSION ID:(2282.25537) 2016-12-14 09:20:27.349 
*** CLIENT ID:() 2016-12-14 09:20:27.349 
*** SERVICE NAME:(orcl) 2016-12-14 09:20:27.349 
*** MODULE NAME:() 2016-12-14 09:20:27.349 
*** ACTION NAME:() 2016-12-14 09:20:27.349 
Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_26060.trc 
ORA-00600: internal error code, arguments: [kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1], [], [], [], [], [] 
========= Dump for incident 64283 (ORA 600 [kkoipt:incorrect pwj]) ======== 
*** 2016-12-14 09:20:27.349 
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) 
----- Current SQL Statement for this session (sql_id=75gnknpgwrw3j) ----- 
select * 
from (select row_.*, rownum NumRow from ( 
select /*+FIRST_ROWS(100)*/ 
b.bill_no as BillNo, 
round(b.TOTAL_AMOUNT, 2) as TOTAL_AMOUNT, 
b.admission_date as AdmissionDate, 
b.discharge_date as DischargeDate, 
b.hospital_name as HospitalName, 
a.reason as Reason, 
b.DISCHARGE_DISEASE_NAME as DischargeDiseaseName, 
ex.varchar01 as Department 
from dw_bill b 
left join dw_bill_ex ex 
on ex.table_par=b.table_par and b.hisid = ex.billid 
left join ad_auditresult a 
on a.claim_id = b.id 
and a.table_par = b.table_par 
where 1=1 and a.table_par >= '20161101' and a.table_par <= '20161214' and a.rule_code='150802' and b.claim_type in (:paramClaimTypes0, :paramClaimTypes1, :paramClaimTypes2) and b.benefit_type in (:paramBenefitTypes0, :paramBenefitTypes1) order by b.bill_no ) row_ where rownum <= 100) 
where NumRow > 0;
    根據Bug13722997相關文章13722997.8的提示,需要對sql語句做修改,就是分割槽表不使用別名:
Description
    A join query on reference partitioned tables with aliases may raise an internal error.
Workaround
    do not use aliases
但是,取消掉分割槽表的別名,無論是使用sys還是業務使用者,sql語句一旦執行立即報錯退出,報錯與當前報錯相同:
ORA-00600: internal error code, arguments: [kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1], [], [], [], [], [] 
    根據Bug9929660(參考9929660.8)的提示,要修改sql語句的並行執行度,但是sql語句相關的分割槽表上並沒有並行,也沒有並行相關的hints,因此排除Bug9929660
Description
Incorrect Join information not invalidated properly in the case of null aware antijoin queries.
Rediscovery Notes:
ORA-600 [kkoipt:incorrect pwj] from Parallel Query in 11.2
Workaround
 Change the degree of parallelism so that optimizer do not choose plan  involving partition wise join.
 Do not let the degree being set exactly match the number of  partitions/subpartitions of the tables involved  in the join to help avoid this error.
    根據B
UG 17185558/17164360的提示需要修改資料庫隱含引數_full_pwise_join_enabled(修改該引數並不需要重啟資料庫,但是注意將引數修改寫到spfile,保證資料庫重啟後也生效),
引數修改後,重新執行sql語句,報錯不再出現:
Description
This bug is only relevant when using Partitioned Tables Rediscovery Notes  ORA-600 [kkoipt:incorrect pwj] may occur while querying partitioned table. 
The error occurs during the PARSE phase, when the optimizer is evaluating  the join order.    
Workaround
  Set "_full_pwise_join_enabled"=false and re-parse the query.
在執行SQL_ID為75gnknpgwrw3j的會話或資料庫級別關閉隱含引數"_full_pwise_join_enabled"
SYS執行命令:ALTER SYSTEM SET _full_pwise_join_enabled=FALSE SCOPE=BOTH SID='*'; 
告警日誌輸出提示:
Fri Dec 16 15:15:46 2016 
ALTER SYSTEM SET _full_pwise_join_enabled=FALSE SCOPE=BOTH SID='*'; 
引數"_full_pwise_join_enabled"修改後,執行75gnknpgwrw3j不再報錯ORA600
 [oracle@rac02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 16 15:19:01 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select count(*) from (
  2   select *
  3    from (select row_.*, rownum NumRow
  4            from (select /*+FIRST_ROWS(100)*/
  5                   dw_bill.bill_no as BillNo,
  6                   round(dw_bill.TOTAL_AMOUNT, 2) as TOTAL_AMOUNT,
  7                   dw_bill.admission_date as AdmissionDate,
  8                   dw_bill.discharge_date as DischargeDate,
  9                   dw_bill.hospital_name as HospitalName,
 10                   ad_auditresult.reason as Reason,
 11                   dw_bill.DISCHARGE_DISEASE_NAME as DischargeDiseaseName,
 12                   dw_bill_ex.varchar01 as Department
 13                    from bmi.dw_bill 
 14                    left join bmi.dw_bill_ex
 15                      on dw_bill_ex.table_par = dw_bill.table_par
 16                     and dw_bill.hisid = dw_bill_ex.billid
 17                    left join bmi.ad_auditresult
 18                      on ad_auditresult.claim_id = dw_bill.id
 19                     and ad_auditresult.table_par = dw_bill.table_par
 20                   where 1 = 1
 21                     and ad_auditresult.table_par >= '20161101'
 22                     and ad_auditresult.table_par <= '20161214'
 23                     and ad_auditresult.rule_code = '150802'
 24                     and dw_bill.claim_type in (2,
 25                                          3,
 26                                          1)
 27                     and dw_bill.benefit_type in
 28                         ('01','02')
 29                   order by dw_bill.bill_no) row_
 30           where rownum <= 100)
 31   where NumRow > 0);
  COUNT(*)
----------
       100
SQL> conn bmi/BMI
Connected.
SQL> select count(*) from (
  2   select *
  3    from (select row_.*, rownum NumRow
  4            from (select /*+FIRST_ROWS(100)*/
  5                   dw_bill.bill_no as BillNo,
  6                   round(dw_bill.TOTAL_AMOUNT, 2) as TOTAL_AMOUNT,
  7                   dw_bill.admission_date as AdmissionDate,
  8                   dw_bill.discharge_date as DischargeDate,
  9                   dw_bill.hospital_name as HospitalName,
 10                   ad_auditresult.reason as Reason,
 11                   dw_bill.DISCHARGE_DISEASE_NAME as DischargeDiseaseName,
 12                   dw_bill_ex.varchar01 as Department
 13                    from bmi.dw_bill 
 14                    left join bmi.dw_bill_ex
 15                      on dw_bill_ex.table_par = dw_bill.table_par
 16                     and dw_bill.hisid = dw_bill_ex.billid
 17                    left join bmi.ad_auditresult
 18                      on ad_auditresult.claim_id = dw_bill.id
 19                     and ad_auditresult.table_par = dw_bill.table_par
 20                   where 1 = 1
 21                     and ad_auditresult.table_par >= '20161101'
 22                     and ad_auditresult.table_par <= '20161214'
 23                     and ad_auditresult.rule_code = '150802'
 24                     and dw_bill.claim_type in (2,
 25                                          3,
 26                                          1)
 27                     and dw_bill.benefit_type in
 28                         ('01','02')
 29                   order by dw_bill.bill_no) row_
 30           where rownum <= 100)
 31   where NumRow > 0);
  COUNT(*)
----------
       100

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

相關文章