of Oracle sort operations
Oracle SQL does far
more than simply extract the row information from the tables, and the row
data can be sorted and summarized in a number of ways. By using Oracle SQL
in conjunction with SQL*Plus, row information can be sorted, transformed,
formatted, and displayed in a report format. In fact, SQL*Plus allows for
sophisticated reports to be generated.
professionals fail to realize that the execution plan for Oracle SQL will
often change when the data is sorted and transformed. To fully understand
how Oracle SQL execution is affected, let’s explore the different
methods for sorting data. Oracle provides several SQL directives that
cause sorting of row data.
Sadly, sorting is a
frequently overlooked aspect of Oracle tuning. In general, an Oracle
database will automatically perform sorting operations, and the SQL tuning
expert must understand all of the SQL directives that invoke Oracle
At the time a SQL*Plus
session is established with Oracle, a private sort area is allocated in
RAM memory for use by the session for sorting. If the connection is via a
non-MTS dedicated connection, a program global area (PGA) is allocated
according to the sort_area_size init.ora parameter. For connections via
the multithreaded server, sort space is allocated in the large_pool.
Unfortunately, until Oracle9i, the amount of memory used in sorting must
be the same for all sessions, and it is not possible to add additional
sort areas for tasks that require large sort operations.
Of course, sorts that
cannot fit into the sort_area_size will be paged out into the TEMP
tablespaces for a disk sort, and disk sorts are thousands of times slower
than memory sorts.
Disk sorts are
expensive for several reasons. First, they are extremely slow when
compared to in-memory sorts. Also, a disk sort consumes resources in the
temporary tablespace. Oracle must also allocate buffer pool blocks to hold
the blocks in the temporary tablespace. In-memory sorts are always
preferable to disk sorts, and disk sorts will surely slow down an
individual task, as well as impact concurrent tasks on the Oracle
instance. Also, excessive disk sorting will cause a high value for free
buffer waits, paging other tasks’ data blocks out of the buffer.
If you like Oracle
tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think
it is right to charge a fortune for books!) and you can buy it right now
at this link: