Oracle Touch Count Algorithm

1
Oracle Touch Count Algorithm

• Edward Hayrabedian
• Semantec Bulgaria OOD

2
Presentation objectives

• Why a new algorithm is needed
• General TC algorithm
• Oracles TC algorithm
• Optimizing Oracles buffer cache

3
New challenges New algorithm

• Bigger caches. As more memory is available,
  buffer caches continue to grow uncomfortably
  huge.
• Better performance requirements dictate improved
  big cache memory management.
• More control of the buffer cache requires more
  granular tuning capabilities.

4
Its 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
Oracles 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
Oracles 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
Oracles touch-count LRU.

• Basically, each buffer is assigned a
  touch-count which assigns a kind of value or
  popularity.

tch 2
tch n
tch 3

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 naturally moves 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 buffers touch count is greater than
  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
A
D
F
E
New
B
C
5
7
4
11
1
3
0
1
16
Example (midpoint insertion)

B
C
A
D
F
E
New
B
C
0
5
7
4
11
1
3
0
1
17
Example (buffer movement)

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

B
C
A
D
F
E
New
B
C
1
0
7
4
1
3
0
1
0
19
Example (TCH incrementation)

Incremented due to DMLs
B
C
A
D
F
E
New
B
C
7
7
4
1
3
0
1
1
0
20
Example (buffer movement)

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

B
C
A
D
F
E
New
B
C
1
0
4
1
0
1
3
0
1
22
Buffer cache optimization.

• Solution should be based upon the problem.
• Problem determination should be Oracle Response
  Time Analysis based.
• Dont mess with the TC related instance
  parameters unless you really understand whats
  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_default is 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 time before it is scanned.
• _db_aging_touch_count is the time window in which
  a buffers 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_criteria is 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_count is involved when a buffers
  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_count is 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

• Xbh blocks inside the buffer cache
• select obj object,
• file,
• dbablk,
• lru_flag,
• status,
• tch touches,
• tim
• from xbh
• where tch gt TCH_thresold

28
Resources

• Craig A. Shallahamer
• All about Oracles 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!