Planning a Database

Perform the following tasks in planning your database:

• Task 1: Define Database Requirements

• Task 2: Estimate Database Hardware Requirements

• Task 3: Plan an Optimal Flexible Architecture (OFA) Database Structure

• Task 4: Decide on Raw Devices

• Task 5: Define Database Backup Strategy

• Task 6: Plan Space Management Parameters

Task 1: Define Database Requirements

Consider the following criteria:

• performance: do you have a high volume of transactions?

• security: how sensitive is the data?

• reliability: is system downtime acceptable at any time; can downtime be scheduled?

• scalability: will demands on the system increase sharply?

• availability: are you running OLTP or decision support services?

****************

Task 2: Estimate Database Hardware Requirements

Use I/O Rates to Calculate Disk Drives Needed

For example, if four physical I/Os are needed for one transaction, and your application performs 100 transactions per second, you need about 400 disk I/Os per second. One disk can perform approximately 50 random I/O operations per second, so the system requires at least 8 disk drives (400/50) with the data files balanced across the disks.

Also, spread disk drives between many controllers. Most disk controllers can only sustain two drives doing sequential reads simultaneously. If tables are used for parallel queries, no more than two disks should be active on a single controller.

See Also: "Tools for Planning and Tuning a Database" for more information on the TKPROF utility.

Configure the UNIX Kernel for Oracle7

See Also: Chapter 3, "Setting the Environment", in the Oracle7 Installation Guide for your platform for information on configuring and rebuilding the UNIX kernel.

****************

Task 3: Plan an Optimal Flexible Architecture (OFA) Database Structure

When you install the Oracle7 Server, tools, applications, and databases, you decide where to place files and how to allocate data across disks. Oracle allows great flexibility for these installation decisions. OFA requirements and rules are cited throughout the first three chapters of this guide.

OFA is designed to:

• Organize large amounts of complicated software and data on disk to avoid device bottlenecks and poor performance.

• Facilitate routine administrative tasks like software and data backup functions, which are often vulnerable to data corruption.

• Alleviate switching among multiple Oracle databases.

• Adequately manage and administer database growth.

OFA Requirements

1. Organize the file system to allow easy administration and accommodate scalability for:

• adding data into existing databases

• adding users

• creating databases

• adding hardware

2. Distribute I/O loads across enough disk drives to prevent performance bottlenecks.

3. Minimize hardware costs when it does not conflict with operational considerations.

4. Ensure that drive failures impact as few applications as possible.

5. Be able to distribute the following items across multiple disk drives:

• the collection of home directories

• the contents of an individual home directory

6. Ensure it is possible to add, move, or delete login home directories without having to revise programs that refer to them.

7. Separate categories of files into independent UNIX directory subtrees so that files in one category are minimally affected by operations upon files in other categories.

8. Be able to execute multiple versions of applications software simultaneously. Cutover after upgrades must be simple for the administrator and transparent for the user.

9. Separate administrative information about one database from that of others; ensure a reasonable structure for the organization and storage of administrative data.

10. Name the database files so:

• database files are easily distinguishable from all other files

• files of one database are easily distinguishable from files of another database

• control files, redo log files, and data files are identifiable as such

• the association of data file to tablespace is clearly indicated

11. Separate tablespace contents to:

• minimize tablespace free space fragmentation

• minimize I/O request contention

• maximize administrative flexibility

12. Tune I/O load across all drives, including drives storing Oracle data in raw devices.

13. For Oracle Parallel Server Installations:

• Store administrative data in a central place, accessible to all database administrators.

• Associate administrative data for an instance with the instance by the file name.

Select an Installation Configuration

See Also: Chapter 20, "Client/Server Architecture" in Oracle7 Server Concepts for more information on installation configurations.

****************

Task 4: Decide on Raw Devices

Advantages of Raw Devices

• faster performance because the Oracle Server bypasses the UNIX buffer cache

• savings in memory usage because the Oracle Server does not use the UNIX buffer cache for database block reads and writes

• increased output performance using asynchronous I/O

• increased throughput

Disadvantages of Raw Devices

• You must devote an entire disk partition to a single database file, leading to wasted disk space or a shortage of disk space.

• Backing up raw devices can be cumbersome compared to backing up file system database files.

