Database Reference
In-Depth Information
What Causes Hot Blocks?
When data is acquired from the buffer cache of the instance, Oracle requires it to acquire a latch, which is the
“Cache Buffers Chains” latch. This means Oracle will acquire a latch every time it searches for a data block cached in
the buffer cache. The database buffer cache is a linked list chain of data blocks and needs to be scanned when a data
block needs to be accessed. Every time Oracle tries to acquire a block from the buffer, it needs to acquire a latch from
the O/S; and when the latch is not available, the process goes to sleep (waits). The more times it sleeps, either the CPU
as a resource is not available or the child buffer is busy and not able to access the blocks.
Yet another but common reason is the buffer busy situation. When data in the buffer needs to be accessed for
several reasons (replacing with new data block, for data block modifications, for flushing the data block, etc.), and the
buffer is currently being accessed or locked by another session, the Oracle session wanting to access the block from
buffer has to wait until such time that the buffer becomes available. High buffer busy waits can lead to a slowdown of
the overall response time for users.
Following some of the best practices with index and data file creation would help reduce some of
these contentions:
1.
Using ASM will help distribute extents across disks distributing I/O.
2.
Using locally managed tablespaces reduces updates to the data dictionary, thus reducing
I/O contentions.
3.
Using ASSM (Automatic Segment Storage Management) when creating tablespaces will
help space management contention at the segment level. Insert-intensive applications can
gain when multiple sessions because with ASSM, there is no need to manage
FREELISTS
and
FREELIST GROUPS
.
4.
Using database-partitioning features to distribute data across different partitions can
reduce contention.
■
Note
please refer to Chapter 9 for more discussions on these best practices.
Workshop—Identifying Hot Blocks
Hot blocks are normally experienced when sessions frequently access data from the buffer cache or request data held
in the buffer of another instance. To determine the hot objects, we could use one of two methods.
Method 1
Step 1
Because cache buffers chain latch is a child latch querying
GV$LATCH_CHILDREN
view, it will give a list of latch
addresses that have the highest sleeps.
Script: MVRACPDnTap_ latch_childrencbc.sql
SELECT inst_id INT,
child# "Child",
addr "Address",
gets "Gets",
misses "Misses",
sleeps "Sleeps"
Search WWH ::
Custom Search