How to manage Informix optimizer?
Dennis Melnikov (11.70.FC5XE) saw a simple join slow from 0.015 to over 5 seconds after update statistics, fixed only by an ORDERED hint. Art Kagel and Mike Walker found no distributions on ret_ban.active; running UPDATE STATISTICS HIGH on that column restored the plan. Art also suggested extra indexes. Upgrade to 14.10 was discussed; Dennis said earlier testing ran too slow.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
------------------------------
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
------------------------------
Hi Dennis: Did you use Update Statistics or Update Statistics high or medium ? Sometimes the nature of the data in a column could change the behavior of the optimizer . We have had a similar problem in the past when a row has been inserted with a value of column was inserted wrong ( a very high value that was added by mistake). The correction of the data solved the problem. Of course directives to the optimizer always help. I am not sure if it is the case in your environment.
Just wondering...is item_k_pr.item_num a character field or numeric? If it's numeric then the quotes around the literal value can cause problems. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
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 -1dbschema -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
------------------------------
Mike, item_num is CHAR(10). ------------------------------ Sincerely, Dennis ------------------------------
Okay - thanks for confirming. You should have high stats on ret_ban.active as it's used in a filter, even though it's not indexed. Can you verify your "low" stats by checking systables.nrows for these two tables? I expect that the estimated and actual row counts should be pretty accurate, but let's confirm. Assuming that your tables are not fragmented, and that it's a logged database, it should be something like: select t.tabname[1,15] tabname, t.nrows est_rows, i.ti_nrows act_rows from systables t, sysmaster:systabinfo i where t.tabname in (" item_k_pr", "ret_ban" ) and t.partnum = i.ti_partnum; ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
Mike,
tabname est_rows act_rows
item_k_pr 2.6444627E7 26448517
ret_ban 20.0 20
Yes, after
UPDATE STATISTICS HIGH FOR TABLE ret_ban (active)the plan got fixed.
Thank you so much!
------------------------------
Sincerely,
Dennis
------------------------------
Dennis: I would recommend adding an index on pr_num with active and a separate index on the pr_value column to the ret_ban table and adding active and pr_value to your UPDATE STATISTICS HIGH statements run periodically. Currently there are no distributions on the ret_ban.active column at all, nor for the pr_value column, so the optimizer does not know how many rows might have a value of 'Y' for the active column nor how many might match rows in item_k_pr . With no index, and knowing that there are only 20 rows in this table, the engine is choosing to perform a sequential scan. I assume that this is some kind of staging or processing list table that is refreshed with new items to process or that are under process. Ideally, I would strongly recommend that you get my dostats utility and run that to update your statistics and data distributions periodically. Dostats does MUCH more than just performing a HIGH on indexed columns and I have often found that just running dostats can resolve similar issues to what you are seeing. Dostats is included in my open source package, utils2_ak, which you can download from my web site: My Utilities Instructions for building the package and all of the utilities it provides, is included, but on Linux, if that is your platform, you should be able to just type "make" or if you want edit the Makefile and set the INSTALLDIR parameter to the location where you want to install the utilities and type "make install". Most of my utilities will print out usage if you just run it with no arguments or with the -? flag. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Hi Dennis, many explanations from Art and Mike, don't need to be extended. What's about to go ahead with 14.10.FC10 or FC11 ? Did you try the same query with them on a test instance? Give him a try. Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion henri.cujass@leolo.com ------------------------------
Henri, Unfortunately, we haven't managed to upgrade to 14.10.FC6, the system run too slow, in spite of thorough testing beforehand. So we're stuck at 11.70. ------------------------------ Sincerely, Dennis ------------------------------
Hi Dennis, this is a fantastic challenge for me. I think the 14.10 is between 10 and 20% faster than the 11.70. Not slower. I'd be happy to take care of that ;-) (PM to me) Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion henri.cujass@leolo.com ------------------------------
Dennis: I agree with Henri. V14.10 is usually faster than v11.70 so you are likely hitting tuning and infrastructure issues that can be addressed. Henri's company, Mike Walker's, and my own are well positioned and capable of helping you with that if you want to try upgrading again. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------