• Raw devices limit your ability to improve performance.

• Raw devices may reduce your random-access read performance.

• Raw devices can require more volume than buffered I/O configurations.

• I/O load balancing and adding files to your database can be more difficult with raw devices.

• you cannot test database recovery before implementing your production database

• you do not have enough disk volume to leave two or more large, unformatted disk slices available for unanticipated growth or I/O balancing

Other Raw Device Considerations

Setting up Raw Partitions

• Avoid using the first cylinder in the a partition for non-root file systems or raw partitions.

• Always partition along cylinder boundaries.

• Choose a single size, or small set of standard sizes, for all raw devices used to store Oracle database files. Select a size that allows you to create a large number of files, each large enough to be convenient.

For example, if you have a 2 GB drive, you can divide it into 10 partitions of 200 MB each. Any tablespace using raw devices should stripe them across several drives. If possible, the striping should be done with a logical volume manager.

• Use symbolic links to integrate raw devices with the file system.

Raw Redo Logs

Using Logical Volumes

See Also: The Oracle7 Reference Addendum for your platform to determine if your system supports an LVM.

Balancing Disk I/O with a Logical Volume Manager

• Use small stripes for online transaction processing (OLTP) systems requiring random access.

• Use medium-sized stripes for the sequentially accessed data found in decision support applications or when using the parallel query option.

• Use large stripes or no stripes for redo logs. Stripe size should be a few times larger than the product of the initialization parameters DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE.

****************

Task 5: Define Database Backup Strategy

See Also: Chapter 4, "Backing Up and Recovering Oracle7 on UNIX" in this guide and Part V, "Database Backup and Recovery", in the Oracle7 Server Administrator's Guide for information on backup and recovery.

****************

Task 6: Plan Space Management Parameters

Setting Storage Parameters Appropriately

Pre-allocating Space for Tables, Indexes and Rollback Segments

Storage Parameter	Default Setting
INITIAL	5 database blocks
NEXT	5 database blocks
MINEXTENTS	1 database block
MAXEXTENTS	Determined by block size (see Table 1 - 2). Only set if you want $MAXEXTENTS to be less than the value dictated by block size.
PCTINCREASE	0

Table 1 - 1. Storage Parameters and Default Settings

When tables are created without a storage parameter, a default is set. To check the parameters in a running database, use the following SQL command:

SELECT * FROM DBA_TABLESPACES;

See Also: Chapter 1, "Oracle7 Server", in the Oracle7 Reference Addendum for your platform and Chapter 1, "Initialization Parameters", in the Oracle7 Server Reference for default settings for initialization parameters.

Adjust the value of the PCTFREE parameter to allocate space in the data blocks:

• Raise the value of PCTFREE to leave enough room for updates and inserts to avoid chaining blocks.

• Lower the value of PCTFREE to save space for read-only data.

Initializing Data and Index Segments

Set NEXT to the same value as INITIAL.

Suggestion: Set INITIAL to one-tenth the size of the file.

PCTINCREASE should normally be set to zero. The number of possible extents is determined by the block size, as shown in Table 1 - 2.

Block Size	Number of Extents	Platforms and Uses for Block Size (most can be set larger)
2 KB	121	Sun, Hewlett-Packard, Sequent, most UNIX platforms
4 KB	249	IBM-AIX, Sequent (for large databases)
8 KB	507	For VLDBs
16 KB	1017	For decision-support VLDBs

Table 1 - 2. Block Size and Number of Extents

Initializing Temporary Segments and Rollback Segments

where:

k	is an integer between 1 and 20 (typically 2 - 3)
s	is the sort area (in data blocks)
b	is one data block

Set NEXT to ks and PCTINCREASE to zero.

Rollback segments should be allocated initially as 20 to 40 equal-sized extents (for example, MINEXTENTS=30). Each extent (INITIAL and NEXT) should be about 0.25 to 0.50 percent of the size of the largest active table. MAXEXTENTS should be set to a high value. Try to allocate at least one rollback segment per available disk drive to spread the I/O as widely as possible.

Suggestion: Oracle Corporation recommends the following guidelines:

• Create one rollback segment per four active transactions.

• Never create more segments than the instance maximum number of concurrently active transactions.

• Never create more than 50 segments.