Art,
update statistics high for table item_k_pr ( item_k_pr_id,item_num,pr_num,value) ;
update statistics high for table ret_ban ( id,pr_num) ;
OPT_SEEK_FACTOR is not an option in 11.70. It has:
OPTCOMPIND 0
OPT_GOAL -1
dbschema -hd :
{
Distribution for owner.item_k_pr.item_k_pr_id
Constructed on 2024-08-30 02:59:49.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( 1)
1: ( 132223, 132223, 254790)
<...>
201: ( 27, 27, 38996695)
Distribution for owner.item_k_pr.item_num
Constructed on 2024-08-30 02:59:32.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( 283056 )
1: ( 132224, 10593, 1009983 )
<...>
200: ( 132026, 9688, Ю╝╖╜╗Г╜═О )
Distribution for owner.item_k_pr.pr_num
Constructed on 2024-08-30 02:59:15.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( 1000 )
1: ( 132077, 99, 1192 )
<...>
56: ( 1436, 3, ▒▐ )
--- OVERFLOW ---
1: ( 36465, 1194 )
<...>
112: ( 44970, 8462 )
Distribution for owner.item_k_pr.item_pr
Constructed on 2015-03-20 01:09:26.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( - )
1: ( 131479, 1326, 12859 )
<...>
14: ( 103634, 455, ═ )
--- OVERFLOW ---
1: ( 19763967, NULL)
<...>
33: ( 35820, ▓ )
Distribution for owner.item_k_pr.value
Constructed on 2024-08-30 02:58:59.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
(
▐╝╞Ф╝╒═ █═Б═╚ЛО ▐═╒╚╝╒╜═
xxxxxxxxxxxxxxxxx
+0 (49)
1: ( 131863, 31, 18 )
<...>
95: ( 121799, 10174, ЪЪЪ xxxxxxxxxxxxxxxxxxxx )
--- OVERFLOW ---
1: ( 3455042, NULL)
<...>
69: ( 101221, ╝╓╜╝Ю═╖╝╒К╘ )
Distribution for owner.item_k_pr.uom_code
Constructed on 2015-03-20 01:09:26.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( )
1: ( 133884, 815, А╛ )
2: ( 3637, 133, ХБ. )
--- OVERFLOW ---
1: ( 25029616, NULL)
2: ( 969204, )
3: ( 241780, ╛╛ )
4: ( 66507, ХБ. )
Distribution for owner.item_k_pr.pr_link_id
Constructed on 2015-03-20 01:09:24.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( NULL)
--- OVERFLOW ---
1: ( 26444627, NULL)
}
{
Distribution for owner.ret_ban.id
Constructed on 2024-08-30 03:51:16.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( 1)
1: ( 1, 1, 1)
<...>
20: ( 1, 1, 20)
Distribution for owner.ret_ban.pr_num
Constructed on 2024-08-30 03:51:16.00000
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( 5973 )
--- OVERFLOW ---
1: ( 20, 5973 )
}
------------------------------
Sincerely,
Dennis
------------------------------
Original Message:
Sent: Fri August 30, 2024 06:05 AM
From: Art Kagel
Subject: How to manage Informix optimizer?
Dennis:
What was the UPDATE STATISTICS command you ran? Also, can you post the output from dbschema -hd for those two tables?
I don't remember if this parameter came in with 11.70 or 12.10, but try setting:
OPT_SEEK_FACTOR 0 # Default is 6 and it affects how the optimizer calculates index costs which changed in 11.70 at some point. Setting this to 0 should restore earlier behavior (doesn't explain why the plan changes after update statistics, but ...)
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Fri August 30, 2024 03:58 AM
From: Dennis Melnikov
Subject: How to manage Informix optimizer?
Hi,
IBM Informix Dynamic Server Version 11.70.FC5XE
After daily routine UPDATE STATISTICS a simple query changed its plan, and executes in 5+ sec instead of 0.015:
SELECT ret_ban.*
FROM item_k_pr, ret_ban
WHERE ret_ban.pr_num = item_k_pr.pr_num
AND ret_ban.pr_value = item_k_pr.value
AND ret_ban.active = "Y"
AND item_k_pr.item_num = "32389";
If hinted with {+ ORDERED} the plan reverses back to normal.
------
SELECT ret_ban.*
FROM item_k_pr, ret_ban
WHERE ret_ban.pr_num = item_k_pr.pr_num
AND ret_ban.pr_value = item_k_pr.value
AND ret_ban.active = "Y"
AND item_k_pr.item_num = "32389";
Estimated Cost: 5
Estimated # of Rows Returned: 1
1) owner.ret_ban: SEQUENTIAL SCAN
Filters: owner.ret_ban.active = 'Y'
2) owner.item_k_pr: INDEX PATH
Filters: owner.item_k_pr.item_num = '32389'
(1) Index Name: owner.idx_item_k_pr_3
Index Keys: pr_num value (Serial, fragments: ALL)
Lower Index Filter: (owner.ret_ban.pr_value = owner.item_k_pr.value AND owner.ret_ban.pr_num = owner.item_k_pr.pr_num )
NESTED LOOP JOIN
QUERY: (OPTIMIZATION TIMESTAMP: 08-30-2024 09:46:09)
------
SELECT
{+ ORDERED} ret_ban.*
FROM item_k_pr, ret_ban
WHERE ret_ban.pr_num = item_k_pr.pr_num
AND ret_ban.pr_value = item_k_pr.value
AND ret_ban.active = "Y"
AND item_k_pr.item_num = "32389";
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 11
Estimated # of Rows Returned: 1
1) owner.item_k_pr: INDEX PATH
(1) Index Name: owner.item_k_pr_1
Index Keys: item_num (Serial, fragments: ALL)
Lower Index Filter: owner.item_k_pr.item_num = '32389'
2) tecsys.ret_ban: INDEX PATH
Filters: (owner.ret_ban.pr_value = owner.item_k_pr.value AND owner.ret_ban.active = 'Y' )
(1) Index Name: owner. 3299_1146146781
Index Keys: pr_num (Serial, fragments: ALL)
Lower Index Filter: owner.ret_ban.pr_num = owner.item_k_pr.pr_num
NESTED LOOP JOIN
How to get it back to the optimal plan without hinting?
------------------------------
Sincerely,
Dennis
------------------------------