Several init.ora parameters govern sorting operations. These include:
sort_area_size 655326 Size of in-memory sort work area
sort_area_retained_size 0 Size of in-memory sort work area
retained between fetch calls
sort_direct_writes AUTO Use direct write
sort_write_buffers 2 Number of sort direct write buffers
sort_write_buffer_size 32768 Size of each sort direct write buffer
sort_spacemap_size 512 Size of sort disk area space map
sort_read_fac 20 Multiblock read factor for sort
Displaying Parameter Values
You can display the current values of these parameters in SQL Plus with the show parameters sort command. Roll your mouse over the output shown below to see information about the two most important parameters:
When a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting.
Unfortunately, the amount of memory must be the same for all sessions and it is not possible to add additional sort areas for tasks that are sort intensive. Therefore, the designer must strike a balance between allocating enough sort area to sort the majority of requests while recognizing that some very large sorts will never be able to fit into the space allocated for sort_area_size.
The size of the private sort area is determined by the sort_area_size init.ora. The size for each individual sort is specified by the sort_area_retained_size init.ora parameter. Whenever a sort cannot be completed within the assigned space, a disk sort is invoked using the temporary tablespace for the Oracle instance.
As a rule, only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.
New sorting parameters
Legacy Oracle version 7.2 added several new parameters to the init.ora file for use in allocating a new in-memory sort area, including:
Defines the size of the in-memory sort area
Defines the number of buffer blocks
If set to TRUE will bypass the need for the sort to contend for free blocks in the buffer cache
Remember that the single most important factor in the performance of an Oracle database is the minimization of disk I/O.
Since sort to disk is I/O intensive, reducing disk sorts is a primary tuning goal. In the next lesson, you will learn how to avoid SQL programming that causes unnecessary sorts.