- 浏览: 4368958 次
- 性别:
- 来自: 厦门
文章分类
- 全部博客 (634)
- Oracle日常管理 (142)
- Oracle体系架构 (45)
- Oracle Tuning (52)
- Oracle故障诊断 (35)
- RAC/DG/OGG (64)
- Oracle11g New Features (48)
- DataWarehouse (15)
- SQL, PL/SQL (14)
- DB2日常管理 (9)
- Weblogic (11)
- Shell (19)
- AIX (12)
- Linux/Unix高可用性 (11)
- Linux/Unix日常管理 (66)
- Linux桌面应用 (37)
- Windows (2)
- 生活和工作 (13)
- 私人记事 (0)
- Python (9)
- CBO (15)
- Cognos (2)
- ORACLE 12c New Feature (2)
- PL/SQL (2)
- SQL (1)
- C++ (2)
- Hadoop大数据 (5)
- 机器学习 (3)
- 非技术 (1)
最新评论
-
di1984HIT:
xuexilee!!!
Oracle 11g R2 RAC高可用连接特性 – SCAN详解 -
aneyes123:
谢谢非常有用那
PL/SQL的存储过程和函数(原创) -
jcjcjc:
写的很详细
Oracle中Hint深入理解(原创) -
di1984HIT:
学习了,学习了
Linux NTP配置详解 (Network Time Protocol) -
avalonzst:
大写的赞..
AIX内存概述(原创)
前阵子生产库上发生了一次ora-00600错误,这里简要记录分析下,如有错误,希望大家给以建议。
报错信息
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []
查看告警日志有如下信息
Mon Aug 29 16:46:08 GMT+08:00 2011Errors in file /oracle10g/app/oracle/admin/zgscdb/bdump/
zgscdb2_j003_14024898.trc:
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []
Mon Aug 29 16:47:16 GMT+08:00 2011Trace dumping is performing id=[cdmp_20110829164716]
Mon Aug 29 17:07:31 GMT+08:00 2011Thread 2 advanced to log sequence 4710 (LGWR switch)
Current log# 16 seq# 4710 mem# 0: /dev/rlv_zs_redo2_8_1
Current log# 16 seq# 4710 mem# 1: /dev/rlv_zs_redo2_8_2
查看 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.QM + 1 AND
JKPZZL_DM
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 <
B.QM
+ 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
参考至:http://blog.sina.com.cn/s/blog_70b55b1a0100t3l3.html
http://blog.chinaunix.net/space.php?uid=20656672&do=blog&id=1744626
http://www.eygle.com/archives/2010/11/ora-600_kcblasm_1.html
http://space.itpub.net/16572356/viewspace-687014
http://space.itpub.net/354732/viewspace-697070
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
发表评论
-
AIX平台下磁盘的PVID对ASM磁盘的破坏
2014-03-19 20:53 2700这篇文章将通过两篇MOS文章来讨论AIX平台下为磁盘分配 ... -
Bug 9020054,ORA-8103 BEING HIT DURING GATHERING OF STATISTICS ON TABLE PARTITION
2013-12-01 09:22 1829Bug 9020054 : ORA-8103 ... -
oracle数据库hanganalyze(原创)
2013-06-23 14:11 8239为什么要使用hanganalyzeOracle 数据库“真 ... -
Oracle:并行操作为什么无法执行(老白)
2013-06-23 10:30 5663在一次系统割接的时候,我们碰到一个十分奇怪的现象。由于进行 ... -
补写的2小节DBA日记
2013-06-05 21:52 13556月8日 ITL等待引发的RAC性能问题 从这几天的情况 ... -
ORA-27054故障排除
2013-03-08 17:57 12844在数据备份过程中,由于目标是使用NFS文件系统,因此在导入 ... -
长时间latch free等待——记一次系统异常的诊断过程
2013-01-09 19:17 3636今天发现一个报表数据库中SQL运行异常,简单记录一下问题的诊断 ... -
ORA-15063: ASM discovered an insufficient number of disks for diskgroup(原创)
2012-11-25 16:59 13014ORA-15063: ASM discovered an in ... -
libpthread.so.0: cannot open shared object file解决方法(原创)
2012-11-24 17:33 17630在linux 5上装10G RAC时,常常会碰到“libpth ... -
ora-02020故障诊断详解(原创)
2012-07-16 12:54 3101ORA-2020错发生在一个分布式事务使用的dblink数超过 ... -
Oracle数据库CPU 100%故障诊断实例(原创)
2012-07-05 13:55 16850前言 这两天一只对外提供查询的数据库CPU使用率 ... -
DBA手记:System State转储分析之问题定位
2012-04-19 22:20 2066在 Oracle 数据库的运行过程中,可能会因为一些异常遇 ... -
ORA-02050故障诊断一例
2012-04-05 17:20 8497昨天客户反映说在下午某时间段有几个事务失败了,让我查下当时数据 ... -
ORA-00308: cannot open archived log(原创)
2012-03-23 09:36 16140笔者在为客户配置DG时发现主备库日志无法正常传输,经仔细检查后 ... -
ORA-00600: internal error code, arguments: [kcblasm_1], [103]原创
2012-03-23 09:36 3056故障报错 Mon Mar 19 11:30:03 GMT ... -
ORA-00600: internal error code, arguments: [kglhdda-bad-free](原创)
2012-03-22 09:16 2927故障报错如下 Thu Mar 15 09:51:29 G ... -
ORA-27300,ORA-27301,ORA-27302: failure occurred at: skgpalive1(原创)
2012-03-22 08:58 3299故障报错如下 Wed Mar 07 16:4 ... -
ora-07445错误相关内容
2012-03-01 17:14 1756本文档主要介绍ora-07445 错误相关内容,并给出了对这 ... -
记一次Oracle 生产库还原归档日志经历(原创)
2012-02-17 10:12 4323中午刚去吃饭,就接到同事电话说急着要恢复生产库上的归档日志。系 ... -
SMON: Parallel transaction recovery tried 引发的问题
2012-01-04 11:26 2338SMON: Parallel transaction rec ...
相关推荐
NULL 博文链接:https://gembler.iteye.com/blog/346242
Patch 8922013: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434]
在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [13], [1990],这是我自己整理的,已经试验过了
ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[] TueAug1209:20:17CST2014 Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29974.trc: ORA-00600:internalerrorcode,arguments...
ora-01033:oracle initialization or shutdown in progress 解决方法 ora-01033:oracle initialization or shutdown in progress 解决方法 ora-01033:oracle initialization or shutdown in progress 解决方法 ora-...
-- 解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 的文件包....... -- 解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 的文件包....... -- 解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 的文件包....... ...
在运行查询SELECT * FROM V$SESSION 会出现ORA-29275:部分多字节字符的错误,这是什么原因开始我不得其解,网上也没有介绍什么好办法。本文给出答案。
ORA-32001:write to spfile requested but no spfile is in use请求写入spfile,但没有使用spfile的解决方法 在输入以下语句中报了这样的错误: SQL>alter system set control_files=’/u01/app/oracle/oradata/prod/...
调整参数后引起,ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3 2768M 的解决方案
ORA-12154: TNS: 无法解析指定的连接标识符的解决方法
ORA-00031:标记要终止的会话解决方案; ORA-00031:标记要终止的会话解决方案;
ORA-01036:非法的变量名/编号 oracle特有的错误
oracle12c程序连接时异常: ORA-01017: 用户名/口令无效; 登录被拒绝 的解决方案。
ORA-00603: ORACLE server session terminated by fatal error ORA-27504: IPC error creating OSD context ORA-27300: OS system dependent operation:sskgxp_select failed with status: 3 ORA-27301: OS ...
oracle19c缺少的函数
解决ORACLE TNS: 无法解析指定的连接标识符; 里面有步骤
oracle 12 c ORA-01017: 用户名/口令无效; 登录被拒绝
NULL 博文链接:https://3w1h.iteye.com/blog/1211779
oracle报错ora-12541:TNS无监听程序