db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Question on automatic statistics feature in 10.8.2.1
Date Tue, 24 May 2011 12:17:37 GMT
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?

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 10.8.2.1

"Bergquist, Brett" <BBergquist@canoga.com> writes:

> I have been testing with the new automatic statistics feature in
> 10.8.2.1 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.

-- 
Knut Anders



Mime
View raw message