In this post, originally written by Glenn Paulley and posted to sybase.com in November of 2009, Glenn talks about statistics management in SQL Anywhere, a key feature that enables SQL Anywhere to operate without the need for a DBA to tune performance on the database.
SQL Anywhere has offered autonomic, self-managing statistics collection since 1992 [1]. In SQL Anywhere 12, we are going one step further with the introduction of a "statistics governor" that makes SQL Anywhere column histograms both self-monitoring and self-healing. Self-healing statistics management in SQL Anywhere 12 includes the:
- recording and categorization of selectivity estimation errors in queries;
- automatic, autonomous correction of statistics errors with low overhead; and
- autonomic monitoring and determination of column histogram maintainability.
Determining error
In SQL Anywhere 12, the server monitors the amount of estimation error with each predicate in every search condition executed during query processing. The difference between actual and estimated selectivities is slightly biased against errors with small selectivities to avoid needless corrections. With this adjusted error, the server computes an error metric for each column histogram based on the number of predicates over that column and the amount of error. This metric is biased towards predicates that encounter significant differences between estimated and actual values, involving step functions when the error is between 20 and 35%, and another step when the error is greater than 35%. If this computed metric is greater than a threshold value, and the server has encountered more than 20 predicates involving that column, then the column histogram is considered a candidate for repair.
Repairing a column histogram
Once the server determines that a column histogram needs repair, the server has three methods at its disposal to attempt to correct the problem. They are (in order of attempts):
- Piggybacking column statistics upon other queries over the same table data;
- re-creating the column histogram from scratch using index statistics (for indexed columns); and
- automatically performing a sampling table scan during periods that the server is relatively idle using a daemon process.
With (1) the server will attempt to gather column statistics by utilizing a scan operator from another query. If the (table or index) scan operator processes at least 70% of the rows in the table, the existing histogram is replaced outright; if less, the existing histogram is adjusted based on the selectivities of the actual values encountered, adjust for the unobserved percentage of the table. For string histograms, the replacement and correction policies are different since the underlying histogram implementation differs substantially from numeric values.
If the server is unable to gather updated statistics via a piggybacked scan - perhaps because the application workload does not contain a query that scans the table in its entirety - then the server will attempt to recreate the column histogram using the upper levels of a primary key, foreign key, or secondary index. If the column is not a leading column of any index, then as a last resort the server will scan the table using a background process. This background process will perform a stratified table scan of 100 + 1% of the table's pages. Sampling is done by partitioning the table pages into n equal-size blocks, where nis the number of pages to be sampled; then the server randomly selects one sample page from each block to use in computing a replacement histogram.
Self-monitoring statistics usage
The server automatically persists updated histograms - which are stored in the ISYSCOLSTAT catalog table - at least every 30 minutes (and during every CHECKPOINT). This is performed by a daemon known as the statistics flusher. In addition to the above self-healing mechanisms, SQL Anywhere 12 includes a statistics monitoring daemon, called the statistics cleaner, that tracks statistical errors over time. If the monitoring daemon determines that a particular histogram continuously requires rebuilding because its error metric continues to remain high, the daemon will automatically execute a DROP STATISTICS statement and will disable auto-creation for that histogram. While the histogram is unavailable, the query optimizer will rely exclusively on index statistics or magic values for selectivity estimation, rather than utilize the (erroneous) values contained in the histogram which are primarily caused by excessive concurrent "churn" in the data from simultaneously-executing connections. At a later point, the server will automatically re-create the column histogram, using one of the above three reconstruction methods, in an effort to correct the anomaly. In our experience, SQL Anywhere's self-managing statistics management is robust and is capable of handling a wide variety of workloads and update scenarios. Nonetheless, various aspects of statistics collection can be controlled, if necessary, by the DBA, and this is also true with both the statistics flusher and statistics cleaner processes. The behaviours of both tasks are customizable through the use of the sa_server_option system procedure.
[1] I. T. Bowman et al. (April 2007). SQL Anywhere: A Holistic Approach to Database Self-Management. In Proceedings, 2nd International IEEE Workshop on Self-Managing Database Systems, Istanbul, Turkey.
