一个oracle查询引起的bug (r4笔记第59天)

jeanron100

发布于 2018-03-15 16:20:55

7190

发布于 2018-03-15 16:20:55

任何软件都不是完美的，oracle也是如此，隔一段时间就会收到oracle的邮件说建议打哪些安全补丁什么的。新发布的产品都是release 1，比如10gR1,稳定版本都在10gR2 不要小看着两个大版本的变化，印象比较深的就是10g 10.2.0.1的安装包有大概600多M,但是在10.2.0.2.0的补丁包就比安装包还多，可见在产品线内做了很多的修改，才使得数据库越来越稳定。昨天下午在检查一个问题的时候，发现数据库日志报出了ora-600的错误，这种症状不清的错误只能求助于metalink了。错误日志的大体内容如下： Thu Feb 26 11:06:35 2015 Archived Log entry 60642 added for thread 1 sequence 60576 ID 0xb8c6d509 dest 1: Thu Feb 26 11:07:20 2015 Errors in file /opt/app/oracle/dbccbspr1/diag/rdbms/cust01/CUST01/trace/CUST01_p019_23657.trc (incident=2100684): ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /opt/app/oracle/dbccbspr1/diag/rdbms/cust01/CUST01/incident/incdir_2100684/CUST01_p019_23657_i2100684.trc Thu Feb 26 11:07:57 2015 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Feb 26 11:07:58 2015 Errors in file /opt/app/oracle/dbccbspr1/diag/rdbms/cust01/CUST01/trace/CUST01_ora_27252.trc (incident=2119548): ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /opt/app/oracle/dbccbspr1/diag/rdbms/cust01/CUST01/incident/incdir_2119548/CUST01_ora_27252_i2119548.trc Thu Feb 26 11:07:58 2015 Sweep [inc][2100684]: completed Sweep [inc][2119548]: completed Sweep [inc2][2100684]: completed Thu Feb 26 11:07:58 2015 Dumping diagnostic data in directory=[cdmp_20150226110758], requested by (instance=1, osid=23657 (P019)), summary=[incident=2100684]. 查看trace文件的内容如下，发现是一个客户端程序调用的。运行的语句是一个采用了并行，使用分析函数的多表关联语句。

***  2015-02-26 11:07:20.965
*** SESSION ID:(4404.677) 2015-02-26  11:07:20.965
*** CLIENT ID:() 2015-02-26 11:07:20.965
*** SERVICE  NAME:(CUST01) 2015-02-26 11:07:20.965
*** MODULE NAME:(PL/SQL Developer)  2015-02-26 11:07:20.965
*** ACTION NAME:(SQL Window - select /*+  PARALLEL(csm,4) PARALLEL(crd,4) PARALLEL) 2015-02-26 11:07:20.965
 
Dump  continued from file:  /opt/app/oracle/dbccbspr1/diag/rdbms/cust01/CUST01/trace/CUST01_p019_23657.trc
ORA-00600:  internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [],  [], []
========= Dump for incident 2100684 (ORA 600 [srsnext_3])  ========
*** 2015-02-26 11:07:20.969
dbkedDefDump(): Starting incident  default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for  this session (sql_id=17a5yw0f09u66) -----
select  /*+ PARALLEL(csm,4)   PARALLEL(crd,4)   PARALLEL(rater,4)   */csm.customer_id,csm.ban,csm.coll_status,csm.l9_crd_status,csm.l9_col_status,crd.tot_obligation_pct,rater.tot_obligation_pct,rater.file_id,rater.sys_creation_date,rater.extract_status,rater.waiver_ind,rater.waiver_exp_date
from  csm_account csm,
cl9_crd_mntr_fa crd,
(SELECT *
                  FROM  (SELECT cl9_rater_input.*,
                               ROW_NUMBER () OVER  (PARTITION BY account_id ORDER BY sys_creation_date desc,notification_timestamp  desc)
                                                                     AS  RANK
                          FROM cl9_rater_input
                          )
                 WHERE RANK = 1) rater
where csm.ban =  crd.account_id
and csm.customer_id = crd.customer_id
and csm.l9_crd_status  = 'PSUS'
and csm.customer_id = rater.customer_id
and csm.ban =  rater.account_id
--and payment.sys_creation_date >  rater.sys_creation_date
and crd.tot_obligation_pct !=  rater.tot_obligation_pct
and rater.tot_obligation_pct <  101

单纯来看语句的情况，真是看不出什么问题，因为这也是客户端执行的，执行频率应该很低，而且从语句能够看出有明显的修改痕迹，所以就放弃了对这个语句进行进一步调优，直接看看metalink怎么说。最后找到一篇相关的文章 Query Fails with ORA-00600: Internal Error Code, Arguments: [srsnext_3] (Doc ID 1589589.1) 这个问题发生的版本是在11.2.0.2，和生产中的问题环境一致。

> sqlplus  -v
SQL*Plus: Release 11.2.0.2.0  Production

oracle给出的解答如下：

CAUSE

Bug 11852469 : TS11.2.0.3V3 - TRC - SRSNEXT.

Rediscovery information:

If the srsnext_3 internal error is raised and the query involves statistical functions or other aggregates that are treated as distinct aggregates then you may be encountering this problem.