Oracle Tips by Burleson Consulting
Performance Issues With ASSM
The Oracle community has mixed feelings about using ASSM
See here for
internals and how
bitmap freelists compare to manual freelist management.
Note: As of 11g release
2, many of the performance bugs in ASSM have been repaired.
However, it is still important to test ASSM if you use it for tables
that experience high-volume DML updates. By high volume, we mean
over 30 concurrent DML statements st any given time.
Among the top points about ASSM, there are both pros
Pros of ASSM:
ASSM is better than a static pctused. The bitmaps make
ASSM tablespaces better at handling rows with wide variations in row
: ASSM will remove buffer busy
waits better than using multiple freelists. When a table has
multiple freelists, all purges must be parallelized to reload
the freelists evenly, and ASSM has no such limitation.
The bitmap freelists remove the need to
define multiple freelists groups for RAC and provide overall
improved freelist management over traditional freelists.
Cons of ASSM:
Several studies have shown that large-table full-table
scans (FTS) will run longer with ASSM than standard bitmaps. ASSM
FTS tablespaces are consistently slower than freelist FTS
operations. This implies that ASSM may not be appropriate for
decision support systems and warehouse applications unless
partitioning is used with Oracle Parallel Query.
§ : Numerous experts have
conducted studies that show that tables with high volume bulk loads
perform faster with traditional multiple freelists.
For row ordered tables, ASSM can adversely
affect the clustering_factor
for indexes. Bitmap freelists are less likely to place
adjacent tows on physically adjacent data blocks, and this can lower
the clustering_factor and
the cost-based optimizer's propensity to favor an index range scan.
Also see MOSC Note: 6781367.8 and
Bug 6781367 ALTER TABLE
ADD COLUMN or mass UPDATE can be slow in ASSM.
The combination of bitmap freelists, ASSM, and Locally Managed
Tablespaces has greatly simplified and improved the internal
management of data blocks within Oracle tablespaces. The use of
bitmap freelists removes the need to define multiple freelists
for tables and indexes that experience high volume concurrent DML
and provides a simple solution to the problem of segment header
However, the savvy DBA recognizes the tradeoff between
one-size-fits-all convenience and the power of being able to set
individual object parameters for tables and indexes. The choice of
LMT and ASSM for tablespace management depends heavily on the
application, and real-world Oracle tablespaces will implement LMT
and ASSM tablespaces only after careful consideration.
The following section examines proactive tablespace management
techniques on Oracle10g.
SEE CODE DEPOT FOR FULL SCRIPTS
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: