AUTO_READAHEAD configuration parameter

onconfig.std value

Not set. If the AUTO_TUNE configuration parameter is set to 1, read ahead is performed automatically in the standard mode.

Values

The general format of the AUTO_READAHEAD value is:

<mode>,<batch_size>,<threshold>

Both <batch_size> and <threshold> are optional, and default to values that should be correct for most applications. They can be tuned if necessary however, and may be done so dynamically.

mode

An integer from 0 – 2.

0 = Disable automatic read-ahead requests.

1 = Enable automatic read-ahead requests in the standard mode. The database server automatically processes read-ahead requests only when a query waits on I/O.

2 = Enable automatic read-ahead requests in the aggressive mode. The database server automatically processes read-ahead requests at the start of the query and continuously through the duration of the query.

batch_size

An integer from 4 – 4096.

When a scanning thread requests read-aheads, a separate thread (a read-ahead daemon) determines which pages are most likely to be useful to the scanner and primes the buffer cache with a batch of them. The default batch_size is 128.

threshold

A whole-number percentage from 1-100.

If the read-ahead daemon does not stay ahead of the scanner, the scanning thread will wait unnecessarily for each batch of I/O. At the same time if the daemon gets too far ahead of the scanner some of its work is likely to be wasted. The scan may complete before all cached pages are processed, for example, or another scanner may re-use a buffer before the earlier scan has a chance to get to it.

As the reader processes the current batch of cached pages, ideally the next batch will be read just before the current batch is consumed. A threshold value of 20 means the next batch of asynchronous reads will be triggered when 20% of the current batch remains unprocessed. The default threshold is 50%.

separators

Separate the mode, batch size, and threshold values with commas.

takes effect

After you edit your onconfig file and restart the database server.

When you reset the value dynamically in your onconfig file by running the onmode -wf command.

When you reset the value in memory by running the onmode -wm command.

If an AUTO_READAHEAD value is not set in your current onconfig file and you edit the AUTO_TUNE configuration parameter and restart the database server

Usage

Automatic read-ahead operations help improve query performance by issuing asynchronous page requests when the database server detects that the query is encountering I/O. Asynchronous page requests can improve query performance by overlapping query processing with the processing necessary to retrieve data from disk and put it in the buffer pool.

Generally, the default value of 1 is appropriate for most production environments.

While there are no specific circumstances in which aggressive read-ahead operations perform significantly better than standard read-ahead operations, aggressive read-ahead might be slightly more effective:

• For some scans that read a small amount of data
• In situations in which you switch between turning read-ahead off for small scans and on for longer scans
• For scans that look only at a small number of rows, because the server performs read-ahead operations immediately rather than waiting for the scan to encounter I/O.

For scans that might turn read-ahead operations off and on because the scan hits pockets of cached data, aggressive read-ahead operations do not turn off read-ahead operations.

Use aggressive read-ahead operations only in situations in which you tested both settings and know that aggressive read-ahead operations are more effective. Do not use aggressive read-ahead operations if you are not sure that they are more effective.

You can use the AUTO_READAHEAD environment option of the SET ENVIRONMENT statement of SQL to enable or disable the value of the AUTO_READAHEAD configuration parameter for a session.