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.

• 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.
• Calculate the I/O Cost of a Cursor
  The cost estimate for processing a cursor is calculated at declare cursor time for all cursors except execute cursors, which is calculated when the cursor opens.
• Scope of the io_cost Limit Type
  A resource limit that restricts I/O cost applies only to single queries. If you issue several statements in a query batch, SAP ASE evaluates the I/O usage for each query.