Re: Dynamic Optimizer problems??
Ronald E. White <ronwhite@erols.com> wrote in article
<328BAF1B.1805@erols.com>...
> Has anyone had any problems with Informix's dynamic optimizer? We're
> running Informix with PeopleSoft and seem to be having problems with the
> dynamic optimizer going to the BIG table to do its first search, then
> going to the little table last. When on those rare occassions it goes to
> the little table first and then to the BIG table the job completes in 5
> to 10 minutes. Otherwise it takes several hours.
>
> <snip>
Be sure you have done an UPDATE STATISTICS on the tables in question. Not
just a gneral
UPDATE STATISTICS for the entire database, but the specific recommendedsequence of
LOW, MEDIUM and HIGH as recommended in the release notes which you can find
in
$INFORMIXDIR/release/SERVERS_*. This can take quite some time on a REALLY
BIG
table, but can improve the optimizer's plan daramatically! You only have
to refresh the
statistics every one in a while, depending on how much the data in the
table changes.
The recommended sequence is something like this:
1) UPDATE STATISTICS MEDIUM for all columns which DO NOT head an index.
2) UPDATE STATISTICS HIGH for each column which heads an index.
3) UPDATE STATISTICS LOW for each multi-column index.
One of our clients does this on every table of a 400MB (and growing)
database every night. At the
moment the enitre sequence completes in about 15 minutes. This, of course,
will vary based on
your hardware, configuration parameters, table fargmentation, etc.
I have a shell script which works with AWK and dbaccess which automatically
performs the
recommended sequence of UPDATE STATISTICS commands for a specified table.
If there
is enough general interest in this, I'll post it somewhere on our Web site.
As noted by another observer, setiing OPTCOMPIND to 0 will alter the query
optimizer's
choices as well. Read up in the manual for more about the overall effect
of OPTCOMPIND.
HTH,
Irwin Goldstein
Objective Software Systems, Inc.
http://www.objectsoft.com