 |
|
Oracle Concepts -
Layout, Fragmentation and Hotspotting
Oracle Tips by Burleson Consulting |
Layout & Fragmentation
As was discussed in the section on OFA, layout
of a set of tablespaces can have dramatic affects on performance and
maintainability of a database. Internal layout of tablespaces can also
cause performance problems if not handled properly. Internal layout of
a tablespace deals with table, index, and rollback segment sizing and
placement. Once the main OFA rules are enforced, you must further
review design to prevent overloading of tablespaces.
Overloading of tablespaces is when many
hi-activity segments are placed in a single tablespace. Many
third-party vendors that build on top of Oracle are often guilty of
overloading. In an ideal setup each table, index and rollback segment
would have its own disk or array with an independent IO channel and
the CPUs would be able to talk to each area simultaneously. However we
all know this is not possible at least with current technology.
Overloading results in what is known as hotspotting, this is where a
single disk or array becomes saturated with IO requests because of the
volume of hot tables or indexes that reside there.
Figure 9 shows the results from Hotspotting
through a Precise*SQL screenshot.
Figure 9: Example of Hotspotting
The thing to notice in Figure 9 is that every
statement is showing IO wait as its major resource problem. In this
particular the entire database was striped across eight disk drives
and all files where placed into a single RAID 1 array. In addition
many tablespace contained dozens if not hundreds of tables or indexes
and in some cases partitions for a single object where all placed into
the same tablespace. The system in figure 9 was also using an EMC
drive array but due to the errors in file layout it could not take
advantage of the EMC advantages.
Tablespace Segments & Free Space
If we had the mythical setup where a single
table occupied a single tablespace we probably would not have a
significant problem with fragmentation. Fragmentation occurs when
multiple objects with differing segment extent sizes occupy the same
tablespace and undergo dynamic extension and subsequent DBA or system
actions result some of these objects being resized or removed. The
action of resizing or removing segments from a tablespace results in
empty spots, if there are multiple segments in the tablespace, these
empty spots may be randomly distributed. If adjacent extents end up
being freed, we can easily coalesce them into a single larger extent,
but if there is intervening live extent areas, we cannot. These two
fragmentation models are known as honeycomb, when the free extents are
side by side, or swiss cheese, when the extents are separated by live
segments.
Honeycomb fragmentation in a tablespace is
automatically cleaned up by the smon process if the default storage
parameter PCTINCREASE is set greater than zero. For tablespaces where
PCTINCREASE is greater than zero use the ALTER TABLESPACE
tablespace_name COALESCE command to coalesce adjacent areas of free
space.
Some experts have proposed always sizing
objects in a given tablespace using the same extent sizes or equal
multiples of the same extent sizes. There is probably some merit to
this since it will reduce the likelihood of a freed extent not
being able to be reused, however, it will also result in some sparse
objects that have wasted space.
I believe a blended approach is in order where
the sizes for objects are calculated and then an attempt is made to
choose and extent size for objects in the same tablespace that will
reduce the overall number of extents per objects but also allow
maximal reuse of space.
Let?s examine some quick placement guidelines
and move on:
1. Put indexes and tables in as physically
separate disk areas as possible
2. Never put rollback segments in with data or
index segments
3. Place redo logs in their own area
4. Separate highly active tables and indexes
into their own tablespaces
5. Group low activity tables and indexes
together (tables with tables and indexes with indexes)
6. Partition high activity tables and indexes
to help balance disk IO and prevent hot disks.
7. Use as many disk controller channels as is
required to reduce channel saturation.
Tables & Indexes/Partitioning
With Oracle8 the capability to partition
tables and indexes was introduced. Prior to Oracle8 partitioned views
where possible but maintaining them was difficult. A partitioned table
is partitioned by ranges of values. In Oracle8i the partitions can be
subpartiioned based on a hash value as well. Indexes can be locally
partitioned (partitioned identically to the tables) or can be globally
partitioned (the index is partitioned on a different scheme than the
table).
Partitioning is helpful where the table size
is large and the values in the table can be readily separated into
discrete ranges. An easy example is when sales are divided by ranges
such as sales division, quarter or store. Partitioning does little
good unless the partitions are placed on different access paths
whether these are disks, arrays or symmetrix devices. If a table or
index is properly partitioned then Oracle parallel operations can
access the partitions in an efficient manner. For some Oracle parallel
operations the table must be partitioned.
Generally you want to examine partitioning candidates and determine
what columns are most frequently used in queries involving ranges of
values such as queries using BETWEEN, inequalities such as greater
than and less than and using the IN clause to portion out a discrete
data set. However, in some cases where a table is extremely large
choosing a partition key that equally divides the table values can
improve parallel scans by equalizing IO across query slave processes.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s
only $19.95 when you buy it directly from the publisher
here.
|