Part 1  |  Part 2

Introduction

The Oracle database currently dominates the market for database software. Through the release of Oracle8, Oracle8i, and Oracle9i, Oracle has evolved into one of the world's most sophisticated database solutions. The challenge for IT professionals is to ensure that they are able to leverage Oracle's powerful features to improve the productivity of their organizations. One of the most effective ways to do this is through Oracle tuning.

Over the past ten years Oracle has developed a mind-boggling array of tuning parameters and techniques, all designed to improve the performance of your Oracle database.

Oracle tuning is a phenomenally complex subject. Entire books have been written about the nuances of Oracle tuning. However, there are some general goals that every Oracle DBA should follow in order to improve the performance of their systems.

During this overview we will briefly cover the following Oracle topics:

• External tuning - We must remember that Oracle does not run in a vacuum. Here we will look at tuning the Oracle server for high performance.
• Row re-sequencing to reduce disk I/O - We must understand how reducing I/O is the most important goal of Oracle tuning.
• Oracle SQL tuning - Oracle SQL tuning is one of the most important areas of Oracle tuning, and it is not uncommon to dramatically improve the performance of an SQL statement by using a few simple SQL tuning rules.
• Tuning Oracle sorting - Sorting is a small of very important component of Oracle performance.
• Tuning Oracle contention - The setting for table and index parameters has a huge impact on UPDATE and INSERT performance.

We always start by tuning the Oracle external environment. No amount of Oracle tuning is going to help if the server has a shortage of RAM or CPU resources.

External performance issues

Oracle does not run in a vacuum. The performance of your Oracle database depends heavily on external considerations. These external considerations include:

• CPU - The amount of CPU cycles available can slow-down SQL. Whenever the run-queue exceeds the number of CPUs on your Oracle server, you are CPU bound.
• RAM memory - The amount of available RAM memory for Oracle can effect the performance of SQL, especially in the data buffers and in-memory sorts.
• Network - Large amounts of Net8 traffic contribute to slow SQL performance.

Many tuning beginners make the mistake of attempting to tune the Oracle database before ensuring that the external environment is not stressed. No amount of Oracle tuning is going to help the performance of the database when an external bottleneck exists.

There are two simple things to monitor when you are checking the external Oracle environment:

1. Run queue waits - When the number of run queue waits exceeds the number of CPUs on your server, the server is experiencing a CPU shortage. The remedy is to add additional CPUs on the server or turn off high processing components such as Oracle Parallel Query.
2. RAM page in's - When you see RAM page-in operations, your existing RAM memory has been exceeded, and memory pages are moving in from the swap space on disk. The remedy is to add more RAM, reduce the size of the Oracle SGAs, or turn on Oracle's multi-threaded server.

You can view server stats in a variety of ways using standard server tools such as vmstat, glance, top and sar. Your goal is to ensure that your database server always has enough CPU and RAM resources to manage the Oracle requests.

Next let's look at how Oracle row-resequencing can dramatically reduce disk I/O.

Row re-sequencing

As we noted, experienced Oracle DBAs know that I/O is the single greatest component of response time and regularly work to reduce I/O. Disk I/O is expensive because when Oracle retrieves a block from a data file on disk, the reading process must wait for the physical I/O operation to complete. Disk operations are about 10,000 times slower than a row's access in the data buffers. Consequently, anything you can do to minimize I/O-or reduce bottlenecks caused by contention for files on disk can greatly improve the performance of any Oracle database.

If response times are lagging in your high-transaction system, reducing disk I/O is the best way to bring about quick improvement. And when you access tables in a transaction system exclusively through range scans in primary-key indexes, reorganizing the tables with the CTAS method should be one of the first strategies you use to reduce I/O. By physically sequencing the rows in the same order as the primary-key index, this method can considerably speed up data retrieval.

Like disk load balancing, row resequencing is easy, inexpensive, and relatively quick. With both techniques in your DBA bag of tricks, you'll be well equipped to shorten response times-often dramatically-in high-I/O systems.

In high-volume online transaction processing (OLTP) environments in which data is accessed via a primary index, resequencing table rows so that contiguous blocks follow the same order as their primary index can actually reduce physical I/O and improve response time during index-driven table queries. This technique is useful only when the application selects multiple rows, when using index range scans, or if the application issues multiple requests for consecutive keys. Databases with random primary-key unique accesses won't benefit from row resequencing.

Let's explore how this works. Consider a SQL query that retrieves 100 rows using an index:

select
   salary
from
   employee
where
   last_name like 'B%';

This query will traverse the last_name_index, selecting each row to obtain the rows. This query will have at least 100 physical disk reads because the employee rows reside on different data blocks.

