13
Building a Database for Performance

One of the first stages in managing a database is the initial database creation. Although performance modifications can be made to both the database and to the Oracle instance at a later time, much can be gained by carefully designing the database for the intended needs. This chapter contains the following sections:

• Initial Database Creation

• Creating Tables for Good Performance

• Loading and Indexing Data

• Initial Instance Configuration

• Setting up OS, Database, and Network Monitoring

  Note: This chapter is an overview of. Before reading this chapter, it may be very valuable to read the information in the Oracle9i Database Performance Methods manual. For detailed information on memory and I/O configuration, see the other chapters in this part.

Initial Database Creation

Database Creation using the Installer

The Oracle Installer lets you create a database during software installation or at a later time using the Database Creation Assistant. This is an efficient way of creating databases for small to medium size environments, and it provides a straightforward graphical user interface. However, this procedure sets some limits on the possibilities for various options, and it is therefore not recommended for database creation in larger environments.

Manual Database Creation

A manual approach provides full freedom for different configuration options. This is especially important for larger environments. A manual approach typically involves designing multiple parameter files for use during the various stages, running SQL scripts for the initial CREATE DATABASE and subsequent CREATE TABLESPACE statements, running necessary data dictionary scripts, and so on.

Parameters Necessary for Initial Database Creation

The initialization parameter file is read whenever an Oracle instance is started, including the very first start before the database is created. Very few parameters must be set before the initial database creation, because they cannot be modified at a later time. These parameters are:

DB_BLOCK_SIZE	This sets the size of the Oracle database blocks stored in the database files and cached in the SGA. The range of values depends on the operating system, but it is typically powers of two in the range 2048 to 16384. Common values are 4096 or 8192 for transaction processing systems and higher values for database warehouse systems.
DB_NAME DB_DOMAIN	These set the name of the database and the domain name of the database respectively. Although they can be changed at a later time, it is highly advisable to set these correctly before the initial creation. The names chosen must be reflected in the SQL*Net configuration as well.
COMPATIBLE	This specifies the release with which the Oracle server must maintain compatibility. It allows you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. If your application was designed for a specific release of Oracle, and you are actually installing a later release, then you might want to set this parameter to the version of the previous release.

See Also: Chapter 15, "I/O Configuration and Design" and Oracle9i Database Performance Methods for more information on the efficient use of datafiles in both the initial database creation and subsequent tablespace creations Oracle9i Database Reference for more information on initialization parameters

The CREATE DATABASE Statement

The first SQL statement that is executed after startup of the initial instance is the CREATE DATABASE statement. This creates the initial system tablespace, creates the initial redo logfiles, and sets certain database options. The following options cannot be changed or can only be changed with difficulty at a later time:

Character set	The character set specified by this option identifies the character set used for SQL text, for the internal data dictionary, and most importantly for text stored as datatypes CHAR, VARCHAR, or VARCHAR2. After data including any type of national characters has been loaded, the character set cannot be changed.
National character set	This character set is used for the datatypes NCHAR, NVARCHAR, and NVARCHAR2. In general, as with the regular character set, it cannot be changed.
SQL.BSQ file	This creates the internal data dictionary. For information on modifying this file, see Chapter 15, "I/O Configuration and Design".
Location of initial datafile	The initial datafile(s) that will make up the system tablespace should be set with care. They can be modified later, but the procedure involves a complete shutdown of the instance.

Example of a CREATE DATABASE Script

CONNECT SYS/ORACLE AS SYSDBA
STARTUP NOMOUNT pfile=/u01/admin/init_create.ora'
CREATE DATABASE "dbname"
DATAFILE '/u01/oradata/system01.dbf' size 200M
LOGFILE  '/u02/oradata/redo01.dbf' size 100M,
         '/u02/oradata/redo02.dbf' size 100M
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "UTF8";

Running Data Dictionary Scripts

