sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-00600 [kkslgbv0]产生原因和解决办法

时间:2016-11-15 10:06   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映在执行计划的时候遇到Oracle异常,alert日志中出现报错ora-00600 [kkslgbv0],结合MOS分析原因为BUG导致。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: ora-00600 [kkslgbv0]

中午某客户来电话说,其中一套rac(asm)的alert log中报600错误,将信息发过来,经过分析确认如下:
Thu Jul  7 10:41:08 2011
Errors in file /oracle/admin/cmsdb3/udump/cmsdb32_ora_23186.trc:
ORA-00600: internal error code, arguments: [kkslgbv0], [], [], [], [], [], [], []
Thu Jul  7 10:41:09 2011
Trace dumping is performing id=[cdmp_20110707104109]
Thu Jul  7 10:43:01 2011
Errors in file /oracle/admin/cmsdb3/udump/cmsdb32_ora_15270.trc:
ORA-00600: internal error code, arguments: [kkslgbv0], [], [], [], [], [], [], []
Thu Jul  7 10:43:02 2011
Trace dumping is performing id=[cdmp_20110707104302]
Thu Jul  7 11:25:28 2011
Thread 2 advanced to log sequence 5757
  Current log# 4 seq# 5757 mem# 0: +DG_DATA/cmsdb3/onlinelog/redo04_1.log
  Current log# 4 seq# 5757 mem# 1: +DG_DATA/cmsdb3/onlinelog/redo04_2.http://www.oracleplus.netlog
Thu Jul  7 11:50:00 2011
Errors in file /oracle/admin/cmsdb3/udump/cmsdb32_ora_12454.trc:
ORA-00600: internal error code, arguments: [kkslgbv0], [], [], [], [], [], [], []
Thu Jul  7 11:50:01 2011
Trace dumping is performing id=[cdmp_20110707115001]
trace cmsdb32_ora_23186.trc 部分信息如下:
*** SERVICE NAME:(cmsdb3) 2011-07-07 10:41:08.491
*** SESSION ID:(767.22) 2011-07-07 10:41:08.491
*** 2011-07-07 10:41:08.491
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkslgbv0], [], [], [], [], [], [], []
Current SQL statement for this session:
select t2.* from tpllib t2 where t2.deleteflag=:"SYS_B_0" and t2.type=:1

and t2.tplgroupid  ====== 省略部分业务sql ======

----- Call Stack Trace -----
calling              call     entry                argument values in hex    
location             type     point                (  means dubious value)   
-------------------- -------- -------------------- ----------------------------
ksedmp()+744         CALL     ksedst()             000000840 
                                                   FFFFFFFF7FFF850C 
                                                   000000000 
                                                   FFFFFFFF7FFF5000 
                                                   FFFFFFFF7FFF3D68 
                                                   FFFFFFFF7FFF4768 
kgerinv()+200        PTR_CALL 0000000000000000     000106400   10652D364 
                                                   10652D000   00010652D 
                                                   000106400   10652D364 
kgeasnmierr()+28     CALL     kgerinv()            106527D18   000000000 
                                                   106036C48   000000000 
                                                   FFFFFFFF7FFF89B0 
                                                   000001430 
kkslgbv()+180        CALL     kgeasnmierr()        106527D18 
                                                   FFFFFFFF7C626270 
                                                   106036C48   000000000 
                                                   000000001   000000005 
kxscod()+932         CALL     kkslgbv()            000106000   000000000 
                                                   10652D358   4A02294D8 
                                                   000000001   0000000AA 
kksCompareBinds()+8  CALL     kxscod()             000000001   5C0130BFC 
76                                                 0000000B0   000000001 
                                                   000000300   00000000B 
kksfbc()+8460        CALL     kksCompareBinds()    000000000   000000004 
                                                   000000000   000000000 
                                                   FFFFFFFF7C659578 
                                                   4A0229388 
opiexe()+2404        CALL     kksfbc()             000380018   000000000 
                                                   000000102   000000000 
                                                   4FFF887C0   4938E96C8 
kpoal8()+1912        CALL     opiexe()             000000003   000106534 
                                                   000106400   1065374F8 
                                                   FFFFFFFF7FFFAD00 
                                                   5C012F9B8 
opiodr()+1548        PTR_CALL 0000000000000000     0BFFFFC00   003901808 
                                                   000000000   000000860 
                                                   000105800   106534E60 
ttcpip()+1284        PTR_CALL 0000000000000000     10576AE00   00000005E 
                                                   106527C00   000000001 
                                                   FFFFFFFF7C63A830 
                                                   00010652A 
opitsk()+1432        CALL     ttcpip()             000000028 
                                                   FFFFFFFF7FFFCD90 
                                                   1056C116C   1056BE950 
                                                   000000000   106527D18 
opiino()+1128        CALL     opitsk()             106534E68   000000001 
                                                   000000000   106534E60 
                                                   1058855B8   0FFFFFFFD 
opiodr()+1548        PTR_CALL 0000000000000000     000106400   10652A798 
                                                   000106400   10652A000 
                                                   000106400   106534E60 
opidrv()+896         CALL     opiodr()             106533FD8   00000003C 
                                                   000106400   106534DE0 
                                                   000106534   00010652A 
sou2o()+80           CALL     opidrv()             106537560   000000000 
                                                   00000003C   106534298 
                                                   00000003C   000000000 
