buffer cache waits. #.2 copyright 2006 kyle hailey buffer cache waits waits disk i/o buffer busy...

Buffer Cache Waits

Copyright 2006 Kyle Hailey

#.2

Buffer Cache Waits

Waits

Disk I/O

Buffer Busy

Library Cache

Enqueue

SQL*Net

Free Buffer

Hot Blocks

Free lists

IO Read

Redo

Buffer Cache

Row Cache

Write Complete

Cache Buffer Chains Latch


#.3

Buffer Cache Waits

Latch - cache buffers chains Latch - cache buffer chains LRU Free Buffer Wait Buffer Busy Wait

Index Data

Free lists Hot Spots

RBS Write Complete Wait Local Write Wait


#.4

REDO Log FilesREDO Log Files Data FilesData Files

DBWRDBWRLGWRLGWR

User2User2

User1User1

User3User3

Log BufferLog Buffer

Buffer CacheBuffer CacheLog Log BufferBuffer

Buffer Buffer CacheCache

SGASGALibrary Library CacheCache

Oracle Memory Stuctures


#.5

Buffer Cache

Caches data blocks from disk that are heavily used

Buffer Cache

Disk

ShadowShadow

ProcessProcess


#.6

Block in cache?

How do you know if a block is cached?

Do you search all the blocks?

Could be 1000s of blocks to search.

ShadoShadoww

ProcesProcesss

??


#.7

Buffer CacheFind a block by

Take

Data file #

Block#

and Hash Them


#.8

Hashing I

Simple has could be a Mod function1 mod 4 = 12 mod 4 = 23 mod 4 = 34 mod 4 = 05 mod 4 = 1

Using “mod 4” as a hash funtion creates 4 “buckets” to store things


#.9

Hashing II

Empty Buckets

11

22

00

33


#.10

Hashing III

Data Block

11

22

00

33

Hash Block’s file# block #’s

Result in a bucket#Put Block in bucket


#.11

Hashing IV

11

22

00

33


#.12

Hashing V

11

22

00

33

After a while the buckets After a while the buckets become populated with become populated with blocksblocks


#.13

To Find a BlockBufferHeaders

Data BlocksHashbucket

1. Hash the block address

2. Look for header

3. Found, read block in cache

4. Not Found Read block off disk

ShadowShadowProcessProcess

11 22 4433


#.14

Cache Buffers Chains

11

22

00

33

A different latch protects each bucketA different latch protects each bucket

Note: multiple buckets are now protected by one latchNote: multiple buckets are now protected by one latch

11

22

33

44


#.15


Hash Buckets

Buffer Headers

Well Well BalancedBalanced

11

22

33

44

55

66

77

88

99

1100


#.16


Hash Buckets

Buffer Headers

Multiple Buckets protected by same latch v9 onwards

11

22

33


#.17

Cache Buffers Chains : Diag

Hash Buckets

s1s1s1s1s1s1s1s1s1s1

SessionsSessions

Contention if too accesses on a bucketContention if too accesses on a bucket

1

22

33


#.18

Cache Buffers Chains : Diag

Hash Buckets

Buffer Headers

Contention: Too Contention: Too Many Buffers in Many Buffers in BucketBucket

1

22

33


#.19

Cache Buffers Chains : Solution

Fine the SQL causing the problem Contributing Factors

Updates, inserts on blocks while reading those blocks Cause multiple copies

Hot root block of an indexSelect for updateUsing dual instead of x$dual


#.20

Need Free Block Block

If the data block isn’t in cache Get a free block in the buffer cacheRead it off diskAdd a headerRead the block into the buffer cache

Need Free Block to Read in New Data Block


#.21

Finding a Free Block

ShadowShadow

ProcessProcess

When a session reads a block When a session reads a block

Into the bufffer cache how does Into the bufffer cache how does

it find a it find a FREEFREE spot? spot?


#.22

Finding a Free BlockBufferHeaders

Data BlocksHashbucket

Buffer Headers Buffer Headers are also on a are also on a LRU linked ListLRU linked List


#.23

Cache Buffers LRU list

MRUMRU LRULRUBuffer Headers

LRU = Least Recently UsedMRU = Least Recently Used

Session

Shadow


#.24

Free Buffer Wait

Finding a Free BlockGo to the LRU end of data blocksLook for first non-dirty block If search too many post DBWR to make freeFree Buffer wait


#.25

Free Buffer Wait

#17 Data Block Cache lack free buffers Tune by

Increase data blocksTry to tune DBWR

Inefficient SQL requesting large # of blocks

In V7, sorts could flood the cache with dirty blocks


#.26

Cache Buffers LRU list

MRUMRU LRULRU

Used Block gets put on FrontUsed Block gets put on Front

Unused blocks get pushed offUnused blocks get pushed off

Buffer Headers


#.27

Cache Buffers LRU Latch

MRUMRU LRULRUBuffer Headers

One Latch protects the linked list during changes to the listOne Latch protects the linked list during changes to the list