Now let's examine the same query where the rows are re-sequenced into the same order as the last_name_index. We see that the query can read all 100 employees with only three disk I/Os (one for the index, and two for the data blocks), resulting in a saving of over 97 block reads.

The degree to which resequencing improves performance depends on how far out of sequence the rows are when you begin and how many rows you will be accessing in sequence. You can find out how well a table's rows match the index's sequence key by looking at the dba_indexes and dba_tables views in the data dictionary.

In the dba_indexes view, we look at the clustering_factor column. If the clustering factor - an integer - roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.

The benefits of row resequencing cannot be underestimated. In large active tables with a large number of index scans, row resequencing can triple the performance of queries.

Once you have decided to re-sequence the rows in a table, you can use one of the following tools to reorganize the table.

• Copy the table using Oracle's Create Table As Select (CTAS) syntax
• Oracle9i in-place table reorganization tool

Next, let's take a look at SQL tuning.

SQL tuning

Oracle SQL tuning is a phenomenally complex subject, and entire books have been devoted to the nuances of Oracle SQL tuning. However there are some general guidelines that every Oracle DBA follows in order to improve the performance of their systems. The goals of SQL tuning are simple:

• Remove unnecessary large-table full table scans - Unnecessary full table scans cause a huge amount of unnecessary I/O, and can drag down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. If the query returns less and 40 percent of the table rows in an ordered table, or 7 percent of the rows in an unordered table), the query can be tuned to use an index in lieu of the full table scan. The most common tuning for unnecessary full table scans is adding indexes. Standard B-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full table scans. The decision about removing a full table scan should be based on a careful examination of the I/O costs of the index scan vs. the costs of the full table scan, factoring in the multiblock reads and possible parallel execution. In some cases an unnecessary full table scan can be forced to use an index by adding an index hint to the SQL statement.
• Cache small-table full table scans - In cases where a full table scan is the fastest access method, the tuning professional should ensure that a dedicated data buffer is available for the rows. In Oracle7 you can issue alter table xxx cache. In Oracle8 and beyond, the small table can be cached by forcing to into the KEEP pool.
• Verify optimal index usage - This is especially important for improving the speed of queries. Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index. This also includes the use of bitmapped and function-based indexes.
• Verify optimal JOIN techniques - Some queries will perform faster with NESTED LOOP joins, others with HASH joins, while other favor sort-merge joins.

These goals may seem deceptively simple, but these tasks comprise 90 percent of SQL tuning, and they don't require a through understanding of the internals of Oracle SQL. Let's begin with an overview of the Oracle SQL optimizers. Next, let's briefly explore Oracle sorting and see how sort operations affect performance.

Tuning Oracle Sorting Operations

As a small but very important component of SQL syntax, sorting is a frequently overlooked aspect of Oracle tuning. In general, an Oracle database will automatically perform sorting operations on row data as requested by a create index or an SQL ORDER BY or GROUP BY statement. In general, Oracle sorting occurs under the following circumstances:

• SQL using the ORDER BY clause
• SQL using the GROUP BY clause
• When an index is created
• When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

At the time a session is established with Oracle, a private sort area is allocated in RAM memory for use by the session for sorting. If the connection is via a dedicated connection a Program Global Area (PGA) is allocated according to the sort_area_size init.ora parameter. For connections via the multithreaded server, sort space is allocated in the large_pool. Unfortunately, the amount of memory used in sorting must be the same for all sessions, and it is not possible to add additional sort areas for tasks that require large sort operations. Therefore, the designer must strike a balance between allocating enough sort area to avoid disk sorts for the large sorting tasks, keeping in mind that the extra sort area will be allocated and not used by tasks that do not require intensive sorting. Of course, sorts that cannot fit into the sort_area_size will be paged out into the TEMP tablespaces for a disk sort. Disk sorts are about 14,000 times slower than memory sorts.

As we noted, the size of the private sort area is determined by the sort_area_size init.ora parameter. The size for each individual sort is specified by the sort_area_retained_size init.ora parameter. Whenever a sort cannot be completed within the assigned space, a disk sort is invoked using the temporary tablespace for the Oracle instance.

Disk sorts are expensive for several reasons. First, they are extremely slow when compared to an in-memory sort. Also, a disk sort consumes resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to hold the blocks in the temporary tablespace. In-memory sorts are always preferable to disk sorts, and disk sorts will surely slow down an individual task as well as impact concurrent tasks on the Oracle instance. Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer.

Next, let's take a quick look at Oracle contention and see how the table storage setting affect the performance of SQL UPDATE and INSERT statements.

Tuning Oracle Contention

One of the benefits of having Oracle is that it manages all of the free space within each tablespace. Oracle handles table and index space management for us and insulates us from the inner workings of the Oracle tables and indexes. However, experienced Oracle tuning professionals need to understand how Oracle manages table extents and free data blocks. This is a very important tuning consideration for systems that have high inserts or updates.

