db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <kristian.waa...@oracle.com>
Subject Re: Question on automatic statistics feature in
Date Tue, 24 May 2011 13:03:48 GMT
On 24.05.11 14:17, Bergquist, Brett wrote:
> Okay, so the " automatic update doesn't kick in until the table contains at least 100
rows" was the information that I was missing.  Is that somewhere I the release notes or documentation?

Hi Brett,

No, by choice, this hasn't been documented properly yet.

Getting this feature right is kind of hard, as you have to balance the 
usage of resources for statistics updates and user load. Different users 
may also have very different expectations.
The release notes only mention how to disable the feature and how to 
turn on logging. The community felt it would be useful to get some 
feedback from users before writing more documentation [1]. You have now 
given us an indication that users may want more information about how 
the statistics updates are triggered. Thanks!

In addition to the undocumented knob Knut Anders mentioned, there are 
three more:
  o derby.storage.indexStats.trace (default="none")
  o derby.storage.indexStats.debug.absdiffThreshold (default=1000)
  o derby.storage.indexStats.debug.lndiffThreshold (default=1.0)

These may very well be removed in the next Derby release. If people find 
that they need to use the undocumented knobs above, please let the 
community know.
All kinds of feedback regarding this feature is welcome.

FYI, the current triggering mechanism is based on prepared statements 
query compilation (only SELECTs and for queries using indexes) and row 
estimates. Using row estimates alone may be insufficient for some types 
of loads. If you don't prepare your statements, you probably should ;)


[1] May seem strange, but this is supposed to be a ~zero admin feature.

> In any case, this feature is greatly appreciated!  It will make the database much more
zero administration which my situation requires.  Thanks!
> Brett
> -----Original Message-----
> From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
> Sent: Tuesday, May 24, 2011 3:30 AM
> To: derby-user@db.apache.org
> Subject: Re: Question on automatic statistics feature in
> "Bergquist, Brett"<BBergquist@canoga.com>  writes:
>> I have been testing with the new automatic statistics feature in
>> and turned on the logging and see it being triggered as I
>> make changes to my database.  So this appears to be working.  What I
>> am surprised at however, is that I have tables in my database that
>> have out of date statistics (none) as reported by this query:
> [...]
>> The tables start empty but with indexes and then data gets added
>> later.  I thought that probably the new automatic statistics feature
>> would be triggered on a query of these tables but it does not seem to
>> be.  If the table already has statistics they seem to be updated.   I
>> could be wrong however and maybe my query is not sufficient to trigger
>> statistics update but I did do a query for a specific value of primary
>> key and saw nothing in derby.log.
> What's the size of the table? The automatic update doesn't kick in until
> the table contains at least 100 rows (the threshold can be tuned with
> the undocumented property derby.storage.indexStats.debug.createThreshold).
> There's also a possibility that the query you executed wasn't actually
> compiled, but just fetched from the statement cache. Derby doesn't check
> that the statistics are up to date on every execution. By default, it
> checks after 100 executions whether a recompile is necessary, and it's
> during the recompile the statistics update is scheduled. To eliminate
> this as the cause, you could call
> syscs_util.syscs_empty_statement_cache() before you execute the query
> against the table, and see if the statistics get created then.

View raw message