The
Automatic Segment Space Management (a.k.a. ASSM or bitmap freelists) is
a wonderful features for most shops. However, using ASSM in some high-DML environments can result in poorer
performance, and you will need to manually set the values for INITRANS and FREELISTS
(as well as FREELIST GROUPS and PCTFREE and PCTUSED).
Let's examine some brief guidelines for these settings.
Understanding INITRANS:
The INITTRANS setting controls Initial
Transaction Slots (ITLs). A transaction slot is required for any
session that needs to modify a block in an object. For tables
INITRANS defaults to 1 for indexes, 2.
The MAXTRANS setting controls the
maximum number of ITLs that a block can allocate (usually defaults to 255). If a block is sparsely populated then
Oracle will dynamically increase the number of ITLs up to MAXTRANS.
ITL's and Block
Waits
However, if the block has little or no free space then transactions
will serialize waiting on a free ITL. This is one cause for data
base block waits. By setting INITRANS to the number of expected
simultaneous DML (data manipulation language – insert, update and
delete) transaction for a single block, you can avoid serialization
for ITL slots.
The maximum value suggested for INITRANS is 100 and
settings over this size rarely improve performance. Therefore a
setting of INITRANS to the average number of simultaneous DML users
and setting MAXTRANS to 100 will most likely result in the best
utilization of resources and performance. Remember, each ITL requires
approximately 23 bytes in the block header.
FREELISTS
and their cousin FREELIST GROUPS:
A freelist if a one-way linked
list (or a bitmap) that identified blocks that can accept data.
FREELIST GROUPS was designed for Oracle Parallel Query (OPQ) and used
in Oracle Real Application Clusters (RAC) where many instances need to
attach to the same data block.
For example, a setting of FREELISTS
4 and FREELIST GROUPS 2 result in 8 total FREELISTS.
Generally speaking FREELIST groups are used in Oracle real
application clusters where the setting should be equal to the number
of nodes (instances) participating in the cluster. FREELISTS should
be set to the number of simultaneous DML users for the table (not
the block!), tables default to 1 FREELIST and 0 FREELIST GROUPS.
Again, settings of greater than 100 rarely result in better
performance for FREELISTS. Also, tables will, by default, extend
based on the minimum allowed extension times the number of FREELISTS
so be aware of this when setting FREELISTS.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
