The
same thing occurs on OpenVMS on version 9.2.0.4 of Oracle.
Nelson
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 7:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Analytic bug in 9.2.0.4
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
