一个oracle查询引起的bug-白红宇

一个oracle查询引起的bug

阅读量：2446 次

发布时间：2019-05-10

本文共 4316 字，大约阅读时间需要 14 分钟。

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