Search the Catalog
Oracle Database Administration: The Essential Reference

Oracle Database Administration: The Essential Reference

By David Kreines & Brian Laskey
1st Edition April 1999
1-56592-516-5, Order Number: 5165
580 pages, $39.95

Sample Chapter 3:

Maximizing Oracle Performance

In this chapter:
Configuration and Tuning--What's the Difference?
Achieving Maximum Performance
Configuring the Operating System
Configuring Oracle
Sizing and Configuring Database Objects
Tuning Oracle

If you ask a room full of Oracle database administrators, "What is the single biggest part of your job?", chances are that the almost universal response will be "Configuration and tuning of the database." Oracle is a very complex and powerful product, and part of its power lies in its ability to get the best possible performance out of each individual database configuration. This chapter presents our approach to the configuration and tuning of an Oracle database and provides guidelines for implementing a high-performance database at your site.

The ongoing, day-to-day responsibility of most Oracle DBAs is to get the best possible performance from the Oracle database. A number of definitions may be offered for "performance," but we define performance as the objective, measurable amount of time required to perform a typical operation in the database in question. Yes, this is a simplistic definition that ignores other metrics like resource utilization, but let's face it: the database is expected to be as fast as possible, so this is a reasonable definition for this purpose.

Entire books have been written on the subject of Oracle performance (see the appendix, Resources for the DBA, for some we consider worthy of your attention [1]), so we cannot hope to address all the intricacies of Oracle performance tuning in a single chapter. Rather, we hope to document a straightforward approach to performance tuning and to provide some practical guidelines that can be applied to a variety of installations.

It is important to realize that every Oracle installation is different in terms of its physical and logical database implementation, the types of transactions processed, and the performance requirements for those transactions. As a result, there cannot be an automatic tuning methodology, although several vendors, including Oracle, have attempted to provide one. Nor can a single set of rules provide a method for maximizing database performance. What can be provided, however, is a methodology that, when properly applied and combined with the knowledge and experience of a working DBA, will result in good performance for any given database.

Configuration and Tuning--What's
the Difference?

Getting maximum performance from an Oracle database requires careful attention to both configuration and tuning of the database. These terms are often used interchangeably, but in reality, they are two different tasks--admittedly, with a bit of grey area between them.

Configuration is the process of setting up the physical and logical components of the database and its host systems, while tuning is the process of modifying the internal behavior of the database so that operations are performed in a particular manner. The entire process can become somewhat circular, since proper tuning often includes modifying the configuration, which then requires another look at tuning. Figure 3-1 shows the basic steps in the configuration and tuning process.

Figure 3-1. The configuration and tuning process

What Can Be Configured?

Some items that can be configured in an Oracle database are the following:

What Can Be Tuned?

Aspects of the Oracle database that can be tuned include the following:

Achieving Maximum Performance

Achieving maximum performance for your Oracle database doesn't just happen--it is usually the result of a lot of hard work, thought, and planning. The rewards, however, are well worth the effort expended: your database runs at peak efficiency, your users are happy, and you look good!

Our approach to maximizing performance is hierarchical in nature. Three distinct areas must be addressed, and they should be addressed in order. These areas are:

These areas are not necessarily independent; in fact, significant changes in one area are likely to require another look at the other areas. They are, however, sequence dependent. That is, you cannot hope to get good performance out of Oracle unless and until you have properly configured and tuned your operating system. Likewise, good query execution depends on a properly configured Oracle environment.

Every Oracle database is different, so we cannot tell you exactly how to accomplish your configuration and tuning goals, or even what those goals should be. What we can do is provide you with an approach we've found to be successful.

Configuring the Operating System

This one is usually easy, since (in most cases) it's not your job! In most installations, there is a system administrator or manager who is responsible for addressing operating system and hardware issues. This system administrator is usually an expert on the hardware and operating system software, and most DBAs are not in a position to second-guess him or her. While deferring to the system administrator's expertise, here are a few points you might want to make sure are addressed:

