Oracle Disk Layout

* What are the sizes of, and available space on, the disks or arrays to be used with Oracle?

* Is this disk or array used for other non-Oracle applications?

* Has the disk been defragmented (if needed)?

* Is this a raw device (if UNIX)?

* What is the speed of the disk or disks in the array; or what is the I/O saturation point of the controller channel?

* Is this a RAM or an optical disk?

Let's look at each of these questions to determine how the answers affect Oracle:

What are the sizes of, and available space on, the disks or arrays to be used with Oracle? Obviously, if there isn't enough space on the disk, you can't use it. If the size is too small to handle projected growth, then you might want to look at another asset. Oracle files can be moved, but not with that section of the database active. If you enjoy coming in before or after hours or on weekends, then by all means put your database files on an inappropriately sized disk asset.

Is this disk or array used for other non-Oracle applications? This question has a many-sided answer. From the Oracle point of view, if you have a very active non-Oracle application, it will be in contention with Oracle for the disk at every turn. If the non-Oracle application, such as a word processing or a calculation program that uses intermediate result files, results in disk fragmentation (on NT) this is bad if the datafile co-located with it has to grow and can't allocate more contiguous space. From the viewpoint of the other application, if we are talking about export files, archive log files, or growing datafiles, an asset we need to operate may be consumed, thus preventing our operation. Look carefully at the applications you will be sharing the disk assets with; talk with their administrators and make logical usage projections.

Has the disk been defragmented (for NT)? This was covered before but bears repeating. A fragmented disk is of little use to Oracle on NT; it will be a performance issue. Oracle needs contiguous disk space for its datafiles. If the disk hasn't been defragmented, have it checked by the system administrator for fragmentation, and defragment it if required.

Is the disk a raw device (for UNIX)? If the disk is a raw device, this restricts your capability for file naming. Be sure you maintain an accurate log of tablespace mapping to raw devices. Map tablespace and other asset locations ahead of time. Remember, an entire raw partition must be used per Oracle datafile; it cannot be subpartitioned without redoing the entire raw setup. If you must use raw, plan it!

What is the speed of the disk? By speed of disk we are referring to the access and seek times. The disk speed will drive disk throughput. Another item to consider when looking at disk speed is whether or not the disk is on a single or shared controller. Is the DSSI chained? All of these questions affect device throughput. Generally, datafiles and indexes should go on the fastest drives; if you must choose one or the other, put indexes on the fastest. Rollback segments and redo logs can go on the slowest drives as can archive logs and exports.

Is the disk a RAM or an optical disk? Ultimately, the RAM and optical usage ties back to disk speed. A RAM drive should be used for indexes due to its high speed. It is probably not a good candidate for datafiles due to the RAM drive's current size limitations; this may change in the future. An optical drive, due to its relative slowness, is excellent for archives and exports, but probably shouldn't be used for other Oracle files. A possible exception might be large image files (BLOBs) or large document files. Usually, unless you have a rewritable CD system, the tablespaces placed on a CD-ROM will be read-only. With the storage capacities of most optical drives, they make excellent resources for archive logs and exports. They can conceivably provide a single point of access for all required recovery files, even backups. This solves the biggest recovery bottleneck: restoration of required files from tape.

Database-Specific Topics

There are numerous database-specific questions to answer before installation:

* What is the number and size of database tablespaces? What is file placement? How many potential applications? How will extent management for tablespaces be handled (dictionary or local managed)?

* What are the SGA issues?

* What is the number of users, administrative, developer, and application?

* What is the number and placement of control files?

* What is the number and placement of redo logs?

* What is the number and placement of rollback segments? Will new UNDO tablespaces be used?

* Will this database be shared between multiple instances (Oracle RAC)?

* Will this database be distributed?

* Should the tools be linked single-task or independent (two-task)?

* Do we need to plan for external Oracle-managed files or BFILE external files?

       Let's examine each of these as they relate to installation of Oracle.

What Is the Number and Size of Database Tablespaces? What Is File Placement, the Number of Potential Applications? 

These are disk space and create-script-related issues. The number of potential applications will drive the number and size of database tablespaces above and beyond the eight base tablespaces. You will see that these are:

SYSTEM: Contains files owned by the SYS and SYSTEM user.

TOOLS:  Contains files usually owned by SYSTEM but that apply to the Oracle developer's toolset; these files contain base information and details of forms, reports, and menus.

ROLLBACK:  Contains the private rollback segments; its size will depend on number of rollback segments and expected transaction size. May also be an Oracle-configured UNDO tablespace, which means you will also have to plan for how long UNDO data should be retained to allow for flashback query.

DEFAULT USER:  Tablespace in which users can create and destroy temporary, non-application-related tables such as those used in SQL*REPORT for intermediate queries.

TEMPORARY USER:  Tablespace for sorts, joins, and other operations that require temporary disk space for intermediate operations. If this tablespace is not available, and default tablespace is not set for each user, these tables will be created and dropped in the SYSTEM tablespace, resulting in fragmentation. Additionally, a poorly designed join or overly ambitious SELECT statement could result in filling the SYSTEM area and halting the database.

UNDOTBS:  In Oracle9i databases created from default templates in the Database Creation Assistant (DBCA) rollback segments are placed in the UNDO tablespace and are now referred to as UNDO segments. Oracle uses automated management of these UNDO segments by default. This UNDOTBS is used by Oracle9i to hold UNDO segments.

CWMLITE:  This Oracle9i tablespace is used to store OLAPSYS schema objects for the new Online Analytical Processing (OLAP) utilities in Oracle9i and is created when the default templates in the DBCA are used to create a database.

DRSYS:  Used to store CTXUSER and WKSYS schema objects used in advanced indexing options and workspace management utilities and is created when the default templates in the DBCA are used to create a database.

Each application should have its own set of data and index tablespaces. If there are several small applications, you might want to put them in a single large tablespace; but if you can avoid this, it makes application management easier. Each application should also have its own index tablespace. This results in a simple formula for determining the number of tablespaces:

5 + 2 times the number of applications expected

Some applications may require multiple tablespaces, for example where, for performance, you want to separate out large tables from the rest of the application. In one case, a single application generated 13 tablespaces. Most applications aren't as complicated as this and will only require two tablespaces. Of course, the purists will claim each table should be in its own tablespace, but this often is overkill. If you are looking at Oracle Applications installs, there may be over 100 tablespaces configured.

How to size tablespaces is a difficult question to answer because each tablespace will have unique requirements. Here are some general guidelines:

* The SYSTEM tablespace, if you split out the tool tables, should only require 300 to 400 MB of disk space, this has increased dramatically due to increased Java and other stored procedures in the Oracle9i system.

* The TOOLS tablespace will depend entirely on the amount of development you expect. At one site with 16 applications being developed, nearly 90 MB were required for the TOOLS tables.

* The ROLLBACK tablespace will again be driven by the number and size of rollback segments you require. The number and size of rollback segments is driven by the number of transactions per rollback segment, the number of users, and the maximum size of nonbatch transactions. With Oracle8i and Oracle9i, you can create a large rollback segment and leave it offline until it is needed for a large transaction, and then use the SET TRANSACTION USE ROLLBACK SEGMENT command to utilize it after bringing it online. The number of rollback segments is driven by the number of expected transactions and can be estimated by the equation:

NUMBER OF DML TRANSACTIONS / TRANSACTIONS PER ROLLBACK SEGMENT

* The number of transactions will be driven by the number of users and types of database operations they will be doing. In fact, if the Oracle kernel sees a violation of the above formula, it will bring online any available public rollback segments. In Oracle9i you should also consider if you wish to use the UNDO tablespace, which takes the place of the rollback segments if it is configured. By default Oracle9i sizes the UNDOTBS at 200 megabytes.

* The DEFAULT USER tablespace size will depend upon the number of users you want to assign to it and the estimated size of tables they will be using. In most cases, 10 to 20 MB is sufficient. If you expect heavy usage, assign quotas to each user.

* The TEMPORARY USER tablespace should be up to twice the size of your largest table, if you use RULE-based optimization and up to four times the size of your largest table for COST-based; it is also dependent on the number of users and the size of sorts or joins they perform. An improperly designed join between large tables can quickly fill a temporary area. For example, an unrestricted outside join of 2,000 row tables will result in a  1-million-row temporary sort table. If those rows are each several hundred bytes long, there goes your temporary space. Unfortunately, there isn't much that can be done other than to train developers or ad hoc query generators not to do unrestricted joins of large tables. If a temporary tablespace gets filled, the users who are assigned to it cannot perform operations requiring temporary space; or, worse, the temporary space may be taken from the SYSTEM area. There is a valid argument for having several temporary areas if you have a large number of users. In one instance, a 100-MB temporary tablespace was completely filled by a single multitable outside join using DECODE statements.

* The CWMLITE and DRSYS tablespaces are usually sized at around 20 megabytes by default.