Oracle Database - Buffer IO (Logical IO)

> Database > Oracle Database

1 - About

A buffer is a container for data.

A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs a physical I/O to copy the buffer from either the flash cache or disk into memory, and then a logical I/O to read the cached buffer.

db block gets and consistent gets statistics (that you can find for instance in the autotrace output) represent the logical io, the number of times you had to latch a buffer in order to inspect it. The less we latch, the better.

A query will generally perform consistent gets and no current get, then the term logical I/O refer often to it.

Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by:

The less logical I/O we can do, the better.

Advertising

3 - Modes

Oracle Database retrieves buffers from the database buffer cache in either one of the following modes:

4 - Type of Buffer

4.1 - Writes

The database writer (DBWn) process periodically writes cold, dirty buffers to disk. DBWn writes buffers in the following circumstances:

  • A server process cannot find clean buffers for reading new blocks into the database buffer cache.

As buffers are dirtied, the number of free buffers decreases. If the number drops below an internal threshold, and if clean buffers are required, then server processes signal DBWn to write.

The database uses the LRU to determine which dirty buffers to write. When dirty buffers reach the cold end of the LRU, the database moves them off the LRU to a write queue. DBWn writes buffers in the queue to disk, using multiblock writes if possible. This mechanism prevents the end of the LRU from becoming clogged with dirty buffers and allows clean buffers to be found for reuse.

  • The database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin.
  • Tablespaces are changed to read-only status or taken offline.

4.2 - Reads

When the number of clean or unused buffers is low, the database must remove buffers from the buffer cache. The algorithm depends on whether the flash cache is enabled:

  • Flash cache disabled

The database re-uses each clean buffer as needed, overwriting it. If the overwritten buffer is needed later, then the database must read it from magnetic disk.

  • Flash cache enabled

DBWn can write the body of a clean buffer to the flash cache, enabling reuse of its in-memory buffer. The database keeps the buffer header in an LRU list in main memory to track the state and location of the buffer body in the flash cache. If this buffer is needed later, then the database can read it from the flash cache instead of from magnetic disk.

When a client process requests a buffer, the server process searches the buffer cache for the buffer. A cache hit occurs if the database finds the buffer in memory. The search order is as follows:

  • The server process searches for the whole buffer in the buffer cache. If the process finds the whole buffer, then the database performs a logical read of this buffer.
  • The server process searches for the buffer header in the flash cache LRU list. If the process finds the buffer header, then the database performs an optimized physical read of the buffer body from the flash cache into the in-memory cache.
  • If the process does not find the buffer in memory (a cache miss), then the server process performs the following steps:
    • Copies the block from a data file into memory (a physical read)
    • Performs a logical read of the buffer that was read into memory

The figure below illustrates the buffer search order. The extended buffer cache includes both the in-memory buffer cache, which contains whole buffers, and the flash cache, which contains buffer bodies. In the figure, the database searches for a buffer in the buffer cache and, not finding the buffer, reads it into memory from magnetic disk.

In general, accessing data through a cache hit is faster than through a cache miss. The buffer cache hit ratio measures how often the database found a requested block in the buffer cache without needing to read it from disk.

The database can perform physical reads from either a data file or a temp file. Reads from a data file are followed by logical I/Os. Reads from a temp file occur when insufficient memory forces the database write data to a temporary table and read it back later. These physical reads bypass the buffer cache and do not incur a logical I/O.

Advertising

5 - Buffer Touch Counts

The database measures the frequency of access of buffers on the LRU list using a touch count. This mechanism enables the database to increment a counter when a buffer is pinned instead of constantly shuffling buffers on the LRU list.

The database does not physically move blocks in memory. The movement is the change in location of a pointer on a list.

When a buffer is pinned, the database determines when its touch count was last incremented. If the count was incremented over three seconds ago, then the count is incremented; otherwise, the count stays the same. The three-second rule prevents a burst of pins on a buffer counting as many touches. For example, a session may insert several rows in a data block, but the database considers these inserts as one touch.

If a buffer is on the cold end of the LRU, but its touch count is high, then the buffer moves to the hot end. If the touch count is low, then the buffer ages out of the cache.

6 - Buffers and Full Table Scans

When buffers must be read from disk, the database inserts the buffers into the middle of the LRU list. In this way, hot blocks can remain in the cache so that they do not need to be read from disk again.

A problem is posed by a full table scan, which sequentially reads all rows under the table high water mark. Suppose that the total size of the blocks in a table segment is greater than the size of the buffer cache. A full scan of this table could clean out the buffer cache, preventing the database from maintaining a cache of frequently accessed blocks.

Blocks read into the database cache as the result of a full scan of a large table are treated differently from other types of reads. The blocks are immediately available for reuse to prevent the scan from effectively cleaning out the buffer cache.

In the rare case where the default behavior is not desired, you can change the CACHE attribute of the table. In this case, the database does not force or pin the blocks in the buffer cache, but ages them out of the cache in the same way as any other block. Use care when exercising this option because a full scan of a large table may clean most of the other blocks out of the cache.

7 - How to decrease the logical I/O

7.1 - Statistics

You must compute/update the statistic to give all information to the query optimizer in order to find the best execution plan.

7.2 - Query Tuning

The query tuning consist to find the better execution plan.

To decrease the logical io, you have in this way several possibilities :

Oracle Database - Example of query Tuning (to decrease the logical I/O)

Advertising

7.3 - Influence of the Array Size Parameter

The array size is the number of rows fetched (or sent, in the case of inserts, updates and deletes) by the server at a time.