Read-ahead operations

The database server automatically reads several pages ahead of the current pages that are being processed for a query, unless you disable automatic read ahead operations. Reading ahead enables applications to run faster because they spend less time waiting for disk I/O.

Automatic read-ahead requests for pages to be brought into the bufferpool cache during sequential scans of data records improves the performance of a query, including OLTP queries and index scans, when the server detects that the query is encountering I/O.

By default, the database server automatically determines when to issue read-ahead requests and when to stop based on when the query is encountering i/o from disk:
  • If queries encounter I/O, the server issues read-ahead requests to improve the performance of the query. This performance improvement occurs because read-ahead requests can greatly increase the speed of database processing by compensating for the slowness of I/O processing relative to the speed of CPU processing.
  • If queries are mostly cached, the server detects that no I/O is occurring and does not read ahead.
Use the AUTO_READAHEAD configuration parameter to change the automatic read-ahead mode or to disable automatic read ahead for a query. You can:
  • Dynamically change the value of the AUTO_READAHEAD configuration parameter by running an onmode -wm or onmode -wf command.
  • Run a SET ENVIRONMENT AUTO_READAHEAD statement to change the mode or enable or disable automatic read-ahead for a session.

You can use the onstat -p command to view database server reads and writes and monitor number of times that a thread was required to wait for a shared-memory latch. The RA-pgsused output field shows the number of pages used that the database server read ahead and monitor the database server use of read-ahead.

Use the onstat -g rah command to display statistics about read-ahead requests.