Because Oracle is a major vendor in the database marketplace, most major hardware vendors have Oracle "experts" on staff who can provide advice on hardware and operating system issues that may affect Oracle's execution on their hardware. Take advantage of this expertise!

Configuring Oracle

Oracle's overall performance is affected by the components that are installed, as well as by how those components are configured. A high-performance Oracle database is essential to obtaining maximum performance from transactions run against that database. This section provides general configuration guidelines and some specific recommendations for configuring SQL*Net/Net8, MTS, Parallel Query, and Parallel Server.

Configuration Guidelines

While every installation is different, there are some general configuration guidelines that can be applied to most databases, regardless of the components installed or the use of the particular database. These general guidelines are described in the following sections.

Check the documentation

This one may seem obvious, but it needs to be said: read the documentation. Even experienced DBAs will benefit from a quick read of the pertinent documentation before beginning an Oracle installation. We recommend that you look at the following (at least):

Check resource requirements

Before beginning an installation, be certain that sufficient system resources are available. The IUG for your platform contains comprehensive information about disk storage and memory requirements. Remember that these requirements are minimums, and that the resources required may actually be higher, depending on other configuration decisions you make. For example, more memory will be required if you specify a larger SGA.

In particular, make sure there is enough disk space available on the device where you place the Oracle software (typically called ORACLE_HOME ) to load all software and ancillary files.

Check system privileges

Most operating systems require the account that is performing the Oracle installation to have certain privileges. Be sure to check the IUG for these, and make sure the system administrator has set them properly. Note that these privileges may include the right to create directories and files on specific devices.

Determine control file locations

Oracle requires at least one control file. You should require at least two, and usually more, control files. This is critical because if all copies of the control file are lost, you will be unable to mount your database. Plan to place control files on different disk devices and, where possible, on different disk controllers.

SQL*Net Configuration

SQL*Net (Oracle7) and Net8 (Oracle8) must be configured, usually using Oracle Network Manager or the Net8 Assistant. This is typically done after the database software is installed and after at least one Oracle instance is up and running, but the configuration should be planned in advance. Before beginning a SQL*Net/Net8 configuration, you must know:

Once SQL*Net/Net8 is configured, the following files (at least) must be placed on each server:

listener.ora
Controls the operation of the SQL*Net listener process
tnsnames.ora
Maintains the relationship between logical node names (aliases) and physical locations in the network when the Oracle Names software is not used
sqlnet.ora
Controls logging of Oracle network operations (not required but highly desirable)

If you are using the Multi-Threaded Server, this fact must also be configured in the INIT.ORA file, as shown in the next section.

Multi-Threaded Server Configuration

The Multi-Threaded Server (MTS) is configured in the INIT.ORA file, as shown in the following sample INIT.ORA parameter settings:

mts_dispatchers="ipc,1"
mts_dispatchers="tcp,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=TEST
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=TEST))"
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(HOST=10.74.72.42)(PORT=1526))"

This example will configure a Multi-Threaded Server that will handle TCP/IP connections to the TEST database. A maximum of 10 dispatchers will be started, and up to 10 server processes will be created.

NOTE: Remember that each MTS process counts against the total count specified in the INIT.ORA parameter PROCESSES, as well as against the maximum processes allowed for the Oracle user at the operating system level.

Parallel Query Configuration

Parallel Query Option (PQO) is a powerful feature of Oracle, but in order to use it properly, the database must be configured properly. Parallel Query allows multiple CPU systems to divide certain database tasks (usually full table scans) into several pieces that can be executed at the same time (in parallel). In order to perform this task, the following are required:

Parallel Server Configuration

In order to utilize Oracle Parallel Server (OPS), which allows a single Oracle database to be shared by multiple Oracle instances, you must carefully specify the Parallel Server characteristics using INIT.ORA parameters on each participating instance, including:

