This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
The goal in tablespace design is
to group database objects according to their data access
distribution patterns. The dependency analysis and transaction
profiles of the database must be considered, and then tablespaces
are divided into containers for the following objects:
* Frequently and randomly
modified tables and indexes belonging to particular functional
* Frequently and randomly
modified tables and indexes with a lower probability of having
affinity to any functional area.
* Tables and indexes that are
mostly READ or READ-ONLY and infrequently modified.
The following criteria must also
be considered for separating database objects into tablespaces:
* Tables should be separated
* Assign READ-ONLY tables to
* Group smaller reference tables
in the same tablespace.
Using this strategy to group
objects into tablespaces will improve the performance of Oracle's
dynamic resource mastering. Oracle?s dynamic resource remastering by
datafiles algorithm re-distributes GCS resources to the instance
where they are needed most. This remastering strategy is designed to
improve resource operation?s efficiency. Oracle remasters cache
blocks to the instance with which the cache blocks are most closely
associated based on historical access patterns. As a result,
resource operations, after remastering, require minimal
communication with remote instances through the GES and GCS.
Extent Management and Locally
Allocating and de-allocating
extents are expensive operations that should be minimized. Most of
these operations in Real Application Clusters require inter-instance
coordination. A high rate of extent management operations can
adversely affect performance in Real Application Clusters
environments more than in single instance environments. This is
especially true for dictionary-managed tablespaces.
Identifying Extent Management
If the row cache lock event is a
significant contributor to the non-idle wait time in v$system_event,
there is contention in the data dictionary cache. Extent allocation
and de-allocation operations could cause this. v$rowcache provides
data dictionary cache information for dc_used_extents and
dc_free_extents. This is particularly true when the values for
dlm_conflicts for those parameters increase significantly over time.
This means that excessive extent management activity is occurring.
Minimizing Extent Management
Proper storage parameter
configuration for tables, indexes, temporary segments, and rollback
segments decreases extent allocation and de-allocation frequency.
This is accomplished using the initial, next, pctincrease,
minextents, and optimal parameters.
Using Locally Managed
Extent allocation and
de-allocation overhead can be greatly reduced if locally managed
tablespaces are used. For optimal performance and the most efficient
use of space, segments in locally managed tablespaces should ideally
have similar space allocation characteristics. This enables the
tablespace to be created with the proper uniform extent size that
corresponds to the ideal extent size increment calculated for the
For example, tables with
relatively high insert rates can be placed in a tablespace with a
10MB uniform extent size. On the other hand, small tables with
limited DML activity can be placed in a tablespace with a 100K
uniform extent size. For an existing system, where tablespaces are
not organized by segment size, this type of configuration can
require significant reorganization efforts with limited benefits.
For that reason, compromise by making most of the tablespaces
locally managed, with AUTOALLOCATE instead of UNIFORM extent