After running the CREATE DATABASE statement, certain catalog scripts must be executed. They are found in the RDBMS/ADMIN directory on UNIX or the RDBMS\ADMIN directory on Windows, under the ORACLE_HOME directory. The following scripts must be executed.

CATALOG.SQL	Needed for all normal data dictionary views
CATPROC.SQL	Needed to load the initial PL/SQL environment

Note: When specific options or features are in use (for example, Java or replication), more scripts are necessary. These are documented with each individual option.

Example of Executing Required Data Dictionary Scripts

CONNECT SYS/ORACLE AS SYSDBA
@@CATALOG
@@CATPROC

The use of the double at-sign forces execution of these scripts from the proper directory.

Sizing Redo Log Files

The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Small log files can increase checkpoint activity and reduce performance. Because the recommendation on I/O distribution for high performance is to use separate disks for the redo log files, there is no reason not to make them large. A potential problem with large redo log files is that these are a single point of failure if redo log mirroring is not in effect.

It is not possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes.

The complete set of required redo log files can be created during database creation. After they are created, the size of a redo log size cannot be changed. However, new, larger files can be added later, and the original (smaller) ones can subsequently be dropped.

Not much can be done to speed up the creation of the initial database and the loading of necessary data dictionary views from catalog SQL files. These steps must be run serially after each other.

Note: Although the size of the redo log files does not affect LGWR performance, it can affect DBWR and checkpoint behavior.

Creating Subsequent Tablespaces

After creating the initial database, several extra tablespaces must be created. All databases should have at least three tablespaces in addition to the system tablespace: a temporary tablespace, which is used for things like sorting; a rollback tablespace, which is used to store rollback segments or is designated as the automatic undo management segment; and at least one tablespace for actual application use. In most cases, applications require several tablespaces. For extremely large tablespaces with many datafiles, multiple ALTER TABLESPACE x ADD DATAFILE Y statements can also be run in parallel.

During tablespace creation, the datafiles that make up the tablespace are initialized with zero values. Oracle does this to ensure that all datafiles can be written in their entirety, but this can obviously be a lengthy process if done in serial. Therefore, run multiple CREATE TABLESPACE statements concurrently to speed up the tablespace creation process. The most efficient way to do this is to run one SQL script for each set of available disks.

For permanent tables, the choice between local and global extent management on tablespace creation can have a large effect on performance. For any permanent tablespace that has moderate to large insert, modify, or delete operations compared to reads, local extent management should be chosen.

Note: Tablespaces created as temporary cannot use local extent management.

Examples of How to Concurrently Create Tablespaces

CONNECT SYSTEM/MANAGER
CREATE TABLESPACE appdata DATAFILE
  '/u02/oradata/appdata01.dbf' size 1000M;

In another session:

CONNECT SYSTEM/MANAGER
CREATE TABLESPACE appindex DATAFILE
  '/u03/oradata/appindex01.dbf' size 1000M;

Creating Tables for Good Performance

When you create a segment, such as a table, Oracle allocates space in the database for the data. If subsequent database operations cause the data volume to increase and exceed the space allocated, then Oracle extends the segment.

When installing applications, an initial step is to create all necessary tables and indexes. This operation is by itself relatively fast, and not much is gained by doing it in parallel. However, some things require attention:

• Use Locally Managed Tablespaces: If the tablespace which contains the segment is dictionary managed, and there is a significant amount of dynamic extension, this can affect performance. Store such tables in locally managed tablespaces, and determine appropriate extent sizes.

• Choosing Extent Sizes: The extent sizes for segments should be N orders of magnitude greater than the DB_FILE_MULTIBLOCK_READ_COUNT. This ensures that mutliblock reads can be performed effectively.

• Setting Storage Options: Applications should carefully set storage options for the intended use of the table or index. This includes setting the values for PCTFREE and PCTUSED. (Using automatic segment-space management eliminates the need to specify PCTUSED).