PARALLEL_SERVER
Must be set to TRUE to enable the Oracle Parallel Server (Oracle8 only).
INSTANCE_NUMBER
Identifies the instance to the database.
ROLLBACK_SEGMENTS
Specifies the private rollback segments to be used by each instance. Public rollback segments can also be specified, but this is not necessary.
THREAD
Identifies the redo log thread to be associated with the instance.
GC_DB_LOCKS
The total number of instance locks (Oracle7 only).
GC_FILES_TO_LOCKS
The number of database file locks.
GC_LCK_PROCS
The total number of distributed locks.
GC_ROLLBACK_LOCKS
The total number of rollback locks.
GC_SAVE_ROLLBACK_LOCKS
The number of rollback save locks (Oracle7 only).
GC_SEGMENTS
The maximum number of segments that may have activities impacting space management performed on them simultaneously (Oracle7 only).
INSTANCE_GROUPS
Assigns the instance to one or more specified groups (Oracle8 only).
LM_LOCKS
The number of locks that will be configured for the lock manager (Oracle8 only).
LM_PROCS
The number of processes for the lock manager (Oracle8 only).
LM_RESS
The number of resources that can be locked by each lock manager instance (Oracle8 only).
OPS_ADMIN_GROUP
Assigns the instance to a group for monitoring (Oracle8 only).
PARALLEL_INSTANCE_GROUP
Identifies the parallel instance group to be used for spawning parallel query slaves (Oracle8 only).
ROW_LOCKING
Should be set to ALWAYS.
SERIALIZABLE
Should be set to FALSE (Oracle7 only).
SINGLE_PROCESS
Should be set to FALSE (Oracle7 only).

Additional information on these parameters can be found in Chapter 12, Initialization Parameters. Because Oracle Parallel Server is a very complex product, you should consult the Oracle Parallel Server Concepts and Administration Guide before attempting to configure a Parallel Server environment. Here are a few points to keep in mind when doing this configuration:

Sizing and Configuring Database Objects

Proper sizing and configuration of database objects are critical to achieving maximum database performance. Proper object sizing is an ongoing task; as objects are created and modified, you must continue to examine their characteristics and make changes when necessary. Some sizing-related problems that negatively impact performance are:

Tablespace fragmentation
This problem, which leaves many unusable small extents scattered about a tablespace, can result when objects are created with inappropriate INITIAL or NEXT extent sizes.
Row chaining
This problem, which causes the data from a single row to reside in multiple Oracle blocks, typically occurs when an insufficient PCTFREE setting is specified and updates subsequently occur to the table.
Multiple extents
Multiple extents, which may cause data for a particular object to be spread across one or more datafiles, result when objects are created with improper INITIAL or NEXT extent sizes. This problem may become critical when the MAXEXTENTS parameter is permitted to assume the default value, since an attempt to allocate an extent beyond that number will result in a failure.
Log waits
Log waits, which cause a process to wait while log buffer records are written to a log file or while a log file switch is occurring, can add significant processing time. These are usually caused by a combination of an insufficient number of log files and log files that are too small.
Failure to extend a rollback segment
Such failures, which can cause a transaction to roll back, are caused when not enough rollback segments are allocated, or when the rollback segments allocated are not large enough.

The following sections contain specific guidelines and suggestions that may help prevent some of these performance problems.

Tables

Tables are the basic units of data storage in an Oracle database, so their configuration and resulting performance will have a large impact on overall database performance. Some guidelines for table configuration are as follows:

Indexes

Perhaps no other single feature of Oracle can provide as much performance improvement as the proper use of indexes. While many performance gains will result from tuning SQL statements (see Chapter 8, Query Optimization), there are also several configuration guidelines we suggest you follow:

Rollback Segments

Rollback segments are used by Oracle to maintain data consistency and to allow transactions to be cancelled or rolled back. The use of rollback segments is fairly I/O intensive, and the following guidelines apply to their configuration:

