RE: Analytic bug in 9.2.0.4

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 05 Dec 2003 04:19:24 -0800
Message-ID: <F001.005D8EEA.20031205041924@fatcity.com>

Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

works on 9202 but that's not what you wanted to hear ... right?=20 =20
Raj

-------------------------------------------------------------------------=

-------=20
Rajendra dot Jamadagni at nospamespn dot com=20 All Views expressed in this email are strictly personal.=20 QOTD: Any clod can have facts, having an opinion is an art !=20

-----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 s= ome SQL=20
that will consistently cause ORA-600 [kkqwrm_noref: COLFDNF set]=20

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

Here's the SQL:=20

select=20

        pga_target_for_estimate=20


        , pga_target_factor=20

        , low_optimal_size=20

        , high_optimal_size=20

        , estd_optimal_executions=20

        , estd_onepass_executions=20

        , estd_multipasses_executions=20

        , estd_total_executions=20

        , ignored_workareas_count=20

from v$pga_target_advice_histogram=20
where pga_target_for_estimate in (=20

        select  pga_target_for_estimate=20
        from (=20
                select=20
                        max(pga_target_for_estimate) over ( partition by =
pga_target_for_estimate) pga_target_for_estimate=20
                        , sum(estd_multipasses_executions) over ( partiti=
on by pga_target_for_estimate) sum_estd_multipasses=20
                        , max(high_optimal_size) over ( partition by pga_=
target_for_estimate) max_high_optimal_size=20
                from v$pga_target_advice_histogram=20
        ) a=20
        where sum_estd_multipasses < 1=20
        group by pga_target_for_estimate, sum_estd_multipasses=20

)=20
order by pga_target_for_estimate, low_optimal_size=20 /=20

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

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

It also appears on 9.2.0.4 on Win2k SP3.=20

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

Jared=20

*************************************************************************=

This e-mail message is confidential, intended only for the named recipien=
t(s) above and may contain information that is privileged, attorney work =
product or exempt from disclosure under applicable law. If you have recei=
ved this message in error, or are not the named recipient(s), please imme=
diately notify corporate MIS at (860) 766-2000 and delete this e-mail mes=

sage from your computer, Thank you.

*************************************************************************=

*************4

------_=_NextPart_001_01C3BB29.AA565064
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

9-1">

<META content=3D"MSHTML 5.50.4934.1600" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D308301612-05122003><FONT face=3D"Courier New" color=3D=

#0000ff=20
size=3D2>works on 9202 but that's not what you wanted to hear ... right?=20=

</DIV>
<DIV> </DIV>
<DIV>Raj</DIV>
<DIV><FONT face=3D"Courier New"=20=

size=3D2>----------------------------------------------------------------=
----------------</FONT>=20

Rajendra dot Jamadagni at nospame= spn dot=20
com All Views expressed in= this email=20
are strictly personal. QOT= D: Any clod=20
can have facts, having an opinion is an art ! </DIV> <BLOCKQUOTE>
 <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft>-----Original Message----- From: Jared.Still_at_radisys= =2Ecom=20
 [mailto:Jared.Still_at_radisys.com] Sent: Thursday, December 04,= 2003=20
 7:29 PM To: Multiple recipients of list ORACLE-L Subjec= t:=20
 Analytic bug in 9.2.0.4 </DIV> While playing around with SQL for some PGA scripts, I managed = to create=20
 some SQL that will consiste= ntly cause=20
  ORA-600 [kkqwrm_no= ref:=20
 COLFDNF set] This appea= rs to be=20
 Bug # 2507421, which was supposedly fixed in 9.2.0.3. <F= ONT=20
 face=3Dsans-serif size=3D2>Here's the SQL: select   &nbs= p;  =20

  &nbsp; pga_target_for_estimate</FONT> <BR><FONT face=3Dsans-serif size=3D=
2>&nbsp;=20
  &nbsp; &nbsp; &nbsp; , pga_target_factor</FONT> <BR><FONT face=3Dsans-s=

erif=20
 size=3D2>        , low_optimal_size <FON= T=20
 face=3Dsans-serif size=3D2>        , high_optimal_s= ize=20
         ,=20 estd_optimal_executions &nb= sp;  =20
     , estd_onepass_executions         , estd_multipasses_executions</FON= T>=20
         ,=20 estd_total_executions &nbsp= ;  =20
     , ignored_workareas_count from v$pga_target_advice_histogram where pga_target_for_estimate in (         select  pga_target_for_estima= te=20
         from (= =20
          =  =20
     select  =  =20
                    =20=

max(pga_target_for_estimate) over ( partition by pga_target_for_estimat= e)=20
 pga_target_for_estimate &nb= sp;  =20
                     ,= =20
 sum(estd_multipasses_executions) over ( partition by pga_target_for_est= imate)=20
 sum_estd_multipasses  =  =20
                     ,= =20
 max(high_optimal_size) over ( partition by pga_target_for_estimate)=20 max_high_optimal_size &nbsp= ;  =20
             from=20 v$pga_target_advice_histogram  =20
       ) a &n= bsp;  =20
     where sum_estd_multipasses < 1 <FONT=20 face=3Dsans-serif size=3D2>        group by=20 pga_target_for_estimate, sum_estd_multipasses ) order by=20 pga_target_for_estimate, low_optimal_size / This bit of = SQL is a bit=20
 useless as is, that is, for anything other than causing ORA-600.= =20
 This is on 9.3.0.4 on RH Linux= 7.2=20
  Kernel 2.4.20-18.7smp It=20
 also appears on 9.2.0.4 on Win2k SP3. Anyone else see similar results?  On a test database of=20=

course. Jared</FONT= >=20

  <BR><BR><BR><BR><BR></BLOCKQUOTE><br>**********************************=
****************************************************<br>This e-mail messa=
ge is confidential, intended only for the named recipient(s) above and ma=
y contain information that is privileged, attorney work product or exempt=
 from disclosure under applicable law. If you have received this message =
in error, or are not the named recipient(s), please immediately notify co=

rporate MIS at (860) 766-2000 and delete this e-mail message from your co=

mputer, Thank you.<br>***************************************************=
***********************************4</BODY></HTML>

------_=_NextPart_001_01C3BB29.AA565064--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

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 - 06:19:24 CST