Adaptive Server uses a cost-based query optimizer to choose the best plan for a particular query. The optimizer estimates the cost of different plans based on statistics about the tables, indexes, partitions, and columns referenced in a query. Cost is computed in terms of I/O and CPU time. The optimizer then chooses the query plan method that has the lowest cost. Inaccurate statistics lead to incorrect cost estimates, and may result in a suboptimal choice of plans and reduced performance.
Some statistics, such as the number of pages or rows in a table (stored in systabstats), are updated automatically during query processing. Other statistics are updated only when update statistics runs, or when indexes are created. Examples of this are the histograms on column and density information, stored in sysstatistics.
Adaptive Server 15.0 is more susceptible to incorrect statistical data than earlier Adaptive Server versions, because multiple algorithms are used for sorting, grouping, unions, joins, and other operations. In addition, Adaptive Server 15.0 uses statistics in more ways than in Adaptive Server 12.x. For example, Adaptive Server 15.0 uses statistics to determine the join order in multitable queries.
Sybase recommends that you maintain up-to-date histograms for all columns referenced in where clauses, both when the where clauses are used as join predicates and as search arguments. Use the statistics advisor in QPTune to identify critical and missing statistics.
