Oninit cdi / Archive
Talk to an expert →

OPTCOMPIND

Posted in 2005

Topics: General Discussion

Colin Dawson — — source: Usenet: comp.databases.informix

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

↪ replying to Colin Dawson
Superboer — — source: Usenet: comp.databases.informix

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

↪ replying to Colin Dawson
Ben Thompson — — source: Usenet: comp.databases.informix

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.

↪ replying to Colin Dawson
david@smooth1.co.uk — — source: Usenet: comp.databases.informix

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.