Sort Areas

Oracle uses the INIT.ORA parameter SORT_AREA_SIZE to allocate memory for use in sorting data. When a sort cannot be completed in memory, Oracle uses temporary segments in the database, which is considerably slower. A careful balance is required for SORT_AREA_SIZE, since large sizes can dramatically increase performance by decreasing I/O, but will also use up memory and can result in paging.

NOTE: Remember that this parameter applies to each user process. Each user process performing a sort will have SORT_AREA_SIZE memory allocated. So, if SORT_AREA_SIZE is set to 1 megabyte, and 100 user processes are performing sorts, a total of 100 megabytes of memory may be allocated.

Temporary Tablespaces

When insufficient sort memory is allocated to the user process to perform a required sort, Oracle performs the sort on disk by creating temporary segments in the tablespace specified by the TEMPORARY TABLESPACE parameter for the user. In addition, temporary segments are used to perform complex queries like joins, UNIONs, and MINUSes, and for index creation. Guidelines for temporary areas include the following:

Redo Logs

Redo logs, also called online redo log files, are critical to Oracle's ability to recover from a failure. Proper configuration of redo logs is critical not only to overall database performance, but also to your ability to recover the database (see Chapter 4, Preventing Data Loss). Guidelines include the following:

Archive Log Destination

An often overlooked aspect of configuration is making certain there is enough space available in the archive log destination. If the database is running in archivelog mode when an online redo log file fills, Oracle's ARCH process copies the contents of that file to the directory specified in the INIT.ORA parameter ARCHIVE_LOG_DEST. If this destination is too small, ARCH is unable to copy the log file, and once all online log files are full, the entire database stops until the situation is resolved. Experienced DBAs will immediately recognize that this condition is most likely to occur in the middle of the night, just as REM sleep has begun!

Tuning Oracle

Perhaps no single aspect of the DBA's job consumes as much time as tuning. Successful Oracle tuning requires a blend of knowledge and experience, and can be both challenging and frustrating--often at the same time! Entire volumes have been written on Oracle tuning (see the appendix, Resources for the DBA), and we cannot hope to cover all aspects of tuning in a single section. Instead, as we mentioned earlier, we will outline for you an approach to tuning that can be applied to a variety of situations.

A Structured Tuning Approach

Successful tuning of an Oracle database requires a careful, disciplined approach. Like overall system configuration, tuning must address the following:

These should be addressed in sequence, since database performance tuning is not possible until the operating system and hardware have been well tuned, and an individual SQL statement cannot be properly tuned if Oracle is not running efficiently. When tuning any of these areas, there are three distinct steps in the process:

  1. Measure current performance.
  2. Make appropriate changes.
  3. Assess the result.
    4. NOTE: Some changes to the Oracle instance may result in the need for changes to the operating system environment. For example, allocating additional database buffers may cause the operating system to start paging, which may require additional operating system tuning to eliminate.

The tuning process is almost always an iterative one. That is, after completing the three steps outlined above, the DBA must return to step 1 and repeat the process. This continues until no additional performance gains are possible.

Oracle Instance Tuning

Most performance improvement at the Oracle instance level will be achieved by tuning two areas: memory utilization and disk I/O.

Memory utilization

It should come as no surprise that memory-based operations are much faster (sometimes by thousands of times) than disk operations. As a result, tremendous performance improvements may be achieved by replacing disk I/O with memory access of data. The three primary ways in which this can be done are described in the following list:

