Database Design-Level Tuning

Database design-level tuning builds on the disk I/O distribution concept discussed in the O/S Level tuning section. It is important for this level of tuning to have multiple disk drives available. The DBA and System Admininstrator may need to monitor and redistribute disk I/O while tuning in this manner. It is important to locate related objects on different disk drives, preferably on different disk controllers as well. Physical location of related objects (including those involved with referential integrity) will have a direct effect on performance. The better the I/O distribution on the available disk drives, the better overall performance will be.

An extent is a unit of storage, a contiguous group of data blocks, that is used by data- oriented objects such as tables and indexes. The storage clause either is the default storage clause of the tablespace or is defined at CREATE <object type> SQL statement. When objects use up all the data blocks in the initial extent, Oracle creates the next extent, based again on parameters from the storage clause, and the object then begins storing data in this next extent. The adding of additional extents is known as dynamic allocation and is an expensive and time-consuming process for Oracle. Size INITIAL EXTENT and NEXT EXTENT accurately to limit the amount of dynamic allocation that occurs. Row chaining and row migrations are also expensive processes for Oracle. How these occur is discussed in depth in the following section on fragmentation. It suffices to say that a significant amount of row chaining and row migration will have a negative impact on performance and is easy to avoid.

Table/Index Relationships and Tablespaces

The more evenly I/O is spread across many data-related devices, the better performance is going to be. There are many techniques for striping or placing parts of objects (both table and index) across many available disk drives. RAID technology can stripe data from a hardware point of view, spreading small but even-sized increments of data files across many disk drives. Oracle8 supports partitioning that easily puts portions of tables and indexes in many tablespaces.

---

Tip:  
Treat RAID partitions as separate disk drives. It will defeat the purpose of distributing the I/O if related objects all use the same RAID devices.

---

Review the FROM and WHERE clauses of frequently used or large result set SELECT statements. Try to keep the indexes on separate disk drives from the tables. Referential integrity can make this selection distribution easy. Always put the parent table, child tables, and the primary/foreign key indexes all on separate disk drives if possible.

The SQL statement in Listing 24.4 can be used to monitor both growth and the number of extents, as well as the maximum number of extents assigned.

Listing 24.4. Segment growth and extent assignment.

SELECT  tablespace_name "TABLESPACE",

        segment_name "SEMENT",

        extents "EXTENTS",

        max_extents "MAX EXTENTS",

        bytes "SIZE",

        owner "OWNER"

 FROM   dba_segments

WHERE   owner not in ('SYS','SYSTEM','SCOTT','NET_CONF')

  and   extents > 1

ORDER BY tablespace_name,owner,segment_name; 

Partitioning

Oracle8 partitioning takes advantage of all the benefits of tablespaces discussed so far. Oracle8 can partition or distribute table data and index leaves across many tablespaces based on key fields. Figure 24.3 shows what the famous DEPT table might look like after being partitioned on the field DEPT_NO. The DEPT table contains 4 rows with DEPT_NO equal to 10, 20, 30, and 40.

Figure 24.3.  DEPT table partitioned.

Partitioned tables/indexes are nothing more than a logical collection of tablespaces. The partition levels can be accessed individually, or the entire structure can be accessed as traditional or nonpartitioned tables are today. This flexibility can greatly enhance an application’s performance and availability.

Partitioning greatly enhances database performance. I/O activity can be easily distributed because existing partitions are easy to split. The Oracle optimizer recognizes partitions and can select only from partitions that contain the data being selected. The Oracle Parallel Server utilizes the number of partitions to determine how to divide the query work.

With partitioning, availability is higher, backups are easier, recovery is shorter. Partitions can be backed up online, taking advantage of the hot backup benefits tablespaces already have. This makes the backup/recovery process much quicker. If media recovery is needed on a particular tablespace that belongs to a partitioned object, the unaffected parts of the object (the other partitions) are accessible.

Partitions must contain fields only from the object and may contain one or more fields. More than one field would be a multicolumn partition key. Partitions can share tablespaces or have one partition per tablespace. Tablespaces still have the same space attributes, backup/recovery, and so on, as nonpartitioned tablespaces. It is a good idea to use the MAXVALUE on the final partition to eliminate the chance of getting a 1440: Inserted partition key is beyond highest legal partition key.

Partition tables may not contain LONG, LONG RAW, or LARGE OBJECTS (LOB).