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.
- Oracle retrieves the rows from the database.
- Oracle moves the rows into the TEMP tablespace for sorting
- Oracle performs the sort without accessing the Oracle data buffer writing entire blocks
- 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
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
----------------------------------- ------- ------------------------------
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.