Identify I/O Costs

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.

Statistics for actual I/O cost include access costs only for user tables and worktables involved in the query. SAP ASE 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.