ORA-600(qerpfAllocateR)错误

在客户的10.2.0.3数据库中，发现了这个错误信息。

 

 

详细的错误信息为：

Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/MASTER/udump/master1_ora_20520.trc:
ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []
Thu Jul 28 18:11:06 2011
Trace dumping is performing id=[cdmp_20110728181105]
Thu Jul 28 18:13:24 2011
Trace dumping is performing id=[cdmp_20110728181323]
Thu Jul 28 18:18:09 2011
Trace dumping is performing id=[cdmp_20110728181809]
Thu Jul 28 18:18:10 2011
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/MASTER/udump/master1_ora_8707.trc:
ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []
Thu Jul 28 18:18:12 2011
Trace dumping is performing id=[cdmp_20110728181812]

对应的trace文件中的详细信息为：

*** SERVICE NAME:(MASTER) 2011-07-28 18:18:10.025
*** SESSION ID:(447.2275) 2011-07-28 18:18:10.025
*** 2011-07-28 18:18:10.025
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []
Current SQL statement for this session:
select * from ( select row_.*, rownum rownum_ from ( SELECT
              tran_id, id, task_id, city_id, isp_id, net_speed_id, tm_base, probe_ip, error_code, cont_err_total, byte_total, byte_page_base, rate_download, dest_ip, ts_total, dns_server, version_id, percent_cpu, percent_cpu_task, percent_mem, percent_mem_task, rate_avg, num_first_elem, byte_first, num_host, ts_dns_total, num_connect, ts_connect_total, num_dom, num_iframe, num_no_compress_elem, num_no_expire_elem, num_no_etag_elem, member_id
         FROM (
                   SELECT /*+ INDEX(NB_PAGE_24612 IN_PAGE_PERF_24612)*/
                          t.id as tran_id, p.id, p.task_id, p.city_id, p.isp_id, p.net_speed_id, p.tm_base, p.probe_ip, p.error_code, p.cont_err_total, p.byte_total, p.byte_page_base, p.rate_download, p.dest_ip, p.ts_total as ts_total, t.dns_server, t.version_id, t.percent_cpu, t.percent_cpu_task, t.percent_mem, t.percent_mem_task, t.rate_avg, p.num_first_elem, p.byte_first, p.num_host, p.ts_dns_total, p.num_connect, p.ts_connect_total, p.num_dom, p.num_iframe, p.num_no_compress_elem, p.num_no_expire_elem, p.num_no_etag_elem, t.member_id, 0 AS ts_total_rank
                   FROM NB_PAGE_24612 p INNER JOIN NB_TRAN_24612 t ON p.tran_id = t.id
                   WHERE p.task_id = :1 AND p.page_seq = :2 AND p.tm_base >= :3 AND p.tm_base <= :4 AND p.is_noise = 0
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
              )
         WHERE 1=1
                  
         ORDER BY tm_base ASC ) row_ where rownum <= 3000) where rownum_ > 0
----- Call Stack Trace -----
calling              call     entry                argument values in hex     
location             type     point                (? means dubious value)    
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFC85437A0 ? 7FFFC8543800 ?
                                                   7FFFC8543740 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFC85437A0 ? 7FFFC8543800 ?
                                                   7FFFC8543740 ? 000000000 ?
ksfdmp()+21          call     ksedmp()             000000003 ? 000000001 ?
                                                   7FFFC85437A0 ? 7FFFC8543800 ?
                                                   7FFFC8543740 ? 000000000 ?
kgerinv()+161        call     ksfdmp()             000000003 ? 000000001 ?
                                                   7FFFC85437A0 ? 7FFFC8543800 ?
                                                   7FFFC8543740 ? 000000000 ?
kgeasnmierr()+163    call     kgerinv()            0067285E0 ? 2AAAAC846270 ?
                                                   7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qerpfAllocate()+791  call     kgeasnmierr()        0067285E0 ? 2AAAAC846270 ?
                                                   7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qerpfAllocate()      2AAAAD025608 ? 2AAAAC846270 ?
)+446                                              7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD025608 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD025408 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD025238 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD0250A0 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD016BB0 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD016A18 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD024D70 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?
qknRwsAllocateTree(  call     qknRwsAllocateTree(  2AAAAD0246F0 ? 2AAAAC846270 ?
)+169                         )                    7FFFC8543800 ? 7FFFC8543740 ?
                                                   000000000 ? 000000000 ?

导致错误产生的是一个标准分页的SQL语句，查询了metalink，发现这是10.2.0.3上的一个bug。文档ID 456992.1描述了这个问题，错误发生在包含ROWNUM的并行查询语句中，当出现NESTED LOOP执行计划时，Oracle执行ROWNUM的PUSHDOWN操作时导致了错误。

解决方法也很简单，除了升级到10204之外，还有两种方法：一、通过alter session disable parallel query，禁止并行执行；二、通过alter session set “_px_rownum_pd” = false，禁止ROWNUM的PUSHDOWN。

和升级或禁止所有并行查询相比，显然第二种方法对系统的影响更小，只是禁止了并行查询中ROWNUM的PUSHDOWN这种优化模式。

 

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

转载于:http://blog.itpub.net/4227/viewspace-703585/