• Using Freelists: For objects with high insert activity, freelists can increase scalability. However, the freelist settings must be done when the table is created. After data is inserted, the table cannot be altered to use freelists.

  Note: Freelists have been the traditional method of managing free space within segments. Automatic segment-space management provides a simpler and more efficient way of managing segment space, and completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS attributes for segments. If such attributes are specified, they are ignored. To use automatic segment-space management, create locally managed tablespaces, with the segment space management clause set to AUTO. For more information on creating and using automatic segment-space management, see Oracle9i Database Administrator's Guide.

• Setting INITRANS: Each datablock has a number of transaction entries that are used for row locking purposes. Initially, this number is specified by the INITRANS parameter, and the default value (1 for tables, 2 for indexes) is generally sufficient. However, if a table or index is known to have many rows per block with a high possibility of many concurrent updates, then it is beneficial to set a higher value. This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object.

Loading and Indexing Data

Many applications need to load data as part of the initial application installation process. This can be fixed data, such as postal code or other type of lookup data, or it can be actual data originating in older systems. Oracle's SQL*Loader tool is the most efficient way to load a substantial amount of data.

Using SQL*Loader for Good Performance

When running SQL*Loader, you specify to use either the conventional path or the direct path. The conventional path uses ordinary SQL INSERT statements to load data into the tables, which means that the loading can be done concurrently with other database operations. However, the loading then is also limited by the normal INSERT performance. For quick loading of large amounts of data, choose the direct path. With the direct path, the loading process bypasses SQL and loads directly into the database blocks. During this type of load, normal operation on the table (or partition for partitioned tables) cannot be performed.

The following tips could help speed up the data loading process using SQL*Loader:

• Use fixed length fields rather than delimited or quoted fields. This reduces the time needed for SQL*Loader to read and interpret the input file.

• Run SQL*Loader locally rather than via a network connection.

• Load numbers read from the input file as text rather than in binary format. This might be faster, especially if the numbers are loaded without any computation. This is true for integers and for floating point numbers.

• For very large load operations, use SQL*Loader to do parallel data load.

  See Also: Oracle9i Database Utilities for detailed information on SQL*Loader

Efficient Index Creation

The most efficient way to create indexes is to create them after data has been loaded. By doing this, space management becomes much simpler, and no index maintenance takes place for each row inserted. SQL*Loader automatically does this, but if you are using other methods to do initial data load, you might need to do this manually. Additionally, index creation can be done in parallel using the PARALLEL clause of the CREATE INDEX statement. However, SQL*Loader is not able to do this, so you must manually create indexes in parallel after loading data.

During index creation on tables that contain data, the data must be sorted. This sorting is done in the fastest possible way, if all available memory is used for sorting. This can be controlled with the startup parameter SORT_AREA_SIZE. The value of this parameter should be set using the following rules:

1. Find the amount of available memory by subtracting the size of the SGA and the size of the operating system from the total system memory.

2. Divide this amount by the number of parallel slaves that you will use; this is typically the same as the number of CPUs.

3. Subtract a process overhead, typically a five to ten megabytes, to get the value for SORT_AREA_SIZE.

   Note: You can also save time on index creating operations (or fast rebuilds) with on the fly statistics generation.

Example of Creating Indexes Efficiently

A system with 512 Mb memory runs an Oracle instance with a 100 Mb SGA, and the operating system uses 50 Mb. Hence, the memory available for sorting is 362 Mb (512 minus 50 minus 100). If the system has four CPUs and you run with four parallel slaves, then each of these will have 90 Mb available, 10 Mb are set aside for process overhead, and SORT_AREA_SIZE should be set to 80 Mb. This can be done either in the initialization file or on a per session basis with a statement like the following:

ALTER SESSION SET SORT_AREA_SIZE = 80000000;

Initial Instance Configuration

A running Oracle instance is configured using startup parameters, which are set in the initialization parameter file. These parameters influence the behavior of the running instance, including influencing performance. In general, a very simple initialization file with few relevant settings covers most situations, and the initialization file should not be the first place you expect to do performance tuning, except for the very few parameters shown below.

