This problem was observed after upgrading IDS from 11.50.FC5W2
on AIX 6.1
to IDS 12.10.FC14 on linux x86-64.
After upgrading to 12.10.FC14, the optimizer is chosing a
different plan presumably based
on a lower estimated cost.
The new plan in 12.10.FC14 has a cost for example of close to
200,000 and is composed of
nested loop joins only. This plan leads to an execution time of
over 20 hours.
Using directives, itâ ™s possible to generate the same plan in
11.50.FC5W2. In 12.10.FC14, a
huge estimated cost of over 500,000,000 is generated and uses a
mix of DYNAMIC HASH
JOINS and NESTED LOOP JOINS but runs to completion in around an
hour.
The query is similar to:
SELECT
tab1.cdesc,
tab1.prio,
tab3.ty,
tab7.o_n AS tg_n,
tab5.f_nr,
tab5.f_n,
0 AS o_a_t,
1 * SUM(NVL(tab3.a,0) *tab4.f_p) AS o_a_c,
0 AS a_t,
0 AS a_c,
0 AS b_a
FROM tab1,
tab2,
tab3,
tab4,
tab5,
tab6,
tab7x tab7
WHERE tab1.id = tab2.c_id
AND tab2.id = tab3.t_cd
AND tab3.d_s_id = tab4.d_s_id
AND tab4.f_id = tab5.id
AND tab5.d_id = tab7.p_id
AND tab5.d_id = tab6.p_id
GROUP BY 1,2,3,4,5,6,7,9,10,11; |
