Transcript Automatic Database Diagnostic Monitor

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
 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.
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.
Buffer cache mgt history
 Standard least recently used (LRU)
algorithm
 Modified LRU algorithm
 Sub caches…
 Touch-count algorithm
Oracle’s standard LRU.
 Basically when a block is touched it is
moved to the head of the LRU list.
SELECT …
MRU
 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.
Oracle’s modified LRU.
 Basically FTS blocks are placed at the tail
of the LRU.
NOT FTS …
MRU
FTS …
 Good: FTS would not destroy the cache.
 Bad: Huge range scan would flood the
cache with index leaf blocks destroying the
cache.
Cache segmentation.
 Basically a DBA can segregate objects
into sub-caches to improve performance.
keep
recycle
default
 Good: Further preserved the main buffer cache
while providing better caching for non-standard
objects.
 Bad: A hassle and some “implementation risk”
pressure.
Oracle’s touch-count LRU.
 Basically, each buffer is assigned a
“touch-count” which assigns a kind of value
or popularity.
tch 1 tch 2 tch 3
…
tch n
Key TC components.
 Midpoint insertion.
 Touch-count incrementation.
 Buffer movement.
 Parameters for everything…
Midpoint insertion details.
 The buffer cache is divided into a hot region and
a cold region.
midpoint pointer
 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.
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.
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 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”!
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”!
Example
(midpoint insertion)
Midpoint insertion
New
B
A
11
C
01
D
13
E
5
F
7
4
Example
(midpoint insertion)
B
A
11
C
01
New
13
0
D
E
5
F
7
4
Example
(buffer movement)
Buffer movement
from Cold to Hot
B
A
11
C
01
New
13
0
D
E
5
F
7
from Hot to Cold
4
Example
(buffer movement)
B
C
01
New
13
0
D
E
1
F
7
A
4
0
Example
(TCH incrementation)
Incremented due to DMLs
B
C
01
New
13
7
D
E
1
F
7
A
4
0
Example
(buffer movement)
Buffer movement
B
C
01
New
13
7
D
E
1
F
7
A
4
0
Example
(buffer movement)
B
C
01
D
13
E
1
F
1
A
4
New
0
0
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.
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.
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 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.
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
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.
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.
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
Resources
 Craig A. Shallahamer
“All about Oracle’s Touch Count Data Block Buffer Cache
Algorithm” (original 2001, version 4a, Jan 5, 2004)
 Yoshihiro Uratsuji
http://www.performanceinsight.com/html/ora3/back/Oracle9i_1.html
 Oracle documentation
Thank you!