Allocate additional DB_BLOCK_BUFFERS
This is probably the single most effective method of improving overall performance, particularly on queries. Additional database buffers allow more data blocks to remain in memory, so the data contained in these blocks can be accessed at memory speed with no need for disk I/O. Buffers are allocated using the INIT.ORA parameter DB_BLOCK_BUFFERS, and the value is the number of database block buffers to be allocated. So, if the database block size is 8192, each DB_BLOCK_BUFFER will be 8192 bytes. Note that changes to DB_BLOCK_BUFFERS do not take effect until the next time the database is started.
NOTE: Be careful not to allocate so many DB_BLOCK_BUFFERS that the operating system begins to page; paging will eliminate any performance gain you may have achieved and will probably have an overall negative effect on performance.
Allocate additional shared pool
The shared pool size is controlled by the INIT.ORA parameter SHARED_POOL_SIZE, which specifies a shared pool size in bytes. The primary contents of the shared pool are the dictionary cache and the shared SQL area. Since the various components of the dictionary cache are automatically allocated by Oracle, any increase in the size of the shared pool results in additional memory for both the dictionary cache and the shared SQL area.

The shared SQL area contains copies of the most recently executed SQL statements, along with associated information like their execution plans. With a larger shared pool, it is more likely that a particular SQL statement has already been parsed and is resident in the shared SQL area, thereby saving the time required to reprocess the statement. This can be of particular value in a transaction processing system, where the same SQL statements are executed multiple times and where speed is a requirement.

Allocate additional log buffer space
The log buffer is used to hold data to be written to the online redo log file. The size of the log buffer is controlled by the INIT.ORA parameter LOG_BUFFER, and the value is expressed in bytes. By allocating additional memory to the log buffer, disk I/O will be reduced, especially when transactions are long or numerous.

Disk I/O

Disk access is the slowest operation on any computer system. As a database system, Oracle relies heavily on disk access for storage of and access to data. Consider a typical SQL statement that updates a row of a table. The following operations take place:

  1. The data dictionary is read to get information about the table and row being manipulated.
  2. The appropriate index is read to locate the row to be updated.
  3. The data block containing the row is read.
  4. Rollback information is written to a rollback segment.
  5. Update information is written to the online log file.
  6. The data block is rewritten.
  7. The index block is rewritten.

All these operations potentially require disk I/O, although some may be eliminated by efficient use of memory, as we described in the previous section. By making disk I/O as efficient as possible, overall performance will be enhanced. The basic guidelines for maximizing disk I/O are the following:

A note about RAID

Recent developments in disk technology have made RAID (Redundant Arrays of Inexpensive Disks) a popular option on many systems. Often, when the term RAID is used, hardware administrators immediately think of RAID level 5 (or RAID-5), which allows multiple disk devices to be combined to form one large device. By allocating one device for the storage of redundant data, a RAID-5 disk array is protected from the failure of any single disk in the array, and is often hot swappable, which means that a failing disk can be replaced even as the other drives continue to function, with no need to shut down the system.

RAID-5 is, in fact, very powerful and inexpensive. It is also a technology to be avoided in most cases when configuring your Oracle database! This may seem a harsh statement, but the reality is that although RAID-5 provides good levels of data protection at a low monetary cost, this comes at a very high cost for disk I/O. In particular, write operations on RAID-5 arrays can be orders of magnitude slower than the same operations on a single disk.

A good alternative to the RAID-5 array is the use of RAID level 0, commonly known as disk mirroring. Although more expensive than RAID-5 (one-half of the disks are used for storing redundant data), RAID-0 provides complete data protection with no sacrifice in I/O efficiency.

NOTE: RAID-0 requires sufficient hardware resources. In particular, since each write operation actually results in two writes to disk, the load on the controller is doubled compared to non-RAID.

The best RAID performance available today is called RAID-0+1, sometimes called RAID-10. This level of RAID combines mirrored disks (as in RAID-0) with striping of data across multiple drives, which can eliminate any delay while waiting for disk head positioning. While not available from all RAID controllers, RAID-0+1 is well worth considering.

Operating system striping

Many operating systems offer automatic striping of disk sectors across multiple devices. This striping permits disk I/O to continue sequentially without a delay for head positioning. While this technique provides better performance than that achieved on a single disk, it has a disadvantage: combining disks into a single striped unit means that the DBA is no longer able to control the location of individual files on separate devices. If you can only have a few large disk devices on your system, you should consider operating system striping, but multiple devices or multiple RAID-0+1 arrays will usually yield better performance from Oracle.

