1 / 29

Oracle Touch Count Algorithm

Oracle Touch Count Algorithm. Edward Hayrabedian Semantec Bulgaria OOD. Presentation objectives:. Why a new algorithm is needed General TC algorithm Oracle’s TC algorithm Optimizing Oracle’s buffer cache. New challenges … New algorithm.

dragon
Download Presentation

Oracle Touch Count Algorithm

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Touch Count Algorithm Edward Hayrabedian Semantec Bulgaria OOD

  2. Presentation objectives: • Why a new algorithm is needed • General TC algorithm • Oracle’s TC algorithm • Optimizing Oracle’s buffer cache

  3. New challenges … New algorithm • Bigger caches. As more memory is available, buffer caches continue to grow uncomfortablyhuge. • Better performance requirements dictate improved “big cache” memory management. • More control of the buffer cache requires more granular tuning capabilities.

  4. It’s all about staying in the cache. Oracle must find an efficient way to make it easy for popular blocks to stay in the cache and make it difficult for other blocks to stay in the cache.

  5. Buffer cache mgt history • Standard least recently used (LRU) algorithm • Modified LRU algorithm • Sub caches… • Touch-count algorithm

  6. Oracle’s standard LRU. • Basically when a block is touched it is moved to the head of the LRU list. • Good: Pretty good chance popular blocks to stay in the cache. • Bad: Full table scan blocks would flood the cache destroying a well developed cache. MRU SELECT …

  7. Oracle’s modified LRU. • Basically FTS blocks are placed at the tail of the LRU. • Good: FTS would not destroy the cache. • Bad: Huge range scan would flood the cache with index leaf blocks destroying the cache. MRU NOT FTS … FTS …

  8. Cache segmentation. • Basically a DBA can segregate objects into sub-caches to improve performance. • Good: Further preserved the main buffer cache while providing better caching for non-standard objects. • Bad: A hassle and some “implementation risk” pressure. keep recycle default

  9. Oracle’s touch-count LRU. • Basically, each buffer is assigned a “touch-count” which assigns a kind of value or popularity. tch 2 tch 3 … tch n tch 1

  10. Key TC components. • Midpoint insertion. • Touch-count incrementation. • Buffer movement. • Parameters for everything…

  11. Midpoint insertion details. • The buffer cache is divided into a hot region and a cold region. • A midpoint pointer is maintained which moves to ensure proper regional block quantities. • When a block is brought into the cache, it is placed in the “middle” of the LRU chain. • A buffer naturallymoves from the hot region into the cold region. midpoint pointer

  12. TC incrementation. • In concept, whenever a block is touched its TC is incremented. • In reality, it is NOT. • No latching is used to reduce possible contention… so some incrementation may not occur. • To reduce rapid fire buffer access cache stress, a touch count can only be incremented once every 3 seconds (by default). • Buffer movement and touch count incrementation are independent events.

  13. Buffer movement. • Keep in mind: • TC incrementation is buffer movement independent. • Blocks are inserted at the midpoint • When: • A server process is looking for a free buffer – OR – • The DBWR process is looking for dirty blocks • AND if the buffer’s touch count is greaterthan 2, • ONLY, then the buffer is moved to the MRU head • When buffer cache movement does occur, the buffer touch count is reset to “ZERO”!

  14. Hot region to cold movement. • Regardless of its touch count, • If a buffer crosses from the hot region into the cold region, • Its touch count is reset to “ONE”!

  15. Example (midpoint insertion) Midpoint insertion B C New A B C D E F 5 7 4 11 0 1 1 3

  16. Example (midpoint insertion) B C A B C New D E F 0 5 7 4 11 0 1 1 3

  17. Example (buffer movement) Buffer movement from Cold to Hot B C A B C New D E F 5 7 4 11 0 1 1 3 0 from Hot toCold

  18. Example(buffer movement) B C B C New D E F A 1 0 7 4 0 1 1 3 0

  19. Example (TCH incrementation) Incremented due to DMLs B C B C New D E F A 7 7 4 0 1 1 3 1 0

  20. Example (buffer movement) Buffer movement B C B C New D E F A 7 4 0 0 1 1 3 7 1

  21. Example (buffer movement) B C B C D E F A New 1 0 1 4 0 0 1 1 3

  22. Buffer cache optimization. • Solution should be based upon the problem. • Problem determination should be Oracle Response Time Analysis based. • Don’t mess with the TC related instance parameters unless you really understand what’s going on.

  23. Severe contention based change. • For example, log buffer contention is not a good case for modifying TC related instance parameters. • Significant buffer cache latching related contention is when TC related instance tuning may help • Goal: Minimize latch contention (reduced of buffer movement) while keeping only the truly popular blocks cached.

  24. Your instance parameter options are many… (1/3) • _db_percent_hot_defaultis the percentage of blocks in the hot region. If you want more blocks to be considered “hot”, increase this parameter. Decreasing this parameter give a buffer more TC incrementation timebefore it is scanned. • _db_aging_touch_countis the time window in which a buffer’s TC can only be incremented by one. Increase this parameter to reduce sudden activity disruptions and to reduce buffer movement. You risk devaluing popular blocks.

  25. Your instance parameter options are many… (2/3) • _db_aging_hot_criteriais the threshold when a buffer can be moved to the head of the LRU list. If you want to make it more difficult for a buffer to be moved (to MRU end of the list), then increase this value. Only really popular blocks will remain in the cache. • _db_aging_stay_countis involved when a buffer’s TC is reset after it is moved to the head of the LRU list. If you want the hot buffer to remain really hot, increase this value.

  26. Your instance parameter options are many… (3/3) • _db_aging_cool_countis the reassigned TC value when a buffer moves into the cool region. Increasing this value will slow the coolness (keep it “popular”) of blocks and make it easier for the buffer to be moved to the head of the LRU list.

  27. Useful statistics • X$bh – blocks inside the buffer cache select obj object, file#, dbablk, lru_flag, status, tch touches, tim from x$bh where tch > &TCH_thresold

  28. Resources • Craig A. Shallahamer “All about Oracle’s Touch Count Data Block Buffer Cache Algorithm” (original 2001, version 4a, Jan 5, 2004) • Yoshihiro Uratsujihttp://www.performance-insight.com/html/ora3/back/Oracle9i_1.html • Oracle documentation

  29. Thank you!

More Related