What’s the Point of Oracle Checkpoints?

What’s the Point of Oracle Checkpoints?

By admin, on November 24th, 2010

Buffer Cache Basics

The buffer cache component structures are:

• Buffers
  • Each buffer may hold an image of one data block at any one time
• Buffer headers
  • Store metadata about contents of the buffers
  • Act as cache management structures
• Buffer pools
  • Collection of buffers used for the same purpose and managed accordingly
• Working set
  • All or part of a buffer pool
  • Assigned to a DBWn process

Buffer Management

• Cached buffers managed by doubly linked lists:
• REPL
  • Buffers containing block images being used
• REPL-AUX
  • Buffers ready to be used for I/O or CR build
• WRITE and CKPT-Q
  • Dirty Buffers requiring I/O
• WRITE-AUX
  • Dirty Buffers with I/O in progress
• Touch count is used to decide the initial insertion location in the REPL chain
• AUX lists avoid wasteful scanning

Redo and the Buffer Cache

• Block modification dirties the buffer containing the block image and generates redo
• A buffer becomes dirty at a particular RBA which is a point in the redo stream
• Redo written by LGWR makes the corresponding part of the redo log file “active”
• Dirty block images written by DBWn makes the corresponding part of the redo log file “inactive”
• Redo is always written prior to the corresponding block images
• Size of active redo in the log file influences instance and crash recovery time
• Trade-off between performance and recovery time

Buffer Cache I/O

• Servers look for an available buffer on REPL-AUX then read a data block into selected buffer
  • Buffer gets moved from REPL-AUX to REPL
  • If block is modified, buffer is added to CKPT-Q
  • Servers move dirty buffers to WRITE during free buffer search
• DBWn writes dirty buffer contents to database
  • Buffer gets moved from WRITE to WRITE-AUX
  • Once block written:
    • Buffer is moved back to REPL-AUX
    • Buffer taken off CKPT-Q
• DBWn writes upon request
  • Make free buffers
  • Checkpoint

Parallel Query Checkpoint

• Writes block images to the database for all dirty buffers belonging to objects accessed by the query from all instances
• Statistics updated:
  • DBWR checkpoint buffers written
  • DBWR checkpoints
• Caused by:
  • Parallel Query
  • Parallel Query component of PDML or PDDL
  • Mandatory for consistency

Log Switch Checkpoint

• Writes the contents of “some” dirty buffers to the database
• Statistics updated:
  • DBWR checkpoints
  • DBWR checkpoint buffers written
  • background checkpoints started
  • background checkpoints completed
• Controlfile and datafile headers are updated
  • CHECKPOINT_CHANGE#