Enable sort_direct_writes

Setting the sort_direct_writes parameter has several performance benefits. When this value is set to TRUE, Oracle will bypass the buffer and write entire sort blocks (from the TEMP tablespace) directly to the disk. Oracle claims that this parameter can cause large disk sorts to run twice as quickly.

1. Oracle retrieves the rows from the database.
2. Oracle moves the rows into the TEMP tablespace for sorting
3. Oracle performs the sort without accessing the Oracle data buffer writing entire blocks
4. Oracle returns the sorted result set to the user

If memory and temporary space are abundant on your system, and you perform many large sorts to disk, you can set the init.ora parameter sort_direct_writes to increase sort performance. When sort_direct_writes is set to TRUE, each sort will allocate several large buffers in memory for direct disk I/O (in addition to the memory in sort_area_size). The Oracle process performing the sort writes the sort data directly to the disk, bypassing the buffer cache.
The default value of sort_direct_writes is AUTO. When the parameter is unspecified or set to AUTO, Oracle automatically allocates direct write buffers if the sort_area_size is at least ten times the minimum direct write buffer configuration. To illustrate, consider the following parameters:

SVRMGR> show parameter sort

NAME                                TYPE    VALUE
 ----------------------------------- ------- ------------------------------
 nls_sort                            string                                
 sort_area_retained_size             integer 0                             
 sort_area_size                      integer 65536                         
 sort_direct_writes                  string  AUTO                          
 sort_read_fac                       integer 5                             
 sort_spacemap_size                  integer 512                           
 sort_write_buffer_size              integer 32768                         
 sort_write_buffers                  integer 2

Minimum sort write configuration = sort_write_buffers * sort_write_buffer_size
Minimum sort write configuration = 2 * 32768
Minimum sort write configuration = 64K

Since sort_area_size is also 64K, this database will NOT use direct sorts until the sort_area_size is increased to at least 640K. Again, the minimum direct write configuration on most platforms is about 64K and direct writes will only be invoked for sorts that are greater than 500K. Small sorts will not use direct writes. In sum, if your database is performing large sorts, Oracle will automatically invoke direct sorting if you keep the default sort_direct_writes value to AUTO.