Tuning the Buffer Cache

Objectives

		After completing this lesson, you should be able to
		do the following:
		Describe how the buffer cache is managed
		Calculate and tune the buffer cache hit ratio
		Tune the buffer cache hit ratio by adding or removing buffers
		Create multiple buffer pools
		Size multiple pools
		Monitor buffer cache usage
		Make appropriate use of table caching
		Diagnose LRU latch contention
		Avoid free list contention

Overview

Managing the Data Buffer Cache

Managing the Data Buffer Cache

Tuning Goals and Techniques

		Tuning goals:
			Servers find data in memory
			90% hit ratio for OLTP
		Tuning techniques:
			Increase buffer cache size
			Use multiple buffer pools
			Cache tables
			Bypass the buffer cache for sorting and parallel reads

Diagnostic Tools

Measuring the Cache Hit Ratio

Guidelines for Using the Cache Hit Ratio

	Hit ratio peaks because of data access methods:
		Full table scans
		Data or application design
		Large table with random access
		Uneven distribution of cache hits
	Guidelines for increasing the cache size:
		Cache hit ratio is less than 0.9
		No undue page faulting
		Previous increase was effective

Using Multiple Buffer Pools

Defining Multiple Buffer Pools

Defining Multiple Buffer Pools

Enabling Multiple Buffer Pools

Keep Buffer Pool Guidelines

		Tuning goal: Keep blocks in memory
		Size: Hold all or nearly all blocks
		Tool: ANALYZE ... ESTIMATE STATISTICS

Recycle Buffer Pool Guidelines

		Tuning goal: Eliminate blocks from memory when transactions have completed
		Size: Hold only active blocks
		Tool: V$CACHE

Recycle Buffer Pool Guidelines

	Tool: V$SESS_IO

Calculating the Hit Ratio for Multiple Pools

Identifying Candidate Pool Segments

		KEEP Pool
			Blocks repeatedly accessed
			Segment size is less than 10% of default buffer pool size
		RECYCLE Pool
			Blocks not reused outside of transaction
			Segment size is more than twice the default buffer pool size

Dictionary Views with Buffer Pools

Other Performance Indicators

Caching Tables

		Enable caching during full table scans by:
		Creating the table with the CACHE clause
		Altering the table with the CACHE clause
		Using the CACHE hint in a query
		Guidelines: Do not overcrowd the cache.

LRU Latches

		LRU latches regulate the least recently used (LRU) lists used by the buffer cache.
		By default, the Oracle server sets the number of LRU latches to one-half the number of CPUs, with a minimum of one.
		Each latch controls a minimum of 50 buffers.

LRU Latch Tuning Goals

		Ensure there are a sufficient number of LRU latches for the data buffer cache, so that contention between server processes is minimized.
		Balance the number of latches with the number of CPUs.
		Set one DBWn process for each latch.

Diagnosing LRU Latch Contention

Resolving LRU Latch Contention

		If the hit percentage for the LRU latch is
		less than 99%:
		Increase the number of LRU latches by setting the parameter DB_BLOCK_LRU_LATCHES
		The maximum number of latches is the lower of:
			Number of CPUs x 2 x 3
			Number of buffers/50

Free Lists

		A free list for an object maintains a list of blocks that are available for inserts.
		The number of free lists for an object cannot be set dynamically.
		Single-CPU systems do not benefit greatly from multiple free lists.
		The tuning goal is to ensure that an object has sufficient free lists to minimize contention.

Diagnosing Free List Contention

Resolving Free List Contention

		1.  Query the V$SESSION_WAIT view.
		2.  Identify the object and get free lists for the segment from DBA_SEGMENTS.
		3.  Re-create the object in question.

Summary

		In this lesson, you should have learned how to:
		Get a high cache hit ratio
		Adjust DB_BLOCK_BUFFERS as necessary
		Separate objects into multiple buffer pools
		Use multiple buffer pools
		Cache tables
		Resolve LRU latch contention
		Avoid free list contention