一个oracle查询引起的bug

任何软件都不是完美的，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. 

SOLUTION

Apply patch 11852469 if it exists for your version/platform  

or

Apply patchset 11.2.0.3 where the fix is included
 

按照目前的情况，打数据库的版本补丁还是需要评估的一件事，需要多方协调来完成。按照问题的情况，因为语句执行的很不频繁，而且只是有客户端做一个简单查询，所以综合评估下来问题的影响范围极小，在备份库中也做了相同的语句测试，发现问题也不会复现，还是需要一些基本的环境和时机的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1443107/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1443107/