Limiting I/O Cost

I/O cost is based on the number of logical and physical accesses (“reads”) used during query processing. To determine the most efficient processing plan before execution, the SAP ASE optimizer uses both logical and physical resources to compute an estimated I/O cost.

SAP ASE uses the result of the optimizer’s costing formula as a unitless number; that is, a value not necessarily based on a single unit of measurement, such as seconds or milliseconds.

To set resource limits, you must understand how those limits translate into runtime system overhead. For example, you must know the effect that a query with a cost of x logical and of y physical I/Os has on a production server.

Limiting io_cost can control I/O-intensive queries, including queries that return a large result set. However, if you run a simple query that returns all the rows of a large table, and you do not have current statistics on the table’s size, the optimizer may not estimate that the query exceeds the io_cost resource limit. To prevent queries from returning large result sets, create a resource limit on row_count.

The tracking of I/O cost limits may be less precise for partitioned tables than for unpartitioned tables when SAP ASE is configured for parallel query processing. See Performance and Tuning Series: Query Processing and Abstract Plans > Parallel Query Processing.