Erwin,

Statistics are *usually* beneficial for the optimizer, but that is not
true 100% of the time.  For that reason a best practice is to save the
old stats prior to generating new ones.  That can easily be done with
the "statdump -o" command.  We recommend maintaining a minimum of 2-3
generation of revisions.  This can be extremely helpful when analyzing
performance problems because the histograms can provide a unique
insight into data distribution and what the optimizer might be
"thinking."  If there is a problem with new stats it is quick and easy
to restore the prior versions using the "optimizedb -i" command, using
the previously saved stats for the reload.

Other tips that we have found useful for best performance include:

1. Use the "-zu200 -zr200" flags to tell optimizedb to generate more
than the default 15 (or less) histogram cells (exact and inexact).  The
maximum number of cells has increased over time but Ingres is smart
enough to look at the "200" and create 499 cells (or whatever) so
setting it to the true limit is not important.

2. Use the "-zk" flag to optimize key columns.

3. Never optimize all columns in a table unless the there are few
attributes and they are all key / index.

4. Look at the "help table" output and if you find an attribute that
has a "average count per value" figure equal to ~25% or more of the
table consider removing stats from that column (statdump -zdl <dbname>
-r<table> -a<column>).  I have found that while the distribution may be
correct it can often cause the optimizer to perform an unnecesary table
scan.  On small tables it might not be a big deal, but in a VLDB
scenario it can be very detrimental.

5. Remember to "sysmod" your database after generating statistics.

Final tips & suggestions...

1. It is good to have a representative test environment where you can
test changes before making them in production.  Once you have something
that works you can just copy the stats over from test to production.
It's quick and minimizes production downtime.

2. Review QEPs to ensure that the change was beneficial.  For an
example on how to read QEPs please see
http://www.Comp-Soln.com/QEP_example.pdf

3. Tests really need to be controlled to demonstrate their
effectiveness (especially important for performance tuning and
benchmarking - two of our specialties).  In the past we followed the
scientific method of only chaning one variable at a time.  It works
great but is time consuming.  For the past 6-8 months we have been
doing more with the "Design of Experiements" approach (a Six Sigma
concept).  It can be challenging to design the test model (i.e., causal
factors) and then determining the impact of those factors based on the
test output, but it has proven itself to be a good shortcut to isolate
the final tweaks that can make the biggest impact.

Hope that helps.  Not all expert recommendations are bad!  :-)

Cheers.

Chip Nickolett (ChipN@Comp-Soln.com
US - Comprehensive Solutions (www.Comp-Soln.com)
UK - Comprehensive Solutions International, Ltd. (www.Comp-Soln.co.uk)