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.
Enable sort_direct_writes
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.
The default parameter
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:
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.
In the next lesson, you will learn about the importance of allocating temporary tablespace for sorting.
Oracle Sorting Concepts - Quiz
Before going on, click the Quiz link below to check your knowledge so far about Oracle sorts. Oracle Sorting Concepts - Quiz