Tuning Through Distribution

It is desirable in any computing environment to distribute the disk I/O as evenly as possible among the available disk drives and controllers. There were (and still are today) many scenarios for striping or partitioning large tables and indexes across many disk drives. The advent of RAID technology made this process very easy from a hardware point of view. Oracle8 supports partitioning which makes the distribution of specific data much easier to implement and administrate. It is equally important to have the previously mentioned SYSTEM, ROLLBACK, and TEMPORARY tablespaces on separate devices, preferably even separate disk controllers. If the application has a high number of INSERTs and UPDATEs, the online redo logs should be on yet another separate disk drive. Each of these tablespaces will incur large volumes of I/O, depending of course on the application. It will noticeably enhance performance to have the files associated with these three tablespaces separated from each other and separated from the other tablespaces.

In the Oracle tablespace layout example (see Figure 19.4), please note that I have intentionally created APPL_TABLE and APPL_INDEX tablespaces. This will logically allow me to create the tables and indexes in separate storage areas. I would create the underlying files assigned to these tablespaces on separate disk devices, preferably with these disk devices on separate disk controllers.

---

Tip:  
Treat RAID partitions as separate disk drives. It would defeat the purpose of distributing the I/O if both data and related index tablespaces used the same RAID partitions.

---

Take this I/O distribution one step further. Review the FROM and WHERE clauses of SELECT statements that access medium or large objects. Make sure the largest of the tables are on separate devices from the smaller tables. Always keep the indexes on separate drives from the tables. Always put the parent table, child tables, and the primary/foreign key indexes all on separate drives if possible. Referential integrity makes this selection distribution easy as the parent and child tables are easy to identify and should reside on separate physical devices.

Sizing Defaults

Oracle manages its space inside the tablespace in units called extents. An extent can be one or more contiguous Oracle blocks. Extents are preallocated space (contiguous Oracle blocks) assigned to individual data-oriented objects. The Oracle block size is defined at CREATE DATABASE time. Extent sizes are given in sizes of bytes and Oracle does the calculation converting the extent size to Oracle blocks, rounding up to the next multiple of block size.

The tablespace has a default storage clause (see Listing 19.1). This default is used only when a storage clause is not specified on objects being created in that tablespace. The storage parameter can be altered; however, any change in storage parameters will have no effect on already existing object storage usage.

Listing 19.1. Storage clause example.

Create tablespace EXAMPLE

      datafile ‘/ora8/v8.0.3/dbs/Example01.dbs’ size 100K

      default storage (

                  initial 25K next 10K

                  minextents 1 maxextents 100

                  pctincrease 0); 

In Listing 19.1, the tablespace EXAMPLE will be created with one file, Example01.dbs, with a size of 100KB. When the CREATE TABLE SQL statement is entered without a storage clause, the default tablespace storage clause will be used. In this example, if no storage clause is given, the table’s initial extent will be 25KB (rounded up to the next even Oracle block size), with each additional extent needed of 10KB. MINEXTENTS is the original number of extents to be allocated, and MAXEXTENTS will be all the extents that will be allocated to this object. If this MAXEXTENTS is reached, the error ORA-01631 max # extents (100) in table xxxxx displays. Pctincrease is a value that will increase the next extent size by this factor over the size of the previously created extent.