To be proficient at object tuning, you need to understand the behavior of freelists and freelist groups, and their relationship to the values of the pctfree and pctused parameters. This knowledge is especially imperative for enterprise resource planning (ERP) applications where poor DML table performance is often directly related to improper table settings.

The most common mistake for the beginner is assuming that the default Oracle parameters are optimal for all objects. Unless disk consumption is not a concern, you must consider the average row length and database block size when setting pctfree and pctused for a table such that empty blocks are efficiently placed back onto the freelists. When these settings are wrong, Oracle may populate freelists with "dead" blocks that do not have enough room to store a row, causing significant processing delays.

Freelists are critical to the effective reuse of space within the Oracle tablespaces and are directly related to the pctfree and pctused storage parameters. When the database is directed to make blocks available as soon as possible (with a high setting of pctused), the reuse of free space is maximized. However, there is a direct trade-off between high performance and efficient reuse of table blocks. When tuning Oracle tables and indexes, you need to consciously decide if you desire high performance or efficient space reuse, and set the table parameters accordingly. Let's take a close look at how these freelists affect the performance of Oracle.

Whenever a request is made to insert a row into a table, Oracle goes to a freelist to find a block with enough space to accept a row. As you may know, the freelist chain is kept in the first block of the table or index, and this block is known as the segment header. The sole purpose of the pctfree and pctused table allocation parameters is to control the movement of blocks to and from the freelists. While the freelist link and unlink operations are simple Oracle functions, the settings for freelist link (pctused) and unlink (pctfree) operations can have a dramatic impact on the performance of Oracle.

As you may know from DBA basics, the pctfree parameter governs freelist un-links. Setting pctfree=10 means that every block reserves 10 percent of the space for row expansion. The pctused parameter governs freelist re-links. Setting pctused=40 means that a block must become less than 40-percent full before being re-linked on the table freelist.

Many neophytes misunderstand what happens when a block is re-added to the freelist. Once a block is re-linked onto the freelist after a delete, it will remain on the freelist even when the space exceeds 60 percent. Only reaching pctfree will take the database block off of the freelist.

Summary of Table and Index Storage Parameter Rules

The following rules govern the settings for the storage parameters freelists, freelist groups, pctfree, and pctused. As you know, the value of pctused and pctfree can easily be changed at any time with the alter table command, and the observant DBA should be able to develop a methodology for deciding the optimal settings for these parameters.

There is a direct trade-off between effective space utilization and high performance, and the table storage parameters control this trade-off:

• For efficient space reuse - A high value for pctused will effectively reuse space on data blocks, but at the expense of additional I/O. A high pctused means that relatively full blocks are placed on the freelist. Hence, these blocks will be able to accept only a few rows before becoming full again, leading to more I/O.
• For high performance - A low value for pctused means that Oracle will not place a data block onto the freelist until it is nearly empty. The block will be able to accept many rows until it becomes full, thereby reducing I/O at insert time. Remember that it is always faster for Oracle to extend into new blocks than to reuse existing blocks. It takes fewer resources for Oracle to extend a table than to manage freelists.

Let's review the general guidelines for setting of object storage parameters:

• Always set pctused to allow enough room to accept a new row. We never want to have free blocks that do not have enough room to accept a row. If we do, this will cause a slowdown since Oracle will attempt to read five "dead" free blocks before extending the table to get an empty block.
• The presence of chained rows in a table means that pctfree is too low or that db_block_size is too small. In most cases within Oracle, RAW and LONG RAW columns make huge rows that exceed the maximum block size for Oracle, making chained rows unavoidable.
• If a table has simultaneous insert SQL processes, it needs to have simultaneous delete processes. Running a single purge job will place all of the free blocks on only one freelist, and none of the other freelists will contain any free blocks from the purge.
• The freelist parameter should be set to the high-water mark of updates to a table. For example, if the customer table has up to 20 end users performing insert operations at any time, the customer table should have freelists=20.

Note that the freelist groups parameter only applies to Oracle Parallel Server and Real Application Clusters. For these types of Oracle, the freelist groups should be set the number of Oracle Parallel Server instances that access the table.

Conclusion

In this brief paper there has not been enough room to completely cover all of the expert tips that relate to Oracle tuning. For more complete information, see my Oracle Press books:

Oracle High-performance SQL Tuning

Oracle9i High-performance Tuning with STATSPACK

--

Donald K. Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems. A former Adjunct Professor, Don Burleson has written 15 books, published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals and edits for Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Don's Web sites include DBA-Oracle, Remote-DBA, Oracle-training, remote support and remote DBA.