Database Reference
In-Depth Information
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
DFS lock handle 359 0.05 0.64
enq: HW - contention 6 0.03 0.09
buffer busy waits 130 0.06 0.50
Our recommendations for a sequence in a RAC database are as follows:
1.
If sequences are heavily accessed, then use a bigger cache value (1,000+). A bigger cache
improves performance because access to a cached value is an order of magnitude faster
than access to an uncached sequence value. The only drawback is that an instance crash
can lose all cached sequence values.
2.
If the sequences are accessed infrequently, use the default cache value of 20.
3.
If you must retrieve values sequentially for
infrequently
accessed sequences, alter the
sequence attributes to
ORDER
,
CACHE
value to avoid ill effects of uncached sequences in
a RAC database. Also, alter the cache value of the sequence to 20. This option has less
impact than sequences with nocache attributes because sequence values are stored in a
GES resource.
4.
If you cannot afford to lose any sequence value, then you should consider
only
ORDER
,
NOCACHE
attributes. You should know that if you alter index attributes to
ORDER
,
NOCACHE
for
frequently accessed sequences, it could lead to a hung cluster.
Freelists and ASSM
A freelist maintains a list of blocks available with sufficient free space in a non-ASSM (Automatic Segment Space
Management) tablespace. Sessions search freelists while searching for a free block to populate a row piece. Freelists
are stored in the segment header block, and if there is a need for excessive concurrent access to freelists, then the
segment header block can be a hot block. The segment header block must be transferred between the instances
repetitively, causing block contention with possible waits for gc buffer busy events.
4
You can alleviate freelist block contention (segment header contention) by creating objects with multiple freelist
groups. Each freelist group is stored in a block by itself. Further, instances have an affinity to a freelist group and
sessions in an instance search in the freelist group with affinity, before searching in another freelist group. So, segment
header contention can be alleviated by creating as many freelist groups as instances; that is, if you have four instances
in a database, then create objects with a freelist group of 4.
Note that freelists and freelist groups are applicable only to Manual Segment Space Management (MSSM)
tablespaces. ASSM tablespaces do not suffer from the contention issue because bitmaps at different levels are used to
maintain lists of free blocks. Each instance has affinity to an L1 bitmap, essentially implementing a concept similar to
multiple freelist groups.
My recommendation is to avoid using MSSM tablespaces and instead use ASSM tablespaces. You should
consider partitioning the table if ASSM is not able to support the concurrency.
4
Note that this is not a complete description of freelist management. There are actually three freelists: master freelist, transaction
freelist, and process freelist. The freelist search algorithm is complicated. Please refer to a MOS paper written by Stephen Heisley
titled “Freelist Management.”
