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.