 |
|
Oracle Tips by Burleson |
Chapter 5 Oracle Tablespace Design
Cons of ASSM:
-
Slow for full-table scans — 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.
-
Slower for high-volume concurrent
INSERTS — Numerous experts have conducted studies that show
that tables with high volume bulk loads perform faster with
traditional multiple FREELISTS.
-
ASSM will influence index clustering
— For row-ordered tables, ASSM can adversely affect the
clustering_factor for indexes. Bitmap FREELISTS are less likely
to place adjacent rows 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.
It remains to be seen how many experienced DBAs
will start using Automatic Space Management and how many will
continue to use the older method. While Automatic Space Management
promises faster throughput for multiple DML statements, Oracle
professionals must always be on the watch for chained rows caused by
a generic setting for PCTFREE. The seasoned DBA may want to bypass
these new features in order to control the behavior of the table
rows inside the data blocks.
Now let’s examine how to design for Oracle
replication.
Replication Design
Managing an Oracle data warehouse becomes very
challenging when we move into the distributed database environment.
The challenge arises because so many components within the database
software contribute to the overall performance. The number of
concurrent users, the availability of space within the buffer and
lock pools, and the balancing of access across processors all can
affect database performance.
When a data warehouse accesses several remote
databases in a single warehouse query, another dimension of
complexity is added to the data warehouse. Not only must the
database administrator (DBA) look at each individual database, but
the DBA also must consider transactions that span several servers.
While accessing several servers in a
distributed warehouse query may seem trivial, performance problems
can be introduced by PC hardware, LAN and network bottlenecks,
router overloads, and many other sources. Let’s take a look at
distributed data warehouses and examine how they differ from
traditional data warehouse environments.
Conclusion
This chapter has been concerned with the
physical design of the file and tablespace structures. The main
points of this chapter include:
-
Locally managed tablespaces are now the
default in Oracle10g and should be used whenever feasible.
-
Automatic segment space management relieves
the one-way chains and relieves buffer busy waits on high-update
segments.
-
You still have the option of manually
setting PCTFREE, PCTUSED and FREELISTS for individual segments.
Now we are ready to look at the physical design
options for Oracle tables and indexes. The next chapter will
explore all of the table options to illustrate the advantages of
each and show how they interface with the logical database design.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
