RE: Analytic bug in 9.2.0.4

From: <Jared.Still_at_radisys.com>
Date: Fri, 05 Dec 2003 08:04:27 -0800
Message-ID: <F001.005D8F06.20031205080427@fatcity.com>

Content-Type: text/plain; charset="us-ascii"

too funny.

It was 'fixed' in 9203.

According to Jonathan, it has actually been fixed in 10.

Jared

"Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> Sent by: ml-errors_at_fatcity.com
12/05/2003 04:19 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Analytic bug in 9.2.0.4

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

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----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

This e-mail message is confidential, intended only for the named recipient(s) above and may 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 corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

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

--=_alternative 00583C8B88256DF3_=
Content-Type: text/html; charset="us-ascii"

<br><font size=2 face="sans-serif">too funny.</font>
<br>
<br><font size=2 face="sans-serif">It was 'fixed' in 9203.</font>
<br>
<br><font size=2 face="sans-serif">According to Jonathan, it has actually been fixed in 10.</font>
<br>
<br><font size=2 face="sans-serif">Jared</font>
<br><font size=2 face="sans-serif"><br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>&quot;Jamadagni, Rajendra&quot; &lt;Rajendra.Jamadagni_at_espn.com&gt;</b></font>
<br><font size=1 face="sans-serif">Sent by: ml-errors_at_fatcity.com</font>
<p><font size=1 face="sans-serif">&nbsp;12/05/2003 04:19 AM</font>
<br><font size=2 face="sans-serif">&nbsp;</font><font size=1 face="sans-serif">Please respond to ORACLE-L</font>
<br>
<td><font size=1 face="Arial">&nbsp; &nbsp; &nbsp; &nbsp; </font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; To: &nbsp; &nbsp; &nbsp; &nbsp;Multiple recipients of list ORACLE-L &lt;ORACLE-L_at_fatcity.com&gt;</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp; &nbsp; &nbsp;</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; Subject: &nbsp; &nbsp; &nbsp; &nbsp;RE: Analytic bug in 9.2.0.4</font></table>
<br>
<br>
<br><font size=2 color=blue face="Courier New">works on 9202 but that's not what you wanted to hear ... right? </font>
<br><font size=3 face="Times New Roman">&nbsp;</font>
<br><font size=2 color=blue face="Courier New">Raj</font>
<br><font size=2 face="Courier New">--------------------------------------------------------------------------------</font><font size=3 face="Times New Roman"> </font><font size=2 face="Courier New"><br>

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----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

</font>
<br><font size=3 face="Times New Roman"><br>
</font><font size=2 face="sans-serif"><br>

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

 &nbsp; &nbsp; &nbsp; &nbsp;pga_target_for_estimate</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, pga_target_factor</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, low_optimal_size</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, high_optimal_size</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, estd_optimal_executions</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, estd_onepass_executions</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, estd_multipasses_executions</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, estd_total_executions</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;, ignored_workareas_count</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>

from v$pga_target_advice_histogram where pga_target_for_estimate in (

 &nbsp; &nbsp; &nbsp; &nbsp;select &nbsp;pga_target_for_estimate</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;from (</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;max(pga_target_for_estimate) over ( partition by pga_target_for_estimate) pga_target_for_estimate</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, sum(estd_multipasses_executions) over ( partition by pga_target_for_estimate) sum_estd_multipasses</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, max(high_optimal_size) over ( partition by pga_target_for_estimate) max_high_optimal_size</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from v$pga_target_advice_histogram</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
 &nbsp; &nbsp; &nbsp; &nbsp;) a</font><font size=3 face="Times New Roman"> </font>

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

<br>
<br>
<br>
</font>
<br><font size=3 face="Times New Roman"><br>
**************************************************************************************<br>

**************************************************************************************4</font>

--=_alternative 00583C8B88256DF3_=--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.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 - 10:04:27 CST