设为首页收藏本站
查看: 95|回复: 0

[PHP] 解决ORA-00600:internalerrorcode,arguments:[kcblasm_1],[1

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2018-7-3 17:02:00 | 显示全部楼层 |阅读模式
    解决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 [B]= B.QC AND THRQ [B]
    如上极其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
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表