Oracle striping

As the DBA, you can achieve results similar to operating system striping by carefully allocating datafiles to individual devices or RAID-0+1 arrays. For example, to set up Oracle striping across four disks, do the following:

The Oracle striping technique is very powerful, especially when combined with Parallel Query, which will allow query processing by multiple CPUs.

SQL Tuning

Suppose that the host server and operating system are running smoothly at your site, and you have configured and tuned Oracle to run at the peak of perfection, but performance on your critical application is still poor. Unfortunately, this is not an uncommon occurrence. The solution is to tune the application by examining and tuning the SQL statements being executed.

SQL tuning is a subject that deserves a book of its own. In fact, there are several good books on the market that address tuning in much more detail than is available here. We urge you to check the sources listed in the appendix, Resources for the DBA. In this section, we'll offer some brief advice and guidelines for tuning your SQL statements.

Query processing

Chapter 8, Query Optimization, describes how Oracle creates a plan for a particular SQL statement. Oracle currently uses one of two methods for determining how to execute a SQL statement:

Rule-based method
Applies a standard, inflexible (but often efficient) set of rules to the statement
Cost-based method
Considers the available statistical information about the objects referenced by a SQL statement (along with available indexes) and creates a plan based on those statistics

The keys to tuning a SQL statement are understanding how the Oracle query optimizers work and knowing how to change Oracle's behavior so it will process the statement more efficiently.

Of course, before you can tune a SQL statement, you must know what it is doing and how. There are many tools on the market today that will help with this task, and one of the most useful (if not the flashiest) is the EXPLAIN PLAN command available in SQL*Plus. By creating a plan table (usually known as PLAN_TABLE) and examining the result of an EXPLAIN PLAN statement, you'll easily see how Oracle executes a particular statement. For example, the SQL statement:

SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

can be explained with the following command:

EXPLAIN PLAN SET STATEMENT_ID='DEMO' FOR
SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

The results stored in PLAN_TABLE can be selected using a simple query:

SELECT LPAD(' ',2*level) || operation || '' || options || ' '||
       object_name EXPLAIN_PLAN
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id=1

and will look like this:

EXPLAIN_PLAN
-------------------------------
NESTED LOOPS
  TABLE ACCESSFULL DEPT
  TABLE ACCESSFULL EMP

This plan shows that both the DEPT and EMP tables will be accessed using a full table scan. This is fine for two small tables like EMP and DEPT; in fact, we want them to be full table scans, because the tables will be cached in memory and no disk I/O will be required (after the first execution, at least). However, if the tables were large, this query could run for a long time, and so we would want to change the way this query is performed.

There are three basic ways to modify the behavior of Oracle's query optimizer:

If we try the first option and add an index on EMP(deptno), the plan will change as follows:

EXPLAIN_PLAN
---------------------------------------------
  NESTED LOOPS
    TABLE ACCESSFULL DEPT
    TABLE ACCESSBY ROWID EMP
      INDEXRANGE SCAN EMPDEPT_IX

You can now see that Oracle will use the index to retrieve rows from EMP via the ROWID, which was obtained from the newly created index, and a full table scan is no longer necessary.

There is often more than one way to perform a particular function using SQL, and it is good programming practice to try several methods (with appropriate benchmarking) before settling on the correct SQL statement to use. Chapter 8, Query Optimization, provides more detailed information on SQL tuning.

Other Useful Tuning Features

Oracle has continued to improve its database product by adding new features that help boost performance. It is important to check the Release Notes on even minor upgrades to Oracle, since new performance features are often included. Some of the features and facilities you might find useful are listed in this section.

Partitioned tables

