一套HP-UX上的10.2.0.4系统在运行某条 select查询语句时出现ORA-00600[kkocxj:pjpCtx]内部错误,TRACE文件信息如下:
FILE VERSION ------------------ Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/app/oracle/product/10.2 System name: HP-UX Node name: crmdb1 Release: B.11.31 Version: U Machine: ia64 Instance name: cbssnm Redo thread mounted by this instance: 1 TRACE FILE --------------- Filename = cbssnm_ora_29061.trc *** ACTION NAME:(SQL 窗口 - 新建) 2010-07-02 15:59:46.238 *** MODULE NAME:(PL/SQL Developer) 2010-07-02 15:59:46.238 *** SERVICE NAME:(SYS$USERS) 2010-07-02 15:59:46.238 *** SESSION ID:(770.4341) 2010-07-02 15:59:46.237 *** 2010-07-02 15:59:46.237 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], [] Current SQL statement for this session: select p.access_number, aa.name from crm.product p, (select aa.prod_id, os.name, os.staff_number from (select * from (select prod_id, party_id, row_number() over(partition by prod_id order by start_dt desc) num from crm.party_2_prod where end_dt > sysdate and party_product_rela_role_cd = 3) where num = 1) aa, crm.our_staff os where aa.party_id = os.staff_id) aa where p.prod_id = aa.prod_id(+) and p.access_number = '15335581126' ----- Call Stack Trace ----- ksedst <- ksedmp <- ksfdmp <- kgerinv <- kgeasnmierr <- $cold_kkocxj <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb <- apaqbdDescendents <- apaqbd <- kkqctCostTransfQB <- kkqctdrvJP <- kkqjpdttr <- kkqctdrvTD <- kkqjpddrv <- kkqdrv <- kkqctdrvIT <- apadrv <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- main <- main_opd_entry
根据错误代码和stack trace可以在metalink上匹配到如下Bug:
Bug:7014646 Abstract: ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KKOCXJ : PJPCTX], [], [], [], [], [] Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions < 11.2 Versions confirmed as being affected * 10.2.0.4 * 11.1.0.6 Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in * 10.2.0.4 Patch 7 on Windows Platforms * 10.2.0.5 (Server Patch Set) * 11.1.0.7 (Server Patch Set) * 11.2 (Future Release) Symptoms: * Internal Error May Occur (ORA-600) * ORA-600 [kkocxj : pjpCtx] Related To: * Optimizer * _OPTIMIZER_PUSH_PRED_COST_BASED Description A complex query can fail during parse with ORA-600 [kkocxj : pjpCtx] Workaround Set "_optimizer_push_pred_cost_based"=false
该bug可以通过实施one off Patch 7014646修复,也可以尝试通过修改隐式参数_optimizer_push_pred_cost_based禁用基于成本的谓词前置特性(WORKAROUND: disable cost based push predicate)来规避该[KKOCXJ:PJPCTX]内部错误发生,具体的修改方法:
SQL> conn / as sysdba SQL> alter system set "_optimizer_push_pred_cost_based"=false; SQL> exit /* 设置该隐式参数无需重启实例 */
Oracle GCS更推荐通过应用补丁7014646的方法来解决问题,而修改以上隐式参数则不一定百分之百能解决问题。
Comment