Notes

Outline

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