opimai_real()+124    CALL     sou2o()              FFFFFFFF7FFFF4E8 
                                                   00000003C   000000004 
                                                   FFFFFFFF7FFFF510 
                                                   105E0F000   000105E0F 
main()+152           CALL     opimai_real()        000000002 
                                                   FFFFFFFF7FFFF5E8 
                                                   10405266C   1064CFE98 
                                                   00247D72C   000014800 
_start()+380         CALL     main()               000000002   000000008 
                                                   000000000 
                                                   FFFFFFFF7FFFF5F8 
                                                   FFFFFFFF7FFFF708 
                                                   FFFFFFFF7D500200 

============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |         |       |       |    63 |           |
| 1   |  SORT ORDER BY       |         |  2114 |  206K |    63 |  00:00:01 |
| 2   |   HASH JOIN          |         |  2114 |  206K |    62 |  00:00:01 |
| 3   |    TABLE ACCESS FULL | TPLGROUP|   288 |  2016 |     6 |  00:00:01 |
| 4   |    TABLE ACCESS FULL | TPLLIB  |  2114 |  192K |    56 |  00:00:01 |
---------------------------------------+-----------------------------------+

Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : XHWUSER
  plan_hash      : 2852640237
Peeked Binds
============
  Bind variable information
    position=2
    datatype(code)=2
    datatype(string)=NUMBER
    precision=0
    scale=0
    max length=22
    value=2
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "TG"@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "TG"@"SEL$2" "T2"@"SEL$1")
      USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$1")
    END_OUTLINE_DATA
  */

  _table_scan_cost_plus_one           = true
  _cost_equality_semi_join            = true
  _default_non_equality_sel_check     = true
  _new_initial_join_orders            = true
  _oneside_colstat_for_equijoins      = true
  _optim_peek_user_binds              = true   ###### 该参数默认是true ######
  _minimal_stats_aggregation          = true
  _force_temptables_for_gsets         = false
  workarea_size_policy                = auto
  _smm_auto_cost_enabled              = true
  _gs_anti_semi_join_allowed          = true
  _optim_new_default_join_sel         = true
  optimizer_dynamic_sampling          = 2

经查为oracle bug 5169008,该bug其实是10202平台的,由于该问题一直没有解决,所以10203也就没给出bug号。
详见metalink文档:

Bug 5169008: ORA-00600 [KKSLGBV0] WHEN CURSOR_SHARING=SIMILAR

目前关于bind peek的问题,10g的所有的版本都有这个问题,9i也有,这个问题到11g都没有完全解决(11g有自适应游标共享的新特性)

下面是关于这个bug的相信描述:
Hdr: 5169008 10.2.0.2.0 RDBMS 10.2.0.2.0 UNKNOWN PRODID-5 PORTID-23 ORA-600
Abstract: ORA-600 [KKSLGBV0] WHEN CURSOR_SHARING=SIMILAR

*** 04/18/06 03:16 am ***
TAR:
----
5327916.993

PROBLEM:
--------
Encounters ORA-600 [kkslgbv0] while running a select with
cursor_sharing=similar.

Bug 4939538 -> Couldnot reproduce . Closed.
Bug 5155885 -> With Bug Screening team.

Failing SQL:
select MAX(last_update_date) as last2_0_, COUNT(last_update_date) as last1_0_
from AF_UPDATE_DATES
where proc_type in (:"SYS_B_0", :"SYS_B_1", :"SYS_B_2", :"SYS_B_3")

DIAGNOSTIC ANALYSIS:
--------------------
ORA=00600 [kkslgbv0] occurs when bind position check in a child cursor
information block fails when trying to replace the literal.
We are looking for the bind variables relating to a cursor but we can not
find them.

We see aggregate functions & INLIST operations, any of the above (or) both
together would cause this problem.   

WORKAROUND:
-----------
cursor_sharing=EXACT
(OR)
alter system flush shared_pool;

RELATED BUGS:
-------------
Bug 4939538 -> Couldnot reproduce . Closed.
Bug 5155885 -> With Bug Screening team.      ###### 这话的意思就是没解决 ######

REPRODUCIBILITY:
----------------
Occurs on customer's enviornment.

TEST CASE:
----------
STACK TRACE:
------------
kkslgbv kxscod kkscbt kksfbc opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr
opidrv sou2o

SUPPORTING INFORMATION:
-----------------------
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------

*** 04/18/06 03:19 am ***
*** 04/22/06 04:52 pm ***
*** 04/22/06 07:22 pm *** (CHG: Sta->10)
*** 04/22/06 07:22 pm ***
*** 05/23/06 11:36 pm *** (CHG: Sta->31 SubComp->UNKNOWN)
*** 06/14/06 06:37 am ***
*** 06/14/06 06:37 am *** (CHG: Sta->16)
*** 06/14/06 09:53 am *** (CHG: Sta->31)
*** 02/07/07 09:42 am *** (CHG: Sta->91)

解决办法:

可以通过刷新shared pool来避免这个错误,不过刷新shared pool后,所有在shared pool的sql语句再次执行的话
都需要再硬解析一次,当然操作的时候,肯定是建议挑一个空闲的时间段去操作,不然可能会造成严重的后果。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00600 [kkslgbv0]产生原因和解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1276.html

Oracle研究中心

关键词:

ORA-00600

ora-00600 [kkslgbv0]