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

-----Original Message-----
From: owner-informix-list (AT) iiug (DOT) org [mailtowner-informix-list (AT) iiug (DOT) org]
On Behalf Of Colin Dawson
Sent: 17 August 2005 09:57 AM
To: informix-list (AT) iiug (DOT) org
Subject: OPTCOMPIND


My systems are set to 2, after testing it. When we upgraded a while
back, I tried both 0 and 2, and 0 proved to be very slow on my specific
system (running on 7.31 FD3)

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:
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.

-----Original Message-----
From: owner-informix-list (AT) iiug (DOT) org [mailtowner-informix-list (AT) iiug (DOT) org]
On Behalf Of Superboer
Sent: 17 August 2005 11:46 AM
To: informix-list (AT) iiug (DOT) org
Subject: Re: OPTCOMPIND



My system is OLTP, and 0 just brings my system to a grinding halt


sending to informix-list

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.

I've seen cases to support this.

Not much ones I admit.


J.

david (AT) smooth1 (DOT) co.uk escribis: