ORACLE ORA-00600之[kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1]
近期的資料庫巡檢中，發現一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告警，告警資訊如下：
例的處理過程涉及到修改一個隱含引數_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.
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；
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.
根據BUG 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.
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.
根據BUG 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='*';
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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORA-00600: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []OBJ
- ORA-00600:: [ttcgcshnd-1], [0], [], [], [], [], [], []GC
- raid1+0 and raid0+1AI
- Opening socket connection to server localhost/0:0:0:0:0:0:0:1:2181. Will not attempt to authenticateServerlocalhost
- 搞定request.getRemoteAddr()獲取的值為0:0:0:0:0:0:0:1REM
- HttpServletRequest呼叫getRemoteAddr獲取IP返回0:0:0:0:0:0:0:1的問題HTTPServletREM
- ORA-00600: internal error code, arguments: [2103], [1], [0], [1], [900], [], [],Error
- MQTT QoS 0, 1, 2 介紹MQQT
- Oracle 11G OCP 1Z0-053 2Oracle
- linux $# $0 $1 $2 $@ $* $$ $?的含義Linux
- Oracle OCP 1Z0-053 (ASM)OracleASM
- 記一次ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []ErrorOBJ
- RAID 1+0和RAID 0+1的區別AI
- Oracle 11G OCP 1Z0-053 1Oracle
- Raid Raid0 Raid1 Raid5 Raid0+1 Raid1+0AI
- Oracle 1Z0-149 題庫Oracle
- kewastUnPackStats(): bad magic 1 (0x0000000022607DF0, 0)AST
- [譯] Flutter 從 0 到 1Flutter
- takes 0 positional arguments but 1 was given
- 淺談0/1切換
- raid 0 1 5 概述AI
- shell中變數$#,$@,$0,$1,$2的含義變數
- Oracle OCP 1Z0 053 Q650Oracle
- Oracle OCP 1Z0 053 Q324Oracle
- Oracle OCP 1Z0 053 Q27Oracle
- Oracle Certificate Profession 1Z0-051-28Oracle
- Oracle Certificate Profession 1Z0-051-22Oracle
- Oracle Certificate Profession 1Z0-051-27Oracle
- Oracle Certificate Profession 1Z0-051-25Oracle
- Oracle Certificate Profession 1Z0-051-26Oracle
- Oracle Certificate Profession 1Z0-051-29Oracle
- Oracle Certificate Profession 1Z0-051-20Oracle
- Oracle Certificate Profession 1Z0-051-16Oracle
- Oracle Certificate Profession 1Z0-051-14Oracle
- Oracle Certificate Profession 1Z0-051-19Oracle
- Oracle Certificate Profession 1Z0-051-12Oracle
- Oracle Certificate Profession 1Z0-051-03Oracle
- Oracle Certificate Profession 1Z0-051-04Oracle