SAP Sybase Adaptive Server Enterprise ASE uses a cost-based optimizer which balances the cost of finding the best query execution plan with the costs of running the query in different ways.
To do this, it checks a histogram that identifies the data distribution, and uses this information to determine join orders, index selection, and more.
You might think that the server would keep this information up-to-date, but because of the resource cost, it does not. You will need to perform this critical task updating statistics as often as your data skews. ASE provides a number of options for updating statistics, depending on the specific needs you have. To update information about the distribution of key values in specified indexes, for all columns in an index, table, or partition, the syntax is:.
Your situation, of course, may be different. Here are a few considerations when choosing which option will work best in your circumstances:. When you increase the steps, it changes the number of histogram points that the optimizer has to work with. When would you do this? If you ever find your performance to be erratic, increasing the number of steps may be a good place to start. You can examine the statistics including time since the last update by querying the systabstats and sysstatistics table, or by using the optdiag utility from the OS prompt.
Some have claimed the update statistics command causes blocking and contention. In earlier versions of ASE, this may have sometime happened; however, in the newer releases of ASE, locking is minimal and this should not be a significant concern. In general, if you are updating statistics for a table that has allpages locking defined, the server places an intent lock at the table level, and a page lock on the page currently being scanned.
Locking level 1. As noted at the beginning, out-of-date statistics is a frequent cause of poor performance. Assess your circumstances and develop a plan for keeping your statistics up to date, so that the query optimizer can do its job.
The former creates histograms not only for the leading column of the index but for all columns specified in the index definition. You can also update the statistics for all columns of all indexes on the table by omitting the index name:. Note that it is not exactly the same as calling update index statistics for each index on the table since the latter updates the statistics of columns referenced by multiple indexes multiple times.
This is basically the same problem as when using update statistics on columns which are not the leading column of an index. The update table statistics is kind of a different beast.
This will set the range cell density to 0. Note that the next update statistics will reset the density information. Please refer to my previous post regarding data skew, their effect on queries and how to handle them: Data skew and query plans. Updates of statistics are a required maintenance activities if you want to keep your queries fast.
On the other hand, updating statistics also has a non-negligeable impact on performance and load. In general the leading column of indexes are the critical ones. The three sorts themselves are not performed in parallel. The value for N is any number between 1 and If update index statistics is used with just a table name, it updates statistics for all columns in all indexes of the table. Specifying the name of an unindexed column or the nonleading column of an index generates statistics for that column without creating an index.
Density statistics are created for all the prefix subsets of the columns of index es whose statistics are being updated. If you use update index statistics on a specific partition, you update global statistics implicitly as well. The partition statistics serve as input to global statistics creation and enable per-partition DDL operations. Global statistics are used by the optimizer.
0コメント