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.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2012
All rights reserved.
Oracle ©
is the registered trademark of Oracle Corporation.
|
|
