In our product, we are running a database with the SQL Anywhere 220.127.116.1195. This database receives changes from another database through replication using SQL Remote every 30 seconds. So the data is more or less permanently changing . Another process deletes data from the database which is detected to be ‘old’. Therefore the database is not increasing in size. The size of the database under test is about 20 GB.
In our test environment we permanently execute various queries against the database. After several days, we found, that the queries are getting slower and slower. The same queries that took a few seconds at the beginning, take several minutes or even up to hours!
After we are performing a
What’s wrong? We assumed that the statistics are maintained by the engine itself. Just because this is described as a new feature for the Version 12 of SQL Anywhere: self-healing statistics management (http://download.sybase.com/presentation/TW2011/DATSQL1.pdf)
How can we keep the performance long term? Are there any special settings we need to configure? There were similar threads in this forum, but without any final solution:
We have saved a database with such corrupt statistics (slow queries). So we can provide query plans, dumps of SYSCOLSTATS (taken after
But the main question is: How do the statistics become corrupt?
Thanks for any ideas.
asked 11 Jul '13, 10:00
Have you thought about fiddling around with the following options:
As to the docs, both options are ON by default and should only be set to OFF in particular cases, and please note, personally, I'm not at all pretending to understand anything about this documented difference:
However, this might be a chance to prevent the system from undesired statistic updates and might be helpful if you find out that your SQL Remote generated "DML flood" (which will consist of many, many single-row DML operations, AFAIK) may be inappropriate as basis for collecting stats.
AFAIK, both options can be set for individual users and/or connections - to allow further fine-tuning.
Note: I'm much more wild-guessing than Breck claims to be...