RE: Analytic bug in 9.2.0.4

From: <nelson.petersen_at_homehardware.ca>
Date: Fri, 05 Dec 2003 05:29:24 -0800
Message-ID: <F001.005D8EEF.20031205052924@fatcity.com>

Content-Type: text/plain;
charset="windows-1252"

The same thing occurs on OpenVMS on version 9.2.0.4 of Oracle.

Nelson

-----Original Message-----
Sent: Thursday, December 04, 2003 7:29 PM To: Multiple recipients of list ORACLE-L

While playing around with SQL for some PGA scripts, I managed to create some SQL
that will consistently cause ORA-600 [kkqwrm_noref: COLFDNF set]

This appears to be Bug # 2507421, which was supposedly fixed in 9.2.0.3.

Here's the SQL:

select

        pga_target_for_estimate 


        , pga_target_factor 

        , low_optimal_size 

        , high_optimal_size 

        , estd_optimal_executions 

        , estd_onepass_executions 

        , estd_multipasses_executions 

        , estd_total_executions 

        , ignored_workareas_count

from v$pga_target_advice_histogram
where pga_target_for_estimate in (

        select  pga_target_for_estimate 
        from ( 
                select 
                        max(pga_target_for_estimate) over ( partition by
pga_target_for_estimate) pga_target_for_estimate 
                        , sum(estd_multipasses_executions) over ( partition
by pga_target_for_estimate) sum_estd_multipasses 
                        , max(high_optimal_size) over ( partition by
pga_target_for_estimate) max_high_optimal_size 
                from v$pga_target_advice_histogram 
        ) a 
        where sum_estd_multipasses < 1 
        group by pga_target_for_estimate, sum_estd_multipasses

)
order by pga_target_for_estimate, low_optimal_size /

This bit of SQL is a bit useless as is, that is, for anything other than causing ORA-600.

This is on 9.3.0.4 on RH Linux 7.2 Kernel 2.4.20-18.7smp

It also appears on 9.2.0.4 on Win2k SP3.

Anyone else see similar results? On a test database of course.

Jared

------_=_NextPart_001_01C3BB33.ACC34370
Content-Type: text/html;
charset="windows-1252"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">


<META content="MSHTML 5.00.3315.2870" name=GENERATOR></HEAD>

<BODY>
<DIV>The same thing occurs on OpenVMS on version 9.2.0.4 of Oracle.</DIV> <DIV> </DIV> <DIV>Nelson</DIV> <BLOCKQUOTE>
<DIV align=left class=OutlookMessageHeader dir=ltr>-----Original Message----- From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: Thursday, December 04, 2003 7:29 PM To: Multiple recipients of list ORACLE-L Subject: Analytic bug in 9.2.0.4 </DIV> While playing around with SQL for some PGA scripts, I managed to create some SQL that will consistently cause  ORA-600 [kkqwrm_noref: COLFDNF set] This appears to be Bug # 2507421, which was supposedly fixed in 9.2.0.3. Here's the SQL: select         pga_target_for_estimate         , pga_target_factor         , low_optimal_size         , high_optimal_size         , estd_optimal_executions         , estd_onepass_executions         , estd_multipasses_executions         , estd_total_executions         , ignored_workareas_count <FONT face=sans-serif

  size=2>from v$pga_target_advice_histogram</FONT> <BR><FONT face=sans-serif 
  size=2>where pga_target_for_estimate in (</FONT> <BR><FONT face=sans-serif 
  size=2>&nbsp; &nbsp; &nbsp; &nbsp; select &nbsp;pga_target_for_estimate</FONT>

from (                 select                         max(pga_target_for_estimate) over ( partition by pga_target_for_estimate) pga_target_for_estimate                         , sum(estd_multipasses_executions) over ( partition by pga_target_for_estimate) sum_estd_multipasses                         , max(high_optimal_size) over ( partition by pga_target_for_estimate) max_high_optimal_size                 from v$pga_target_advice_histogram         ) a         where sum_estd_multipasses < 1         group by pga_target_for_estimate, sum_estd_multipasses ) order by pga_target_for_estimate, low_optimal_size / This bit of SQL is a bit useless as is, that is, for anything other than causing ORA-600. This is on 9.3.0.4 on RH Linux 7.2  Kernel 2.4.20-18.7smp It also appears on 9.2.0.4 on Win2k SP3. Anyone else see similar results?  On a test database of course. Jared </BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C3BB33.ACC34370--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: nelson.petersen_at_homehardware.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Dec 05 2003 - 07:29:24 CST