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: Selecting a few rows is really slow when ordering by primary key
Date Thu, 02 Feb 2012 10:14:18 GMT
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

Mime
View raw message