13
Building a Database for Performance

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 Operating System, Database, and Network Monitoring

  Note: This chapter is an overview of Oracle's new methodology for designing a database for performance. Before reading this chapter, it is important to read the information in the Oracle9i Database Performance Planning manual. For detailed information on memory and I/O configuration, see the other chapters in Part III.

Initial Database Creation

One of the first stages in managing a database is the initial database creation. This section describes important steps in the creation of an Oracle database.

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. There are very few parameters that cannot be modified at a later time. The most important parameters to set correctly at database creation time are listed in Table 13-1.

Table 13-1  Important Initialization Parameters for Database Creation

Parameter	Description
DB_BLOCK_SIZE	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 and DB_DOMAIN	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	Specifies the release with which the Oracle server must maintain compatibility. It lets you 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.

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 options listed in Table 13-2 cannot be changed or can only be changed with difficulty at a later time.

Table 13-2  Database Options for Initial Creation

Database Options	Description
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.
EXTENT MANAGEMENT LOCAL	Use the EXTENT MANAGEMENT LOCAL clause to create a locally managed SYSTEM tablespace. AUTOALLOCATE is the default; extent sizes are chosen by the system. EXTENT SIZE UNIFORM is not available for the SYSTEM tablespace.
Default temporary tablespace	If you specify EXTENT MANAGEMENT LOCAL, then you must also specify the default temporary tablespace.
MAXDATAFILES	The maximum number of data files.
MAXLOGFILES	The maximum number of log files.

Example 13-1 Sample 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"
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE mytemp TEMPFILE 'temp.f' SIZE 1000M
MAXDATAFILES = 50
MAXLOGFILES = 5;

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 13-2 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.

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 special "empty" block images. TEMPFILES are not initialized.

Oracle does this to ensure that all datafiles can be written in their entirety, but this can obviously be a lengthy process if done serially. Therefore, run multiple CREATE TABLESPACE statements concurrently to speed up the tablespace creation process. See the SQL statement in Example 13-3. 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.

Creating Permanent Tablespaces - Automatic Segment-Space Management

For permanent tablespaces, Oracle Corporation recommends using automatic segment-space management. Such tablespaces (often referred to as bitmapped tablespaces) are locally managed tablespaces with bitmap segment space management. They are available with Oracle9i Release 1 (9.0.1) and later.

Creating Temporary Tablespaces

Temporary tablespaces can be dictionary-managed or locally managed. With Oracle9i Release 1 (9.0.1) and later, Oracle Corporation recommends use of locally managed temporary tablespaces. Example 13-3 shows how you can create a temporary tablespace with local extent management:

Example 13-3 Creating a Temporary Tablespace

CONNECT SYSTEM/MANAGER
CREATE TABLESPACE appdata DATAFILE
  '/u02/oradata/appdata01.dbf' size 1000M;
CREATE TEMPORARY TABLESPACE mytemp TEMPFILE 'temp.f' SIZE 1000M;

In another session:

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

Creating Tables for Good Performance

When installing applications, an initial step is to create all necessary tables and indexes. 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 creating tables and indexes, note the following:

• Specify automatic segment-space management for tablespaces

  This allows Oracle to automatically manage segment space for best performance.

• Set storage options carefully

  Applications should carefully set storage options for the intended use of the table or index. This includes setting the value for PCTFREE. Note that using automatic segment-space management eliminates the need to specify PCTUSED.

  Note: Use of free lists is no longer encouraged. To use automatic segment-space management, create locally managed tablespaces, with the segment space management clause set to AUTO.

• Set the INITRANS value higher if necessary

  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 for each 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.

  See Also: Oracle9i Real Application Clusters Concepts for more information about Real Application Clusters storage considerations Oracle9i Real Application Clusters Administration for more information about associating instances and users with free lists and free list groups Oracle9i Real Application Clusters Deployment and Performance for more information about configuring multiblock lock assignments Oracle9i Real Application Clusters Deployment and Performance for more information about using free lists and free list groups in Real Application Clusters

  Note: Note that the operation of creating tables is relatively fast, and not much is gained by doing it in parallel.

Data Segment Compression

