Oracle Optimal Flexible Architecture (OFA)


Physical Database Structure

An Oracle database consists of a number of operating system files. Tablespaces are an Oracle managed logical represenation of underlying operating system level datafiles. A tablespace comprises one or more datafiles. Note that multiple tablespaces can not share a single datafile. Datafiles make up the physical storage space for the database. Tablespaces are logically divided into segments. Datafiles are physically divided into extents which are divided into blocks.

The block is the smallest physical structure. Block size can not be altered after database creation. Block size can determine database access speed, dependant of course on the type of system. A database containing mostly short length records is suited to a smaller block size whereas a multimedia storage database is probably better suited to a much larger block size. The block size can be altered in the initialisation parameters file by changing DB_BLOCK_SIZE parameter. It is suggested that the block size should be set to the largest value allowed for the operating system in use ? This is questionable.

Block space usage is determined by the PCTFREE, PCTUSED, INITTRANS and MAXTRANS database object storage clause settings. PCTFREE reserves space for future row length increases and thus block expansion. PCTUSED sets a threshold above which no new records can be inserted into a block. INITTRANS determines conncurrent allocated in each block header when each block is allocated. MAXTRANS determines the maximum number of concurrent transactions allowed for a block.

When creating segments they must be created for enough contiguous space for the initial size of the database. A non-contiguous segment would lead to a large amount of random I/O onto the disk rather than sequential data access.

Oracle Optimal Flexible Architecture (OFA)

The OFA is the Oracle recommended database file configuration or setup. Ease of maintenance and reliability is assisted by file distribution plus datafile naming standards. OFA makes a point of suggesting the following standards.

Logical Tablespace Database Layout for OFA

Different types of data can be placed into different types of tablespaces. In general the following rules apply.

Below is an example of a general database tablespace structure. Note how different tablespaces contain differing types of functionality. Also note that these different tablespaces can each contain multiple datafiles. These datafiles can be placed onto separate disks to reduce I/O contention and thus increase performance speed. This is the Optimal Flexible Architecture (OFA) where differing types of data and/or functionality are placed into separated logical groups.

OFA and Datafile I/O Contention

Contention between datafiles placed on the same disk can be a performance problem. This can be resolved to a certain extent by putting separate datafiles onto separate disks. These datafiles should be separated according to usage. Percentage uses can be assessed as to the datafiles using their tablespace functionality types in order to determine the potentially most efficient location of tablespace datafiles in relation to other tablespace datafiles in the database.

95% of all database I/O activity will usually be concentrated in a limtied set of tablespaces. Obviously differeing types of databases can change usage percentages to a certain extent such as between online transaction processing (OLTP) databases and primarily batch processing databases. Note that I/O percentages can be assessed using database computed statistics and other accessible values.

Tablespace % Total Usage
DATA_TRANSACTIONAL - OLTP / BATCH 50 / >>50
ROLLBACK / SPECIALITY 20 / ?
SYSTEM 15
INDEX_TRANSACTIONAL 10
TEMPORARY 2
DATA_STATIC / <CACHE> 2
INDEX_STATIC / <CACHE> 1 / <1

Background Process I/O Contention

There are two types of I/O contention.

Concurrent I/O

Contention occurs when multiple accesses are performed against the same device at the same point in time. This type of contention for resources can be reduced by separating tables, indexes, rollback segments and redo logs to separate disks.

Interference

Interference occurs when writes to sequentially written files are interrupted by reads or writes to other files on the same disk. The DBWR, LGWR and ARCH processes all come into contention whcih each other. The solution in Oracle8 is multples of each of these processes to avoid process interference.