Oracle7 Server Tuning
Some applications cause Oracle to sort data. This section tells you
- how to allocate memory to optimize your sorts
- how to avoid sorting when creating indexes
Allocating Memory for Sort Areas
The default sort area size is adequate to hold all the data for most sorts. However, if your application often performs large sorts on data that does not fit into the sort area, then you may want to increase the sort area size. Large sorts can be caused by any SQL statement that performs a sort that operates on many rows. SQL statements that perform sorts are listed in Chapter 9, "Memory Structures and Processes", of Oracle7 Server Concepts.
Recognizing Large Sorts
Oracle collects statistics that reflect sort activity and stores them in the dynamic performance table V$SYSSTAT. By default, this table is only available to the user SYS and to users granted the SELECT ANY TABLE system privilege. These statistics reflect sort behavior:
Monitor these statistics over a period of time while your application is running with this query:
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts(memory)', 'sorts(disk)');
The output of this query might look like this:
NAME VALUE
------------------------------------------------------- ----------
sorts(memory) 965
sorts(disk) 8
The information in V$SYSSTAT can also be obtained through SNMP.
Increasing Sort Area Size
If a significant number of sorts require disk I/O to temporary segments, then your application's performance may benefit from increasing the size of the sort area. In this case, increase the value of the initialization parameter SORT_AREA_SIZE. The maximum value of this parameter varies depending on your operating system.
Performance Benefits of Large Sort Areas
Increasing the size of the sort area increases the size of each run and decreases the total number of runs. Reducing the total number of runs may reduce the number of merges Oracle must perform to obtain the final sorted result.
Performance Tradeoffs for Large Sort Areas
Increasing the size of the sort area causes each Oracle process that sorts to allocate more memory. This increase reduces the amount of memory available for private SQL and PL/SQL areas. It can also affect operating system memory allocation and may induce paging and swapping. Before increasing the size of the sort area, be sure enough free memory is available on your operating system to accommodate a larger sort area.
If you increase the size of your sort area, you may consider decreasing the retained size of the sort area, or the size to which Oracle reduces the sort area if its data is not expected to be referenced soon. To decrease the retained size of the sort area, decrease the value of the initialization parameter SORT_AREA_RETAINED_SIZE. A smaller retained sort area reduces memory usage but causes additional I/O to write and read data to and from temporary segments on disk.
Avoiding Sorts
One cause of sorting is the creation of indexes. Creating an index for a table involves sorting all the rows in the table based on the values of the indexed column or columns.
Oracle also allows you to create indexes without sorting. If the rows in the table are loaded in ascending order, you can create the index faster without sorting.
The NOSORT Option
To create an index without sorting, load the rows into the table in ascending order of the indexed column values. Your operating system may provide you with a sorting utility to sort the rows before you load them.
When you create the index, use the NOSORT option on the CREATE INDEX command. For example, this CREATE INDEX statement creates the index EMP_INDEX on the ENAME column of the EMP table without sorting the rows in the EMP table:
CREATE INDEX emp_index
ON emp(ename)
NOSORT;
Choosing When to Use the NOSORT Option
Presorting your data and loading it in order may not always be the fastest way to load a table. If you have a multiple-CPU computer, you may be able to load data faster using multiple processors in parallel, each processor loading a different portion of the data. To take advantage of parallel processing, load the data without sorting it first. Then create the index without the NOSORT option.
On the other hand, if you have a single-CPU computer, you should sort your data before loading, if possible. Then you should create the index with the NOSORT option.
SORT_DIRECT_
WRITES parameter
If memory and temporary space are abundant on your system, and you perform many large sorts to disk, you can set the initialization parameter SORT_DIRECT_WRITES to increase sort performance. When this parameter is set, each sort will allocate several large buffers in memory for direct disk I/O. You can set the initialization parameters SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE to control the number and size of these buffers. The sort will write an entire buffer for each I/O operation. The Oracle process performing the sort writes the sort data directly to the disk, bypassing the buffer cache.
Performance Tradeoffs of Direct Disk I/O for Sorts
Using sort direct writes causes each Oracle process that sorts to allocate SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE bytes of memory in addition to the memory already allocated for the sort area. Ensure that your operating system has enough free memory available to accommodate this increase. Also, sorts that use direct writes will tend to consume more temporary segment space on disk.
One way to avoid increasing memory usage is to decrease the sort area by the amount of memory allocated for direct writes. Note that reducing the sort area may increase the number of sorts to disk, which will decrease overall performance. A good rule of thumb is that the total memory allocated for direct write buffers should be less than one-tenth of the memory allocated for the sort area. If the minimum configuration of the direct write buffers is greater than one-tenth of your sort area, then you should not trade sort area for direct write buffers.