Sorting with 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.

In Oracle 11g R2 and later (as of 2020), the 1) minimum direct write configuration and 2) direct writes for sorting operations depend on the PGA_AGGREGATE_TARGET^[1] parameter and the SORT_AREA_SIZE^[2] setting. However, Oracle generally no longer uses `SORT_AREA_SIZE` explicitly unless the database is configured to use manual PGA memory management.
Here is how the behavior evolves in Oracle 11g R2 and later:

1. Minimum Direct Write Configuration (2020 and beyond):
   Oracle 11g R2 uses automatic PGA memory management by default, where PGA_AGGREGATE_TARGET governs memory allocation for sorting and hash joins. Direct write behavior is now influenced by the work area size policy, which is set to AUTO by default.
   • The threshold for direct writes is dynamically determined based on the estimated sort size and the available PGA memory.
   • The effective direct write threshold is typically larger than in older versions and may start around 1 MB or more for sorts, depending on the platform and workload.
2. Direct Writes for Large Sorts:
   Oracle starts using direct writes when the estimated size of a sort operation exceeds the memory threshold for in-memory sorting. In the default automatic PGA memory management, this threshold is dynamically calculated but generally aligns with the following:
   • Sorts exceeding 1 MB to 2 MB (typical range for Oracle 11g R2 on most platforms) are eligible for direct writes.
   • Small sorts (< 1 MB) are processed entirely in memory.
3. Manual Configuration (if using SORT_AREA_SIZE):
   If manual PGA management is used (by disabling PGA_AGGREGATE_TARGET), the SORT_AREA_SIZE parameter controls the direct write threshold:
   • Direct writes occur when SORT_AREA_SIZE is explicitly set to values larger than approximately 1 MB to 2 MB.
   • The minimum direct write configuration for many platforms in Oracle 11g R2 is approximately 1 MB, significantly larger than the 64 KB threshold from the year 2000.

Key Takeaways:

• For Oracle 11g R2 and later, direct writes are typically triggered for sort operations exceeding 1 MB to 2 MB, depending on the system's configuration and memory management settings.
• The minimum configuration for direct writes has increased significantly from the 64 KB threshold seen in older versions, reflecting changes in hardware capabilities and Oracle's adaptive memory management mechanisms.

In Oracle 11g and later, the behavior and relevance of the `SORT_DIRECT_WRITES` parameter have evolved due to changes in Oracle's memory management architecture. Let’s break this down:

1. Parameter Behavior in Oracle 11g and Later:
   • The SORT_DIRECT_WRITES parameter is still valid but is seldom used directly by DBAs because of improvements in the way Oracle manages sorting and memory.
   • In Oracle 11g, Oracle introduced Automatic Memory Management (AMM) and improved PGA (Program Global Area) Automatic Memory Management. This significantly reduced the need to explicitly configure parameters like SORT_DIRECT_WRITES.
2. Default Behavior:
   • The default value for SORT_DIRECT_WRITES remains AUTO. This means Oracle decides whether to use direct write buffers automatically based on the size of the sort and available memory resources.
   • The specific heuristic of needing the SORT_AREA_SIZE to be at least ten times the minimum direct write buffer size is no longer explicitly documented for Oracle 11g and later.
3. Impact of SORT_AREA_SIZE:
   • In Oracle 11g and later, the PGA_AGGREGATE_TARGET setting takes precedence over SORT_AREA_SIZE for managing memory allocated to sorts.
   • If PGA_AGGREGATE_TARGET is enabled, Oracle dynamically allocates memory for sorting operations, and the SORT_AREA_SIZE parameter serves only as a guideline for individual sessions, not a strict limit.
   • The SORT_AREA_SIZE parameter’s influence on direct writes is diminished under PGA management, as Oracle dynamically manages sorting memory more efficiently.
4. Practical Relevance:
   • Most environments in Oracle 11g and later rely on automatic memory management rather than tuning specific parameters like SORT_DIRECT_WRITES or SORT_AREA_SIZE.
   • Direct writes for sorting are still automatically enabled as needed, but the dependency on the specific ratio of SORT_AREA_SIZE to buffer size from Oracle 8i is no longer a focus.
5. Recommendations for Oracle 11g and Later:
   • Use PGA_AGGREGATE_TARGET to manage sort memory efficiently.
   • Avoid manual tuning of SORT_DIRECT_WRITES unless you have a specific use case requiring low-level memory management.
   • Use tools like the Automatic Workload Repository (AWR) and SQL Tuning Advisor to identify sort-related performance bottlenecks.

Conclusion: The principle that Oracle automatically allocates direct write buffers when `SORT_DIRECT_WRITES` is set to `AUTO` still holds true in Oracle 11g and later. However, the reliance on the `SORT_AREA_SIZE` to determine this behavior is largely obsolete in modern Oracle versions due to the introduction of automatic memory management.

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:

SQL> 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.
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 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.

Before going on, click the Quiz link below to check your knowledge so far about Oracle sorts.
Oracle Sorting Concepts - Quiz

[1]PGA_AGGREGATE_TARGET: PGA_AGGREGATE_TARGET is a parameter in Oracle databases that defines the total amount of memory allocated for the Program Global Area (PGA). The PGA is a memory region that stores data and control information for each server process connected to the database, and PGA_AGGREGATE_TARGET helps manage the overall PGA memory usage.

[2]SORT_AREA_SIZE: SORT_AREA_SIZE is an Oracle parameter that defines the maximum amount of memory a session can use for sorting data. This parameter is important for tuning because sorts are common operations in databases and can be resource-intensive. By adjusting SORT_AREA_SIZE, you can influence how much memory is allocated for sorts, potentially improving the performance of queries that involve ordering data.