Buffer Cache Waits 2 Buffer Cache Waits Hot

Buffer Cache Waits

#. 2 Buffer Cache Waits Hot Blocks Buffer Busy Buffer Cache Disk I/O Enqueue Waits Library Cache Redo Free Buffer Free lists IO Read Write Complete Cache Buffer Chains Latch Row Cache SQL*Net Copyright 2006 Kyle Hailey

#. 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 Copyright 2006 Kyle Hailey

Oracle Memory Stuctures SGA Log Library Buffer Cache Log Buffer DBWR LGWR User 1

Buffer Caches data blocks from disk that are heavily used Buffer Cache Shadow Process

Block in cache? How do you know if a block is cached? Do you

#. 7 Buffer Cache Find a block by Take Data file # Block# and

#. 8 Hashing I Ø Simple has could be a Mod function Ø 1 mod 4 = 1 Ø 2 mod 4 = 2 Ø 3 mod 4 = 3 Ø 4 mod 4 = 0 Ø 5 mod 4 = 1 Ø Using “mod 4” as a hash funtion creates 4 “buckets” to store things Copyright 2006 Kyle Hailey

#. 9 Hashing II Empty Buckets 0 1 2 3 Copyright 2006 Kyle Hailey

#. 10 Hashing III Data Block 0 Hash Block’s file# block #’s Result in

#. 11 Hashing IV 0 1 2 3 Copyright 2006 Kyle Hailey

#. 12 Hashing V After a while the buckets become populated with blocks 0

#. 13 To Find a Block Hash bucket Buffer Headers Data Blocks Shadow Process 1 1. 2. 3. 4. 2 Hash the block address Look for header Found, read block in cache Not Found Read block off disk Copyright 2006 Kyle Hailey 3 4

#. 14 Cache Buffers Chains A different latch protects each bucket 0 1 1 2 2 3 3 4 Note: multiple buckets. Copyright are 2006 now protected by one latch Kyle Hailey

#. 15 Cache Buffers Chains Well Balanced 1 2 Hash Buckets 3 4 5

#. 16 Cache Buffers Chains 1 Hash Buckets Multiple Buckets protected by same latch

#. 17 Cache Buffers Chains : Diag s 1 s 1 s 1 1

#. 18 Cache Buffers Chains : Diag Buffer Headers 1 Hash Buckets 2 Contention:

#. 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 index Ø Select for update Ø Using dual instead of x$dual Copyright 2006 Kyle Hailey

#. 20 Need Free Block Ø If the data block isn’t in cache Ø Get a free block in the buffer cache Ø Read it off disk Ø Add a header Ø Read the block into the buffer cache Need Free Block to Read in New Data Block Copyright 2006 Kyle Hailey

#. 21 Finding a Free Block When a session reads a block Into the

Finding a Free Block Hash bucket Buffer Headers are also on a LRU linked

#. 23 Cache Buffers LRU list MRU LRU Buffer Headers Session Shadow LRU =

#. 24 Free Buffer Wait Ø Finding a Free Block Ø Go to the LRU end of data blocks Ø Look for first non-dirty block Ø If search too many post DBWR to make free Ø Free Buffer wait Copyright 2006 Kyle Hailey

#. 25 Free Buffer Wait Ø #17 Ø Data Block Cache lack free buffers Ø Tune by Ø Increase data blocks Ø Try to tune DBWR Ø Inefficient SQL Ø requesting large # of blocks In V 7, sorts could flood the cache with dirty blocks Copyright 2006 Kyle Hailey

#. 26 Cache Buffers LRU list MRU Buffer Headers Used Block gets put on

#. 27 Cache Buffers LRU Latch MRU Buffer Headers LRU One Latch protects the

#. 28 Cache Buffers LRU Latch MRU Buffer Headers Mid-Point Insertion Copyright 2006 Kyle

#. 29 Cache Buffers LRU Latch LRU Mid-Point Insertion Oracle Tracks the touch count of blocks. As the block is pushed to the LRU end, if it’s touch count is 3 or more, it’s promoted to the MRU end Copyright 2006 Kyle Hailey

#. 30 Cache Buffers LRU Latch : Solution Set 1 Buffer Headers Set 2

#. 31 Cache Buffers LRU Latch : Solution Other Ø Increase Size of Buffer

#. 32 Buffer Busy Waits Ø User 1 tries to change a buffer header

#. 33 Buffer Busy Ø 14 kinds of blocks Undo 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, 231 (block change issues) Copyright 2006 Kyle Hailey

#. 34 Buffer Busy Wait What’s Important: Ø What kind of block header Ø Rollback Ø Data Ø Ø Index Table Ø Segment Header Ø If Pre 10 Ø Read (100, 110, 120, 130 ) Ø Write (200, 210, 220, 231 ) Copyright 2006 Kyle Hailey

#. 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 10 g, 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 Copyright 2006 Kyle Hailey

#. 36 Buffer Busy Solutions Ø IF Buffer Busy Waits high Ø First Ø Ø look at v$waitstat Class Time/Count SQL> select * from v$waitstat; CLASS COUNT TIME --------- -----data block 1 1 segment header 0 0 free list 0 0 undo header 0 0 undo block 0 0 Copyright 2006 Kyle Hailey

#. 37 Buffer Busy Wait Solutions Buffer wait Statistics DB/Inst: LINUX 3/linux 3 -> ordered by wait time desc, waits desc Snaps: 39 -42 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 ------------------------------ Copyright 2006 Kyle Hailey

#. 38 Load : Example Ø 4 Sessions running Insert into toto values (null,

#. 39 Lack of Free List : Diag S 1 S 2 S 3

#. 40 With Free Lists : Diag S 1 S 2 S 3 S

#. 41 10 g SQL> select * from v$waitstat; CLASS COUNT TIME --------------1. data block 89640 13732 2. sort block 0 0 3. save undo block 0 0 4. segment header 1017 2558 5. save undo header 0 0 6. free list 0 0 7. extent map 0 0 NAME P 1 8. 1 st level bmb 204874 18896 9. 2 nd level bmb 155 2 -----------10. 3 rd level bmb 0 0 11. bitmap blockbuffer busy waits 0 0 file# 12. bitmap index block 0 0 13. file header block 0 0 14. unused 0 0 15. system undo header 0 0 16. system undo block 0 0 17. undo header 219 122 18. undo block 0 0 Copyright 2006 Kyle Hailey P 2 P 3 -------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 Copyright 2006 Kyle Hailey

#. 43 Local Write Wait Ø #25 Ø Truncating a table Ø Wait for

#. 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 spots Ø Data DML : add free lists Ø Data Read: change app, tune I/O Ø RBS : more RBS segs, use UNDO Ø Write Complete Waits –: increase cache size Ø Local Write Wait : truncates / reduce cache size Copyright 2006 Kyle Hailey