[Laskey99] Section 3.6. Tuning Oracle

3.6.

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.

3.6.1. 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:

1. Measure current performance.

2. Make appropriate changes.

3. Assess the result.

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.

3.6.2. Oracle Instance Tuning

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

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

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.

3.6.2.2. 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:

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

3.6.2.3. 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 1, commonly known as disk mirroring. Although more expensive than RAID-5 (one-half of the disks are used for storing redundant data), RAID-1 provides complete data protection with no sacrifice in I/O efficiency.

RAID-1 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-1) with striping (RAID-1) 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.

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

3.6.2.5. 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:

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

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

3.6.3.1. Query processing

Chapter 8, 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:

• 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:

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, provides more detailed information on SQL tuning.

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

3.6.4.1. 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:

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

3.6.4.2. 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:

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

3.6.4.3. 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);

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

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;

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