Maximizing Oracle Performance
Configuration and Tuning--What's the Difference?
Achieving Maximum Performance
Configuring the Operating System
Sizing and Configuring Database Objects
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 ), 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
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:
- Components of the database that affect the allocation of system processes, such as:
- Multi-Threaded Server (MTS)
- Parallel Query
- Parallel Server
- The layout and sizes of physical storage
- The sizing of database objects, such as:
- Rollback segments
- Sort areas
- Temporary tablespaces
- Redo logs
- Partition tables
- Index-only tables
- The amount and allocation of memory, such as:
- Database buffers
- Redo log buffers
- Shared pool
What Can Be Tuned?
Aspects of the Oracle database that can be tuned include the following:
- Memory utilization
- Disk utilization
- SQL statement execution
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:
- Operating system configuration
- Oracle resource configuration
- Object creation and SQL execution
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:
- Physical memory should be fully utilized, but swapping (in environments that swap memory) should not occur. Swapping memory to disk is a very slow process, so if your system needs more memory, buy more memory! In particular, make certain that you do not create a System Global Area (SGA) that is too big for physical memory, since swapping of the SGA will severely degrade Oracle performance.
- The CPU should approach 100% utilization at peaks, but processes should not have to wait for the CPU.
- Disks and controllers should be running at or near their optimal capacities (usually 60 to 90% of maximum) with minimal I/O waits. As the DBA, you do have some control over this area as well, as we'll describe later in this chapter.
- Network throughput should not be a bottleneck. Consider backbone networks to link servers to each other, and separate client/server traffic from server/server traffic where possible.
- Try to keep your Oracle server machine a pure server, and move users to another machine.
- Be sure any operating system components (including patches) that may affect Oracle are installed.
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!
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.
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):
- Hardware-specific Installation and User's Guide (IUG)
- Server Administrator's Guide
- Release Notes (usually packed with the media)
- The README file, which is usually found on the installation media and contains last-minute information that may not be in the printed documentation.
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 (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:
- The types of network protocols that will be used to access Oracle in your environment
- The naming scheme you will use to identify Oracle network nodes
- The names and network locations of all servers, gateways, and MultiProtocol Interchanges in your environment
Once SQL*Net/Net8 is configured, the following files (at least) must be placed on each server:
- Controls the operation of the SQL*Net listener process
- Maintains the relationship between logical node names (aliases) and physical locations in the network when the Oracle Names software is not used
- 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:
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:
- Multiple parallel processes must be permitted by setting the INIT.ORA parameter PARALLEL_MAX_SERVERS to a value greater than 0.
- Tablespaces must be created using multiple datafiles, which should be allocated to separate devices. Ideally, there will be as many devices allocated to each tablespace as there are CPUs in the system.
- Tables taking advantage of Parallel Query should have their degree of parallelism set (using the PARALLEL clause in the CREATE TABLE statement) to the number of datafiles comprising the tablespace in which the table is created.
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:
- Must be set to TRUE to enable the Oracle Parallel Server (Oracle8 only).
- Identifies the instance to the database.
- Specifies the private rollback segments to be used by each instance. Public rollback segments can also be specified, but this is not necessary.
- Identifies the redo log thread to be associated with the instance.
- The total number of instance locks (Oracle7 only).
- The number of database file locks.
- The total number of distributed locks.
- The total number of rollback locks.
- The number of rollback save locks (Oracle7 only).
- The maximum number of segments that may have activities impacting space management performed on them simultaneously (Oracle7 only).
- Assigns the instance to one or more specified groups (Oracle8 only).
- The number of locks that will be configured for the lock manager (Oracle8 only).
- The number of processes for the lock manager (Oracle8 only).
- The number of resources that can be locked by each lock manager instance (Oracle8 only).
- Assigns the instance to a group for monitoring (Oracle8 only).
- Identifies the parallel instance group to be used for spawning parallel query slaves (Oracle8 only).
- Should be set to ALWAYS.
- Should be set to FALSE (Oracle7 only).
- 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:
- On Unix platforms, all datafiles must be created in raw partitions.
- When creating a database, only redo thread 1 is created automatically; additional threads must be explicitly created, and you must specify which thread a redo log belongs to.
- Although not required, ensuring that the instance number and thread number are the same will avoid confusion.NOTE: The terms "Parallel Query" and "Parallel Server" are often confused. Parallel Query refers to the ability of a single Oracle instance to divide an operation (for example, a full table scan) across multiple CPUs on the same host computer and merge the completed results. Parallel Server, on the other hand, is a feature whereby multiple Oracle instances on different host machines share a single physical database. In this case, work is divided across Oracle instances either by distributing users across multiple instances, or by spawning parallel query processes across instances.
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 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:
- Try to estimate how big a table will be and allocate a sufficiently large INITIAL extent to hold the entire table. However, if you are using Parallel Query, allocate the total space across as many extents in different datafiles as the degree of parallelism for the table.
- Consider using multiple tablespaces for tables, each for a different size or type of table. For example, you might have three tablespaces: LARGE_DATA, MEDIUM_DATA, and SMALL_DATA, each of which would be used to hold tables of a particular size. If you are using multiple tablespaces for tables, be sure to allocate each table in the appropriate tablespace.
- Be sure to assign a DEFAULT TABLESPACE to each user. If one is not assigned, Oracle will use the SYSTEM tablespace by default.
- If possible, always allocate INITIAL and NEXT extents in multiples of the same size units; for example, allocate in multiples of 512K. This way, extents will be of uniform size and it will be easier to allocate additional extents without fragmenting the tablespace. Where possible, consider making all extents in a tablespace the same size.
- Set the PCTINCREASE parameter to 0, in order to prevent runaway extent allocation and to preserve uniform extent sizes.
- Set MAXEXTENTS to UNLIMITED. This will prevent running out of extents, since multiple extents have little performance impact in and of themselves (although widely scattered extents can negatively affect performance). Do this to prevent errors, but do not use it as a substitute for proper INITIAL sizing.
- Set PCTFREE to 0 if no updates will be performed on the table. If updates will be performed, try to estimate the degree to which columns of a row will grow, and allocate a PCTFREE that will prevent block chaining without excessive unused space in the block.
- Set INITRANS to a value greater than 1 (the default) if multiple transactions will access the table simultaneously.
- Set MAXTRANS to the maximum number of simultaneous accesses expected on the table. A lower value will result in one or more transactions waiting for a prior transaction to complete.
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:
- Create a separate tablespace for indexes, and make certain that the datafiles for this index tablespace are not on the same disk device as any datafiles for tablespaces that contain indexed tables.
- Try to estimate the size of an index and allocate a sufficient INITIAL extent to hold the entire index, unless you are using Parallel Query, in which case you should allocate the total space across as many datafiles as the degree of parallelism for the index.
- If possible, always allocate INITIAL and NEXT extents in multiples of the same size units; for example, allocate in multiples of 512K. This way, extents will be of uniform size and it will be easier to allocate additional extents without fragmenting the tablespace.
- Set PCTINCREASE to 0 in order to prevent runaway extent allocation and to preserve uniform extent sizes.
- Set MAXEXTENTS to UNLIMITED. This guideline will prevent your running out of extents, since multiple extents have little performance impact in and of themselves (although widely scattered extents can negatively affect performance). Do this to prevent errors, but do not use it as a substitute for proper INITIAL sizing.
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:
- Create a separate tablespace for rollback segments and, if possible, place the datafiles for this tablespace on a different disk device from other Oracle datafiles.
- Never create rollback segments in the SYSTEM tablespace (except for the temporary rollback segment required during database creation; see Chapter 2, Installation).
- Be sure that there is enough space allocated to your rollback tablespace to allow rollback segments to grow as large as necessary to accommodate large update transactions. Remember that batch transactions tend to be large.
- Always use the same value for the INITIAL and NEXT extents for rollback segments (define them in the DEFAULT STORAGE clause of the CREATE TABLESPACE statement). This guideline will prevent space fragmentation by allocating rollback segment space in equal-size chunks.
- Remember that each rollback segment must have at least two extents, so the initial size of a segment will actually be the sum of INITIAL + NEXT.
- Define an OPTIMAL value so that rollback segments that are required to grow in size to accommodate a large transaction can be shrunk to a more reasonable size. Don't make this size too small, however, or time will be wasted allocating additional extents to your rollback segments.
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.
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:
- Create a separate tablespace (usually called TEMP) for temporary segments, and place the datafile(s) for this tablespace on a separate disk device, if possible.
- Specify INITIAL and NEXT parameters in the DEFAULT STORAGE clause of the CREATE TABLESPACE command. Use the same value for both in order to eliminate space fragmentation, which is particularly likely in the TEMP tablespace, where objects are constantly being created and dropped.
- Be certain to specify a TEMPORARY TABLESPACE for each user. If one is not specified, Oracle defaults to SYSTEM, which is almost guaranteed to have a negative impact on performance.
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:
- Use Oracle's built-in mirroring capability and put multiple sets of redo log files on different disk devices.
- Allocate enough redo log files so that Oracle does not have to wait for a file to complete archiving before it is reused. Oracle requires at least two redo log files, but four or more may be necessary.
- Allocate redo log files that are large enough to prevent too many log switches, but small enough to support good recovery if the current online log file is lost in a failure. With smaller files, you will probably be able to recover all transactions that have been archived, whereas a large log file size exposes the database to the potential for more lost transactions.
- Set the INIT.ORA parameter LOG_CHECKPOINT_INTERVAL to a value larger than the size of your redo log files. This will prevent checkpoints until the log file is full (which forces a checkpoint). This parameter is expressed in database blocks.NOTE: Remember that a log switch causes dirty (i.e., updated) buffers to be written to disk from the SGA.
- If you are running Oracle7, consider setting the INIT.ORA parameter CHECKPOINT_PROCESS to TRUE. Doing so creates a separate process that performs the checkpoint, rather than the LGWR (Log Writer) process. See Chapter 10, The Oracle Instance, for more information.
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!
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:
- Hardware and operating system performance
- Oracle instance performance
- Individual transaction (SQL) performance
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:
- Measure current performance.
- Make appropriate changes.
- Assess the result.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.
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 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:
- The data dictionary is read to get information about the table and row being manipulated.
- The appropriate index is read to locate the row to be updated.
- The data block containing the row is read.
- Rollback information is written to a rollback segment.
- Update information is written to the online log file.
- The data block is rewritten.
- 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:
- Segregate I/O operations to separate disks wherever possible. In this way, there is no need to wait for one disk operation to finish before another is performed. For example, if both the rollback segment and the log file were on the same disk, the rollback record would be written; then the disk head would need to be moved to another part of the disk where the log file record would be written. This would be very time-consuming.
- Place high-I/O-volume disks on different controllers. Most modern controllers can handle a limited number of concurrent operations, but using as many controllers as possible will eliminate any controller waits and will speed performance.
- Place busy files and tablespaces (e.g., log files, rollback segments, some indexes) on the fastest available disks.
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.
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:
- Create a tablespace with four datafiles, each located on a different device.
- Create objects in the tablespace, specifying MINEXTENTS 4. Oracle will allocate the four extents on the four datafiles, thereby implementing striping. This action is not automatic; it can be accomplished by using the ALTER TABLE ... ALOCATE EXTENT command.
The Oracle striping technique is very powerful, especially when combined with Parallel Query, which will allow query processing by multiple CPUs.
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.
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:
FROM scott.emp, scott.dept
can be explained with the following command:
EXPLAIN PLAN SET STATEMENT_ID='DEMO' FOR
FROM scott.emp, scott.dept
The results stored in PLAN_TABLE can be selected using a simple query:
SELECT LPAD(' ',2*level) || operation || '' || options || ' '||
CONNECT BY PRIOR id = parent_id
START WITH id=1
and will look like this:
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:
- Provide one or more indexes to be used in executing the query.
- Rewrite the SQL to use a more efficient method.
- Provide direction to the query optimizer in the form of hints.
If we try the first option and add an index on EMP(deptno), the plan will change as follows:
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, 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:
- Identify the data field that will define the partition (for example, sales_year).
- Specify the ranges of values in the CREATE TABLE ... PARTITION BY RANGE clause.
- Specify a different tablespace (for best performance, place each on a separate disk) for each partition of the table. Note that separate tablespaces are not required, but this practice allows a partition of the table to be taken offline while maintaining access to the balance of the table.
Partitioned tables should usually be accompanied by a corresponding partitioned index, as follows:
- Use the LOCAL keyword in the CREATE INDEX command to tell Oracle to create a separate index for each partition of the indexed table.
- Use the GLOBAL keyword in the CREATE INDEX command to tell Oracle to create a single index using values that may not correspond to the partitioning of the indexed tables. GLOBAL indexes may also be partitioned.
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:
- Since the data is not stored in a table, there are no ROWIDs available.
- A primary key must be defined for the table.
- No additional indexes can be created; only the primary key may be indexed.
An index-only table is created by using the ORGANIZATION INDEX clause of the CREATE TABLE command.
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);
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)
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;
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
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)
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.