Oracle Database - Buffer cache

Card Puncher Data Processing


For many types of operations, Oracle Database uses the buffer cache to store data blocks read from disk. Oracle Database bypasses the buffer cache for particular operations, such as sorting and parallel reads.

Buffer Cache is an other name for logical oracle RAM buffer and is a part of the in System Global Area (SGA).

The “buffer cache”:

  • caches the physical blocks read (hold a copies of the data blocks of the data files)
  • is shared by all concurrent process



alter system flush buffer_cache

Many people think that they must flush the buffer cache before tuning, to emulate the real world.

If you tune with logical_io (consistent_gets) in mind, the physical_io will take care of themselves over time (assuming you ensure an even distribution of I/O across disks).

Flushing the buffer cache gets rid of tons of information that rightly would be there in a production environment when you run the query.

Documentation / Reference

Discover More
Db File Scattered Read
Oracle Database - 'db file scattered read' wait event

db file scattered read is a wait event. It's a multiblock read into many discontinuous SGA buffers This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting...
Oracle Segment Extent Data Block
Oracle Database - (Data|Db|Logical|Oracle) Block or Page

Articles which talk : block management. ?? At the finest level of granularity in the logical structure of an Oracle Database, the data is stored in data blocks. The data block sizes should be a...
Card Puncher Data Processing
Oracle Database - (Db Block Get|Current Get) Buffer Mode (CU)

db block get is a buffer retrieval mode. It's also know as current mode get. In this mode, the block is retrieved in the buffer cache. The block is then known as a CURRENT block. You will see these...
Card Puncher Data Processing
Oracle Database - (Memory) Cache (Tables, )

Set the tablespace offline and online or flush the buffer cache or flush the shared pool Small tables are automatically cached according to this criteria : Table Caching Criteria Table Size Size...
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

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...
Card Puncher Data Processing
Oracle Database - Buffer Pool

The buffer pool is a category assigned to a segment to manage the lifetime of a segment in the buffer cache. Three separate buffer pools: KEEP pool (to maintain frequently accessed segments in...
Card Puncher Data Processing
Oracle Database - Consistent (Read get|Buffer Mode) (CR)

A consistent read get is a retrieval of a read-consistent version of a block from the buffer_cache and then may include read asides to UNDO (rollback segments). A query will generally perform “consistent...
Card Puncher Data Processing
Oracle Database - DB_FILE_MULTIBLOCK_READ_COUNT Parameter

This parameter determines how many database blocks are read in a single I/O (with a single operating system READ call) during a: full table scan or index fast full scan. SQL parallel execution is...
Card Puncher Data Processing
Oracle Database - Database Writer Process (DBWn)

Database writer process (DBWn) is a background process that writes buffers in the database buffer cache to data files. Modified or new data is not necessarily written to a datafile immediately. To reduce...

Share this page:
Follow us:
Task Runner