On 01.02.2012 14:51, Rick Hillegas wrote:
Hi Simon,
Glad to hear that regenerating the statistics fixed your problem. More
inline...
On 2/1/12 4:07 AM, Simon Chatelain wrote:
...
But one small question remains, do you think that it is enough to run
SYSCS_UPDATE_STATIS
TICS only once, or should I plan to run this procedure at regular
interval (let's say once a week or once a month) in case the
statistics are not correctly updated as new rows are inserted in the
database ?
If you upgrade to 10.8, the statistics should be regenerated
automatically. The statistics are supposed to be recalculated when the
number of rows in the table is too far out of sync with the number of
rows estimated by the statistics. "Too far out of sync" means that the
ratio between the two numbers is more than e. If statistics are not
being generated often enough, you can adjust the triggering ratio by
setting the following Derby property:
derby.storage.indexStats.debug.lndiffThreshold
That property defaults to be 1.0. By setting it to a value between 0.0
and 1.0, you should speed up the rate at which Derby recalculates
statistics. By setting the property to a larger value, you should slow
down the rate at which Derby recalculates statistics. Let us know if
you think that statistics are not being regenerated often enough. This
is a new feature which we are eager to tune and improve.
You may want to set derby.storage.indexStats.log=true, and more
importantly, derby.storage.indexStats.debug.trace=true. Run you app for
a while, then grep for "{istat}" in derby.log.
This will tell you if automatic calculation is being triggered.
Although strongly discouraged for production environments, you can try
to set lndiffThreshold to zero, or a very small value, to schedule an
update on every occasion, i.e. when you compile a statement like "select
... from tbl where indexed_column = ?".
Note that the istat daemon will only be triggered when the table changes
in size. If you do updates that changes the index key distribution
significantly you'll want to generate new statistics manually/periodically.
Regards,
--
Kristian
Hope this helps,
-Rick