#.28


MRUMRU LRULRU

Mid-Point Mid-Point InsertionInsertion

Full Table Scan Full Table Scan Insertion PointInsertion Point

Buffer Headers


#.29


MRUMRU LRULRU

Mid-Point Mid-Point InsertionInsertion

Oracle Tracks the touch count of blocks. As the Oracle Tracks the touch count of blocks. As the block is pushed to the LRU end, if it’s touch count block is pushed to the LRU end, if it’s touch count is 3 or more, it’s promoted to the MRU endis 3 or more, it’s promoted to the MRU end


#.30

Cache Buffers LRU Latch : Solution

Buffer Headers

Multiple SetsMultiple Sets

_db_block_lru_latches = 8_db_block_lru_latches = 810gR2 with cpu_count = 210gR2 with cpu_count = 2

Set 1Set 1

Set 2Set 2


#.31

Cache Buffers LRU Latch : Solution Other

Increase Size of Buffer Cache Reduce Checkpointing Activity Use Async I/O for DBWR Increase DBWR write batch size


#.32

Buffer Busy Waits

User 1 tries to change a buffer header User 2 already has buffer header “locked”

11

22

00

33

User1User1

User2User2


#.33

Buffer Busy

14 kinds of blocksUndo Header - more RBS segs

data (index and table are different for bbw)

segment header – free lists

freelist blocks – free lists groups

9 kinds Buffer Busy Waits 100,110,120,130 (read I/O issues)

200,210,220,230, 231 (block change issues)


#.34

Buffer Busy Wait

What’s Important: What kind of block header

RollbackData

Index Table

Segment Header If Pre 10

Read (100,110,120,130 )Write (200,210,220,230, 231 )


#.35

Buffer Busy Cases

Undo Header - Not enough rollback segments

Undo Block – hot spot in RBS

Data Block Table Block w/ DML : Table lacks Free lists Table I/O : Multiple sessions reading same block into data cache

Note: in 10g, becomes “read by other session” Index : has hot block, partition or change SQL

Segment Header – add free Lists (with Datablock) Free List – add free lists groups


#.36

Buffer Busy Solutions

IF Buffer Busy Waits highFirst look at v$waitstat

Class Time/Count

SQL> select * from v$waitstat;

CLASS COUNT TIME------------------ ---------- ----------data block 1 1segment header 0 0free list 0 0undo header 0 0undo block 0 0

SQL> select * from v$waitstat;

CLASS COUNT TIME------------------ ---------- ----------data block 1 1segment header 0 0free list 0 0undo header 0 0undo block 0 0


#.37

Buffer Busy Wait Solutions

Buffer wait Statistics DB/Inst: LINUX3/linux3 Snaps: 39-42

-> ordered by wait time desc, waits desc

Class Waits Total Wait Time (s) Avg Time (ms)

---------------------- ----------- ------------------- -------------

undo header 1,159 18 16

data block 132 2 12

file header block 45 0 2

segment header 4 0 10

undo block 1 0 0

------------------------------------------------------------


#.38

Load : Example

4 Sessions running

Insert into toto values (null, ‘a’);

Commit;


#.39

Lack of Free List : Diag

S1 S2 S3 S4


#.40

With Free Lists : Diag

S1 S2 S3 S4


#.41

10gSQL> select * from v$waitstat;

CLASS COUNT TIME------------------ ---------- ----------1. data block 89640 137322. sort block 0 03. save undo block 0 04. segment header 1017 25585. save undo header 0 06. free list 0 07. extent map 0 08. 1st level bmb 204874 188969. 2nd level bmb 155 210.3rd level bmb 0 011.bitmap block 0 012.bitmap index block 0 013.file header block 0 014.unused 0 015.system undo header 0 016.system undo block 0 017.undo header 219 12218.undo block 0 0

NAME P1 P2 P3

---------------- -------- ------- ------

buffer busy waits file# block# class

NAME P1 P2 P3

---------------- -------- ------- ------

buffer busy waits file# block# class


#.42

write complete waits

#18 Usually happens in tandem with free buffer Tune by

Increase data block cache

Happens because shadow wants to access blocks that are currently being written to disk by DBWR


#.43

Local Write Wait

#25 Truncating a table Wait for Data Cache to be cleared of all

blocks of truncated table Wait by shadow for DBWR


#.44

Summary Buffer Cache Waits

Latch - cache buffers chains: find SQL Latch - cache buffer chains LRU Free Buffer Wait : increase cache size, tune SQL Buffer Busy Wait

Index : aleveate hot spotsData DML : add free listsData Read: change app, tune I/ORBS : more RBS segs, use UNDO

Write Complete Waits –: increase cache size Local Write Wait : truncates / reduce cache size

buffer cache waits. #.2 copyright 2006 kyle hailey buffer cache waits waits disk i/o buffer busy...

Documents

kyle hailey block

kyle hailey buffer cache

redo buffer cache row

free block block

blocks slide

kyle hailey findi

bucket slide

new data block slide