Re: Optimizer Issues with 10.00.xC8

On Feb 29, 4:45 am, "Thomas J. Girsch" <tgir...@xxxxxxxxxxxxxxxx>
wrote:
Has anyone else been having optimizer issues with IDS 10.00.xC8?

We've got two applications on two different hardware platforms where the
database backend was recently upgraded to IDS 10.00.FC8 (one DB is on
Solaris 8, the other is on AIX 5.2), and in both cases, we've started
getting queries that have long been running just fine that are now
picking a bad path.  These queries used to take seconds and now take
minutes.

In the AIX case, we know that the statistics are less-than-current (the
table is truncated and reloaded WITHOUT updating stats right before the
query runs), but the point is that in IDS 10.00.FC6, these queries ran
fine under these circumstances (and finished in about 6 seconds), but on
FC8, under the exact same circumstances, it takes nearly 3 minutes.
I've tested various scenarios, and the query actually runs better with
NO STATS AT ALL than it does with the out-of-date stats it has (also not
true in FC6).

In the Solaris case, I have a query that looks like a textbook case for
distributions.  It's a fairly sizable table, with millions of rows.
There are two indexes:  one is a composite, unique index on colA, colB
(integer, smallint) and the other is a nonunique index on colC
(integer).  We have a query that selects against this table where colA =
X and colC = Y.  The data in colA has high selectivity, whereas the data
in colC has very low selectivity (something like half the records in the
table have colC = 0).  If I run full statistics with distributions
(including HIGH on colA and High on colC), the optimizer picks the index
on colC every time, which is Very Bad, and takes forever.  If I drop
distributions, it (correctly) picks the composite index on (colA, colB)
and runs very quickly.  This is precisely the opposite of the behavior I
would expect.  For what it's worth, it doesn't seem to matter if
OPTCOMPIND is 0 or 2 for this test, it still picks the wrong path when
it has distributions.

Has anyone else had a similar experience?  Any suggestions?

Thanks in advance,

- TJG

Thomas,

We've recently seen a query that showed exactly the same symptoms
after upgrading from FC5 to FC8 on AIX 5.3. The query was rewritten as
it was poorly written to start with but it had never showed any
performance problems on the previous version. Has a call been logged
with IBM regarding this? Without digging into too much detail, it does
sound like a bug to me.

RoB
.

Relevant Pages

  • Re: Optimizer Issues with 10.00.xC8
    ... one is a composite, unique index on colA, colB and the other is a nonunique index on colC. ... If I run full statistics with distributions, the optimizer picks the index on colC every time, which is Very Bad, and takes forever. ...
    (comp.databases.informix)
  • Re: Optimizer Issues with 10.00.xC8
    ...  one is a composite, unique index on colA, colB ... and the other is a nonunique index on colC ...  If I run full statistics with distributions ... You may indeed have found a bug in the 10.00.xC8 optimizer and should ...
    (comp.databases.informix)
  • Re: Optimizer Issues with 10.00.xC8
    ... First, if the selectivity of colA is so good, but with millions of rows the default resolution of the data distributions may be too low. ... Whereas for ColC, nearly 60% of the rows have a value of ColC = 0. ... what I've found is that queries run from dbaccess always choose the best index. ... The best solution for this is to UPDATE STATISTICS LOW...DROP DISTRIBUTIONS immediately before the truncate/load operation, run that initial round of time sensitive queries without data distributions, and reinstate the distributions as soon after the initial round of queries as is practical. ...
    (comp.databases.informix)
  • Optimizer Issues with 10.00.xC8
    ... Has anyone else been having optimizer issues with IDS 10.00.xC8? ... one is a composite, unique index on colA, colB and the other is a nonunique index on colC. ... If I run full statistics with distributions, the optimizer picks the index on colC every time, which is Very Bad, and takes forever. ...
    (comp.databases.informix)
  • Re: Text column in select query
    ... SELECT TOP 10 ID, colA, colB, colC ... remove the text column from the select query it takes a second, ...
    (microsoft.public.sqlserver.clients)