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.

• Allow for easy maintenance and future growth.
• Sufficient disk space.
• Lower hardware costs by utilising Oracle to the maximum.
• Isolate different applications from each other. This can help to localise the effects of potential hardware failure.
• Two or more disk drives to accomodate for failure.
• Home directories should be capable of movement.
• Place the datafiles into database specific directory structures.
• Allow for support of multiple Oracle versions. Past experience has shown this to be a little scary !
• Allow for the separate administration of databases, especially those residing on a single machine in a single Oracle installation. This is fun. Set the ORACLE_SID=<instance_name> whenever executing ANYTHING on the server.
• Name datafiles as being identifiable as datafiles and as being part of a specific database.
• Place tablespace ontp separate physical disks to reduce I/O contention.
• Separate general administration files from specific database administration files.
• Always use explicit path names when executing executables, refering to or loading non-executable scripts. Oracle tends to have a habit of leaving residuals all over the place in terms of multiples of the same file and suprises in the registry in NT and also deleted databases tend to leave a bit of a mess.
• Multiple versions of Oracle installed onto the same machine should be installed into separate directory structures.
• Seperate files and tablespaces in relation to life-span and usage frequency. This will reduce both I/O contention and potential administrator confusion.
• It is recommended in various texts that fewer or less than 8 characters should be used for tablespaces. This may go back to the days of DOS for instance because I have not so far encountered a problem in this respect. In my current position I am intending to rectify this and am creating all new databases to be compliant.
• Be consistent for all databases with respect to file sizes and their relative increments plus always create redo logs for instance, as the same size, unless redo logs are required to be resized due to serious database activity. Small redo logs can result in too much log file switching on very active databases.
• It is best to reserve a single disk for each application in order to reduce contention. However, with the advent of RAID arrays containing automated recovery from mirrored disks the idea of separated disks to manage I/O contention and minimise disasters may be outdated.
• In an Oracle Parallel Server / Clustered machine evironment ALWAYS administer from a single server or machine. This could avoid confusion. Oracle Enterprise Manager is an extremely effective solution in the management of multiple databases in or out of a clustered or Oracle Parrallel Server environment.

Logical Tablespace Database Layout for OFA

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

• The SYSTEM tablespace is created automatically on database creation. This tablespace contains the meta-data in the database,ie. the data about the data. DO NOT put anything into the tablespace deliberately. Leave the SYSTEM tablespace for Oracle internal use only !!!
• Separate DATA and INDEX tablespaces, preferably on two separate disks. This means that when a query is executed and both data and indexes are used that I/O contention will be minimised since two disks are being read simultaneously.
• Create separate tablespaces for both rollback (RBS) and temporary internal work such as sorting executed by Oracle (TEMP). Rollback segments maintain before-change images of database changes. Not only should the rollback tablespace and the temporary tablespace be separated but they should be apart from the rest of the database tablespaces such as SYSTEM, INDEXES and DATA tablespaces since rollback and temporary tablespaces have unique functionality.
• Place general user work areas and storage intoa USER tablespace.
• Create tools or application tablespace for applications.

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.

• SYSTEM - meta-data or data dictionary, all database objects.
• DATA_STATIC - static, non-changeable data. Some of the tables could be cached and some packages could be pinned into memory.
• DATA_TRANSACTIONAL - frequently changing data. Note that separate applications can be divided into separate tablespaces.
• INDEX_STATIC - indexes for static data.
• INDEX_TRANSACTIONAL - indexes for frequently changing data.
• RBS - normal operations rollback segments.
• RBS_SPECIALITY - special rollback segments usually assigned to specific transactions such as very large rollback segments for very large queries.
• TEMP - temporary sorting area.
• TEMP_BY_USER - temporary sorting areas assigned to specific users.
• TOOLS/APPLICATIONS_ORACLE - database management and application tools. Note that separate applications can be divided into separate tablespaces.
• TOOLS/APPLICATIONS_ORACLE_INDEXES - indexes for tools or applications tablespace.
• USERS_DEVELOPMENT - development database user created objects. Parts or all of these development areas may eventually be released to production areas. Note that it is a good idea to maintain completely separate databases for production, development and perhaps even pre-production testing aswell. Applications accessing the ORacle database can theoretically cause an Oracle database to crash.
• PARTITIONS - create multiple partitions for partition table segments and partition index segments.
• TEMP_LOAD - database loading processing temporary area such as when using SQL^*Loader.

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.

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.