To develop appropriate limits for I/O cost, use set commands to determine the number of logical and physical reads required for some typical queries:
set showplan on displays the optimizer’s cost estimate. Use this information to set preexecution time resource limits. A preexecution time resource limit violation occurs when the optimizer’s I/O cost estimate for a query exceeds the limit value. Such limits prevent the execution of potentially expensive queries.
set statistics io on displays the number of actual logical and physical reads required. Use this information to set execution-time resource limits. An execution-time resource limit violation occurs when the actual I/O cost for a query exceeds the limit value.
Statistics for actual I/O cost include access costs only for user tables and worktables involved in the query. Adaptive Server may use other tables internally; for example, it accesses sysmessages to print out statistics. Therefore, there may be instances when a query exceeds its actual I/O cost limit, even though the statistics indicate otherwise.
In costing a query, the optimizer assumes that every page needed requires a physical I/O for the first access and is found in the cache for repeated accesses. Actual I/O costs may differ from the optimizer’s estimated costs, for several reasons.
The estimated cost is higher than the actual cost if some pages are already in the cache or if the statistics are incorrect. The estimated cost may be lower than the actual cost if the optimizer chooses 16K I/O, and some of the pages are in 2K cache pools, which require many 2K I/Os. Also, if a big join forces the cache to flush its pages back to disk, repeated access may require repeated physical I/Os.
The optimizer’s estimates are inaccurate if the distribution or density statistics are out of date or cannot be used.
