解决ORA-00600: internal error code, arguments: [kcblasm_1], [1

解决ORA-00600: internal error code, arguments: [kcblasm_1], [103]

前阵子生产库上发生了一次ora-00600错误，这里简要记录分析下，如有错误，希望大家给以建议。

报错信息

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

查看告警日志有如下信息

查看 zgscdb2_j003_14024898.trc

When executing a statement using a SELECT getting:
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

The execution plan from the ORA-600 trace file is showing hash join is used.

Call Stack is including:

kcblasm1 kcblasm kxhfFndFreeSlot kxhfNewBuffer qerhjGetNewBuffer ksxb1bqb kxhrPack

INSERT INTO BB_KJ_B00 WITH KJ_JZRQ_TEMP AS (SELECT MIN(QC) QC,TO_DATE(:B2 , 'YYYY-MM-DD') QM, :B1 JG FROM KJ_JZRQ WHERE KJND = (SELE
CT KJND FROM KJ_JZRQ WHERE TO_DATE(:B2 , 'YYYY-MM-DD') BETWEEN QC AND QM) ) SELECT :B1 SJ_SWJG_DM,ZSXM_DM DM,SUM(SE) SE,SUM(ZYSE) ZY
SE,SUM(SSSE) SSSE,SUM(DSSE) DSSE,SUM(XQSE) XQSE,SUM(XZSE) XZSE,TO_DATE(:B2 , 'YYYY-MM-DD') BBQ,'SQL2' BZ FROM ( SELECT A.ZSXM_DM, SE
, A.ZYSE, A.SSSE, A.DSSE, A.XQSE, A.XZSE, RKRQ, D.SJ_SWJG_DM FROM V_KJ_SB_ZSXX_TIPS A, KJ_JZRQ_TEMP B, DM_ZSXM C, KJ_ZW_HSDW D WHERE
A.SKSS_SWJG_DM = D.SS_SWJG_DM AND A.ZSXM_DM = C.ZSXM_DM AND D.SJ_SWJG_DM = B.JG AND RKRQ >= B.QC AND RKRQ IN (SELECT PZZL_DM FROM DM_PZZL WHERE JKS_XYBZ = 'Y' OR WSZ_XYBZ = 'Y') UNION ALL SELECT A.ZSXM_DM, -SE, -A.ZYSE, -A.SSSE, -A.DSSE,
-A.XQSE, -A.XZSE, THRQ, D.SJ_SWJG_DM FROM V_KJ_SB_TTXX_TIPS A, KJ_JZRQ_TEMP B, DM_ZSXM C, KJ_ZW_HSDW D, DM_YSFPBL L WHERE A.SKSS_SWJ
G_DM = D.SS_SWJG_DM AND D.SJ_SWJG_DM = B.JG AND A.YSFPBL_DM = L.YSFPBL_DM AND A.ZSXM_DM = C.ZSXM_DM AND THRQ >= B.QC AND THRQ + 1 ) GROUP BY ZSXM_DM

如上极其BT的insert select造成了这个错误，，数据库版本是10.2.0.5

在10.2.0.5版本中，所有平台环境下补丁程序P7612454，该补丁是解决hash
join时候，Direct IO最大限制4096，我们从执行计划中可以看出,hash join的build
table表的cardinality非常大，这个是造成该问题的罪魁祸首。解决方案如下：
1、11.2版本解决了上述问题
2、升级补丁P7612454，该补丁替换lib中的kcbl.o文件
3、如果执行计划中是hash join造成的，在会话层中设置"_hash_join_enable"
=false，如果执行计划是hash group by 造成的，设置"_gby_hash_aggregation_
enable"=false，在 相应的SQL前加execute immediate 'alter session set "_hash_join_enabled" = false'亦可;

4、修改SQL语句，尽量减少build table的cardinality的值，可以避免该问题的生成

METALINK上的相应资料

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.5.0 and later [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms
When executing a statement using a SELECT getting:
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []
The execution plan from the ORA-600 trace file is showing hash join is used.
Call Stack is including:
kcblasm1 kcblasm kxhfFndFreeSlot kxhfNewBuffer qerhjGetNewBuffer ksxb1bqb kxhrPack
Cause
The issue was investigated in:
Bug 9800302 - 10.2.0.5 GETTING ORA-00600 [KCBLASM_1]
closed as duplicate of:Bug 7612454 - Abstract: DSS:PERF REGRESSIONS IN SERIAL DIRECT READS fixed in 11.2.
As per development team the number of slots available for direct I/Os (limited to 4096) forced the hash-join algorithm to operate on fewer number of slots and resulted in more spills to disk. This caused:
direct path IO to perform worse in 10.2.0.5 than earlier releases with more "direct path read" operations or
ORA-600 [kcblasm_1] errors.

Solution
1. Upgrade the database to 11.2.
OR
2. Apply &incFamilyProds=false&flag=search))" target=_blank>Patch 7612454 available on MOS. If a patch is not currently available on top of your database version and/or platform please raise a Service Request to request for it.
Please be sure that your database version qualifies for getting a new patch as per Note 209768.1 and Note 742060.1.
OR
3. Use the workaround of setting:
"_hash_join_enabled"= false

References
BUG:9781592 - ORA-600 [KCBLASM_1] [103] DURING HASH JOIN QUERY USE ON DATABASE IN DWH
BUG:9800302 - 10.2.0.5 GETTING ORA-00600 [KCBLASM_1]
BUG:9804132 - INSERT FAILS WITH ORA-600 [KCBLASM_1], [103]
NOTE:209768.1 - Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy
NOTE:742060.1 - Release Schedule of Current Database Releases
NOTE:7612454.8 - Bug 7612454 - More "direct path read" operations / OERI:kcblasm_1