See Also: Chapter 14, "Memory Configuration and Use"

The following describes the parameters necessary in a minimal initialization file. Although these parameters are necessary, but they have no performance impact:

DB_NAME	Name of the database. This should match the ORACLE_SID environment variable.
DB_DOMAIN	Location of the database in Internet dot notation.
OPEN_CURSORS	Limit on the maximum number of cursors (active SQL statements) per session. The setting is application-dependent, and the default, in many cases, is sufficient.
CONTROL_FILES	Set to contain at least two files on different disk drives to prevent failures from control file loss.
DB_FILES	Set to the maximum number of files that can assigned to the database.

The following list includes the most important parameters to set with performance implications:

DB_BLOCK_SIZE	Sets the database block size.
DB_BLOCK_BUFFERS	Size of the buffer cache in the SGA. There are no good and simple rules to set a value, which is very application dependent, but typical values are in the range of twenty to fifty per user session. More often, this value is set too high than too low.
SHARED_POOL_SIZE	Sets the size of the shared pool in the SGA. The setting is application-dependent, but it is typically is in the range of a few to a few tens of megabytes per user session.
PROCESSES	Sets the maximum number of processes that can be started by that instance. This is the most important primary parameter to set, because many other parameter values are deduced from this.
SESSIONS	This is set by default from the value of processes. However, if you are using the shared server, then the deduced value is likely to be insufficient.
JAVA_POOL_SIZE	If you are using Java stored procedures, then this parameter should be set depending on the actual requirements of memory for the Java environment.
LOG_ARCHIVE_XXX	Enables redo log archiving. See Oracle9i User-Managed Backup and Recovery Guide.
ROLLBACK_SEGMENTS	Allocates one or more rollback segments by name to this instance. If you set this parameter, the instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated as TRANSACTIONS /TRANSACTIONS_PER_ROLLBACK_SEGMENT).

Example of a Minimal Initialization File

In many cases, only the parameters mentioned below need to be set to appropriate values for the Oracle instance to be reasonable well-tuned. Below is an example of such an initialization file:

DB_NAME = finance
DB_DOMAIN = hq.company.com
CONTROL_FILES = ('/u01/database/control1.dbf', '/u02/database/control2.dbf')
DB_BLOCK_SIZE = 8192
DB_BLOCK_BUFFERS = 12000 # this is approximately 100 Mb
DB_FILES = 200 # Maximum 200 files in the database
SHARED_POOL_SIZE = 100000000 # 100 Mb
PROCESSES = 80 # Would be good for approximately 70
# directly connected users
# log_archive_XXX   
# Set various archiving parameters 

Configuring Rollback Segments

Oracle needs rollback space to keep information for read consistency, for recovery, and for actual rollback statements. This is kept either in rollback segments or in one or more automatic undo management tablespaces.

The V$UNDOSTAT view contains statistics for monitoring and tuning undo space. Using this view, you can better estimate the amount of undo space required for the current workload. Oracle also uses this information to help tune undo usage in the system. The V$ROLLSTAT view contains information about the behavior of the undo segments in the undo tablespace.

See Also: Oracle9i Database Administrator's Guide for detailed information on managing undo space using rollback segments or using automatic undo management Chapter 24, "Dynamic Performance Views for Tuning" for more information on the V$UNDOSTAT and V$ROLLBACK views

Setting up OS, Database, and Network Monitoring

To effectively diagnose performance problems, it is vital to have an established performance baseline for later comparison when the system is running poorly. Without a baseline data point, it can be very difficult to identify new problems. For example, perhaps the volume of transactions on the system has increased, or the transaction profile or application has changed, or the number of users has increased.

After the database is created, tables are created, data is loaded and indexed, and the instance is configured, it is time to set up monitoring procedures.

See Also: Chapter 20, "Oracle Tools to Gather Database Statistics"