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 User 2 User 3 REDO Log Files Copyright 2006 Kyle Hailey Data Files #. 4
Buffer Caches data blocks from disk that are heavily used Buffer Cache Shadow Process Disk Copyright 2006 Kyle Hailey #. 5
Block in cache? How do you know if a block is cached? Do you search all the blocks? Could be 1000 s of blocks to search. ? Shado w Proces s Copyright 2006 Kyle Hailey #. 6
#. 7 Buffer Cache Find a block by Take Data file # Block# and Hash Them Copyright 2006 Kyle Hailey
#. 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 a bucket# Put Block in bucket 1 2 3 Copyright 2006 Kyle Hailey
#. 11 Hashing IV 0 1 2 3 Copyright 2006 Kyle Hailey
#. 12 Hashing V After a while the buckets become populated with blocks 0 1 2 3 Copyright 2006 Kyle Hailey
#. 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 Buffer Headers 6 7 8 9 1 0 Copyright 2006 Kyle Hailey
#. 16 Cache Buffers Chains 1 Hash Buckets Multiple Buckets protected by same latch v 9 onwards Buffer Headers 2 3 Copyright 2006 Kyle Hailey
#. 17 Cache Buffers Chains : Diag s 1 s 1 s 1 1 Hash Buckets Sessions 2 3 Contention if too accesses on a bucket Copyright 2006 Kyle Hailey
#. 18 Cache Buffers Chains : Diag Buffer Headers 1 Hash Buckets 2 Contention: Too Many Buffers in Bucket 3 Copyright 2006 Kyle Hailey
#. 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 bufffer cache how does it find a FREE spot? Shadow Process Copyright 2006 Kyle Hailey
Finding a Free Block Hash bucket Buffer Headers are also on a LRU linked List Copyright 2006 Kyle Hailey Data Blocks #. 22
#. 23 Cache Buffers LRU list MRU LRU Buffer Headers Session Shadow LRU = Least Recently Used MRU = Least Recently Used Copyright 2006 Kyle Hailey
#. 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 Front Unused blocks get pushed off Copyright 2006 Kyle Hailey LRU
#. 27 Cache Buffers LRU Latch MRU Buffer Headers LRU One Latch protects the linked list during changes to the list Copyright 2006 Kyle Hailey
#. 28 Cache Buffers LRU Latch MRU Buffer Headers Mid-Point Insertion Copyright 2006 Kyle Hailey LRU Full Table Scan Insertion Point
#. 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 Multiple Sets _db_block_lru_latches = 8 10 g. R 2 with cpu_count = 2 Copyright 2006 Kyle Hailey
#. 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 Copyright 2006 Kyle Hailey
#. 32 Buffer Busy Waits Ø User 1 tries to change a buffer header Ø User 2 already has buffer header “locked” 0 1 User 1 2 User 2 3 Copyright 2006 Kyle Hailey
#. 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, ‘a’); Commit; Copyright 2006 Kyle Hailey
#. 39 Lack of Free List : Diag S 1 S 2 S 3 S 4 Copyright 2006 Kyle Hailey
#. 40 With Free Lists : Diag S 1 S 2 S 3 S 4 Copyright 2006 Kyle Hailey
#. 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 Data Cache to be cleared of all blocks of truncated table Ø Wait by shadow for DBWR Copyright 2006 Kyle Hailey
#. 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
- Slides: 44