Segment Segregation
As a general rule of thumb, you want to divide segments into different tablespaces based on their
type, size, and frequency of access. Furthermore, each of these tablespaces would benefit from
being on its own disk group or disk device; in practice, however, most shops will not have the
luxury of storing each tablespace on its own device. The following bulleted points identify some of
the conditions you might use to determine how segments should be segregated among tablespaces.
They are not prioritized here because the priority depends on your particular environment. Using
Automatic Storage Management (ASM) eliminates many of the contention issues listed with no
additional effort by the DBA. ASM is discussed in detail in Chapter 4.
Big segments and small segments should be in separate tablespaces.
Table segments and their corresponding index segments should be in separate
tablespaces.
A separate tablespace should be used for each application.
Segments with low usage and segments with high usage should be in different
tablespaces.
Static segments should be separated from high DML segments.
Read-only tables should be in their own tablespace.
Staging tables for a data warehouse should be in their own tablespace.
?–
?–
?–
?–
?–
?–
?–
74 Oracle Database 11g DBA Handbook
Tablespaces should be created with the appropriate block size, depending on whether
segments are accessed row by row or in full table scans.
Pages:
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174