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
- RE: Analytic bug in 9.2.0.4 Jared . Still
- RE: Analytic bug in 9.2.0.4 Trevor . Williams
- RE: Analytic bug in 9.2.0.4 Prem Khanna J
- RE: Analytic bug in 9.2.0.4 jo_holvoet
- Re: Analytic bug in 9.2.0.4 Jonathan Lewis
- RE: Analytic bug in 9.2.0.4 Jamadagni, Rajendra
- RE: Analytic bug in 9.2.0.4 nelson . petersen
- Re: Analytic bug in 9.2.0.4 Mladen Gogala
- RE: Analytic bug in 9.2.0.4 Brian McGraw
- RE: Analytic bug in 9.2.0.4 Hrncirik, Debbie
- RE: Analytic bug in 9.2.0.4 Jared . Still