Home > Database, Oracle > ORACLE BUFFER CACHE TUNING

ORACLE BUFFER CACHE TUNING

Main Points:

  • Understand How Buffer Cache Works
  • Measure Buffer Cache Performance
  • Improve Buffer Performance

Why use Buffer Cache

• Data accessed from Memory is faster than data accessed from disk.

Understand Buffer Cache Working
• What is in buffer cache
o Blocks for – Tables, Indexes, Clusters,
o Blocks for – LOB Segments, LOB Indexes
o Blocks for – Rollback Segments (UNDO)
o Blocks for – Temporary Space

Different types of Block
Free – block not currently used
Pinned – block currently used by server process
Clean – block that was just used and now is ready to be re-used
Dirty – block that needs to be written to disk because it contains committed data

•  LRU list
Data blocks from data files are put on the MRU side of the LRU list (exception is FTS)
•  Dirty List
Keeps track of dirty blocks
•  User Server Processes (Shared Server Processes)
First check if the data requested by user is already in the buffer cache If not, it searches LRU list to find out free blocks. (Statistics: “Free Buffer Inspected”). Reads data from data file into free blocks in buffer cache
•  DBWR process
Moves dirty blocks from LRU list to Dirty list.Write dirty blocks to Data file

Measure Buffer Cache Performance

• Hit Related Stats (V$SYSSTAT)
o Buffer Cache Hit Ratio (should be > 95% for OLTP)
Physical Reads
Physical Reads Direct
Physical Reads Direct(LOB)
Session Logical Reads

• Non-hit related Stats (V$SYSTEM_EVENT & V$SYSSTST)
o Free buffers inspected (v$sysstat)
o Free buffer wait (v$system_event)
o Buffer Busy Wait (v$system_event)

What is stored in V$SYSSTAT
All system wide events and their cumulative values from the last instance startup
• STATISTIC# –  Statistics ID
• NAME  – The name of the statistic
• CLASS –  Category the statistic falls into
o 1 User
o 2 Redo
o 4 Enqueue
o 8 Cache
o 16 Operating System
o 32 Real Application Cluster
o 64 SQL
o 128 Debug
• VALUE Current value of statistic

There are over 200 different statistics tracked by the V$SYSSTAT view. However, only four of these are used when
calculating the performance of the Database Buffer Cache which are as follows:

Hit Related Stats (Increase hit ratio)

•  Physical Reads: This statistic indicates the number of data blocks (i.e. tables, indexes, and rollback segments) read from disk into the Buffer Cache since instance startup.

•  Physical Reads Direct This statistic indicates the number of reads that bypassed the Buffer Cache because the data blocks were read directly from disk instead. Because direct physical reads are done intentionally by Oracle when using certain features like export or Parallel Query.

•  Physical Reads Direct (LOB): This statistic indicates the number of reads that bypassed the Buffer Cache because the data blocks were associated with a Large Object (LOB) datatype

•  Session Logical Reads: This statistic indicates total number of reads requested for data. This value includes requests satisfied by access to buffers in memory and requests that caused physical I/O

Non-hit related stats (Minimize them) 

• Free Buffer Inspected: Number of Buffer Cache buffers inspected by user Server Processes before finding a
free buffer.

SELECT name, value
FROM v$sysstat
WHERE name IN (’free buffer inspected’)

• Free Buffer Waits: These waits occur whenever the Server Process had to wait for Database Writer to
write a dirty buffer to disk (DBWR)

SELECT event, total_waits
FROM v$system_event
WHERE event = ’free buffer waits’

• Buffer Busy Waits :These waits occur whenever a buffer requested by user Server Processes is already in memory, but is in use by another process. These waits  can occur for rollback segment buffers as well as data and index buffers (DBWR)

SELECT event, total_waits, average_wait
FROM v$system_event
WHERE event = ’buffer busy waits’

 

SQL>SELECT name,value,
2 FROM V$SYSSTAT
3 WHERE name IN (‘free buffer inspected’)
4 UNION
5 SELECT event,total_waits
6 FROM V$SYSTEM_EVENT
7 WHERE event in (‘free buffer waits’,’buffer busy waits’);

NAME VALUE
————— —–
buffer busy waits 170
free buffer inspected 0

High or steadily increasing values for any of these statistics indicate that User server processes are spending too much time
searching for and waiting for access to free buffers in the Database Buffer Cache

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: