OPTCOMPIND
Colin Dawson asked what OPTCOMPIND value others used for OLTP. Superboer advised 0, since 2 may let the optimizer choose sequential scans and cause locking and performance problems when statistics are stale. Ben Thompson measured 0 at least 10% faster than 2 on IDS 7.31. Another poster also used 0, calling the optimizer unreliable with 2. Consensus was 0.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Apologies if this has been discussed before..... I'm interested in people's opinions on what they have set this parameter to for OLTP systems and why they used that setting. I'm not interested in what the parameter means, I know that, I can RTFM as well as anybody. FYI: I am using 0 on one system and 2 on another and there doesn't seem to be any noticable degradation in response times, query times are very similar Regards Colin There are 10 types of people in the world, those that understand binary and those that don't sending to informix-list
oltp use optcompind=0!!! if small table with indexes and current stats you will get index lookups. if set to 2 the optimizer may choose seq scans causing locking problems etc. and when your table grows and you do not run update stats it still will seq scan causing perf problems. so 0 for otlp Superboer
Colin Dawson wrote: > Apologies if this has been discussed before..... > > I'm interested in people's opinions on what they have set this parameter to > for OLTP systems and why they used that setting. I'm not interested in what > the parameter means, I know that, I can RTFM as well as anybody. > > FYI: I am using 0 on one system and 2 on another and there doesn't seem to > be any noticable degradation in response times, query times are very similar Some time ago I tested altering OPTCOMPIND on a IDS 7.31.UC5 system running on AIX 4 with our OLTP application and I found that the 0 setting was significantly faster than the 2 setting, at least 10% faster as I recall. Clearly it will depend on the queries you run. I've not revisited it since we started using 9.30, 9.40 and now 10.00. Ben.
I set to to 0. I have seen loads of questions on here in the past about upgrading to IDS and finding everything running slowly. It appears that sometimes even though 2 should mean least cost the optimizer gets it wrong and will not use indexes. Setting OPTCOMPIND to 0 avoids these problems. It may work ok for a while and then in production you find a query that suddenly takes forever since the optimizer is not using an index. Personally I think the optimizer is buggy and settting to 0 avoids these bugs. It make work most of the time but then you hit a problem and performance nosedives.