Partitioned tables, which are available beginning with Oracle8, allow a table to be created across multiple subtables, each of which holds a particular subset of the table data. For example, a table could be partitioned by year, with all data from 1998 in one partition, all 1999 data in another, and so on. Partitioning is particularly useful for large tables, since queries involving only an identifiable subset of data can operate on the data in the appropriate partitions without accessing other partitions. For example, updating 1999 records would only require Oracle to perform I/O operations on the 1999 partition of the table. Partitioning is specified in the CREATE TABLE statement. In order to use this feature, you must:

Partitioned tables should usually be accompanied by a corresponding partitioned index, as follows:

Index-only tables

In some cases, all the data that would normally be stored in a table can be stored in an index, and the table is not necessary. An index-only table, available starting with Oracle8, keeps the data sorted according to the primary key column. There are some limitations to this type of object:

An index-only table is created by using the ORGANIZATION INDEX clause of the CREATE TABLE command.

Bitmap indexes

Bitmap indexes can yield greatly improved performance when the data being indexed has low cardinality--that is, if there are relatively few distinct values for the indexed column. An example of a good candidate for a bitmap index would be GENDER, which would have values of "M" or "F". A poor candidate for a bitmap index would be SALES_AMOUNT, which is likely to have a different value for almost every row.

Creating a bitmap index is similar to creating a standard index; you include the keyword BITMAP in the CREATE INDEX statement. For example, to create a bitmap index on the GENDER column of an EMPLOYEE_MASTER table, you'd specify the following statement:

CREATE BITMAP INDEX empmast_ix ON employee_master(gender);

Temporary tablespaces

Oracle7 introduced the concept of temporary tablespaces, which are used exclusively for Oracle's sort segments. By eliminating serialization of space management operations involved in the allocation and deallocation of sort space, all operations that use sorts can benefit from improved performance when sorts are too large to fit in memory. These performance gains are particularly significant when running Oracle Parallel Server.

NOTE: A temporary tablespace can be used only for sort segments; no permanent objects may be created in a temporary tablespace.

To create a temporary tablespace, use the keyword TEMPORARY in the CREATE TABLESPACE statement. For example, the following statement will create a temporary tablespace called TEMP:

CREATE TABLESPACE TEMP
DATAFILE '/disk99/oracle/oradata/TEST/temp01.dbf' SIZE 50M
DEFAULT STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED)
TEMPORARY;

An existing non-temporary tablespace may be converted to a temporary tablespace by using the SQL statement if it contains no permanent objects:

ALTER TABLESPACE tablespace TEMPORARY;

Unrecoverable operations

Beginning with Oracle 7.2, it has been possible to create a table or index without writing redo log records. This option provides better performance, since significantly less I/O is required. To take advantage of this feature, specify either UNRECOVERABLE (Oracle7 syntax) or NOLOGGING (Oracle8 syntax) in the object creation statement. For example, suppose that you are moving data from another database using a database link and that you use the statement:

INSERT INTO newtable
SELECT * from oldtable@oldlink;

This method would certainly work, but redo log records would be created for each insert, which could be costly. The same task could be accomplished with the following statement:

CREATE TABLE newtable AS
SELECT * from oldtable@oldlink
NOLOGGING;

The NOLOGGING option is particularly useful when rebuilding indexes. The inclusion of the NOLOGGING keyword can cut substantial time from index creation. The SQL statement would look similar to this:

CREATE INDEX indexname ON table(column)
NOLOGGING;

Note, however, that if you experience a system failure at some point after an unrecoverable statement has completed, you will be unable to recover the transactions using the roll forward mechanism. You must recognize that a system failure has occurred and rerun the statement.

1. We particularly recommend Mark Gurry and Peter Corrigan's Oracle Performance Tuning, Second Edition (O'Reilly & Associates, 1997).

oreilly.com Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
 International | About O'Reilly | Affiliated Companies | Privacy Policy

© 2001, O'Reilly & Associates, Inc.