Data segment compression reduces disk use and memory use (specifically, the buffer cache), often leading to a better scaleup for read-only operations. Data segment compression can also speed up query execution.

Tuning to Achieve a Better Compression Ratio

Oracle9i Release 2 (9.2) achieves a good compression ratio in many cases with no special tuning. However, if you need a better compression ratio, tuning can improve it slightly in some cases and very substantially in other cases.

Heap-organized block-level compression works by eliminating column value repetitions within individual blocks. This is done by moving such repeated column values into a shared block-level symbol table and replacing occurrences with references into the symbol table. Therefore, the compression ratio is higher in blocks that have more repeated values. As a database administrator or application developer, you can take advantage of this fact by reorganizing rows in the segments that you want to compress, to increase the likelihood of such repetitions.

For a single-column table, you can order table rows by the column value, using a CREATE TABLE AS SELECT with an ORDER BY clause.

You can also apply this method to a table in which one column has low cardinality and other columns have high cardinalities: Order the table rows by the low cardinality column.

The following views contain information about column cardinalities within segments:

• ALL_TAB_COL_STATISTICS
• ALL_PART_COL_STATISTICS
• ALL_SUBPART_COL_STATISTICS

Example 13-4 Estimating Data Segment Compression/Decompression Ratio

You can estimate the compression or decompression ratio for a table table_t by using the following procedure, which allows automatic cleanup:

1. Make sampling repeatable:

   ALTER SESSION SET EVENTS '10193 trace name context forever, level 1';
   
   

2. Spawn a DBMS_JOB to execute once after 1 minute with the following body (cleanup procedure):

   LOCK TABLE table_t$a1 IN EXCLISIVE MODE; 
   DROP TABLE table_t$a1; 
   DROP TABLE table_t$a2;
   
   

3. Create an empty compressed table:

   CREATE TABLE table_t$a1 COMPRESS AS SELECT * FROM table_t WHERE ROWNUM < 1;
   LOCK TABLE table_t$a1 IN SHARE MODE;
   
   

4. Create an empty uncompressed table:

   CREATE TABLE table_t$a2 NOCOMPRESS AS SELECT * FROM table_t 
          WHERE ROWNUM < 1;
   INSERT /*+ APPEND */ INTO table_t$a1 SELECT * FROM table_t 
          SAMPLE BLOCK(x,y);
   INSERT /*+ APPEND */ INTO table_t$a2 SELECT * FROM table_t 
          SAMPLE BLOCK(x,y);
   
   

The data segment compression ratio is the number of blocks in table table_t$a1 divided by number of blocks in table table_t$a2.

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 through 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.

Specifying Memory for Sorting 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. Oracle recommends that you enable automatic sizing of SQL working areas by setting the PGA_AGGREGATE_TARGET initialization parameter.

Specifying Memory for SQL Work Area with SORT_AREA_SIZE

The memory for the SQL work area can also be controlled with the SORT_AREA_SIZE initialization parameter.

The value of the SORT_AREA_SIZE 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 13-5 is an example of setting the SORT_AREA_SIZE parameter.

Example 13-5 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. The memory available for sorting is 362 Mb, which equals 512 minus 50 minus 100. If the system has four CPUs running with four parallel slaves, then each of these will have 90 Mb available. 10 Mb is set aside for process overhead, so SORT_AREA_SIZE should be set to 80 Mb. This can be done either in the initialization file or for each session with the following statement:

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 few parameters shown in Table 13-4.

Table 13-3 describes the parameters necessary in a minimal initialization file. Although these parameters are necessary they have no performance impact:

Table 13-3 Necessary Initialization Parameters Without Performance Impact

Parameter	Description
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) for each 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.

Table 13-4 includes the most important parameters to set with performance implications:

Table 13-4  Important Initialization Parameters With Performance Impact

Parameter	Description
DB_BLOCK_SIZE	Sets the database block size.
DB_CACHE_SIZE	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 for each user session. More often, this value is set too high than too low. DB_BLOCK_BUFFERS has been deprecated.
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 for each 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 in the following example need to be set to appropriate values for the Oracle instance to be reasonable well-tuned. Here 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 Undo Space

Oracle needs undo 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.

Setting up Operating System, 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.