 |
|
Optimizing Sorting for
Oracle SQL Statements
Oracle Tips by
Burleson
|
As a small but very important component of SQL
execution, sorting is a frequently overlooked aspect of Oracle SQL
tuning. In general, an Oracle database will automatically perform
sorting operations on row data as requested by an order by or
group by clause, but there are other SQL clauses that also
cause sorting. Oracle sorting happens transparently to the user and
occurs under the following circumstances:
-
When SQL contains an order by clause
-
When SQL contains a group by clause
-
When SQL contains a select distinct clause
-
When an index is created
-
When SQL contains a union or minus
operation
-
When a sort merge join is invoked by the SQL optimizer
At the time a session is established with
Oracle, a private sort area is allocated in RAM for use by the session
for sorting. If the connection is via a 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 region allocated
by the large_pool_size initialization parameter.
Unfortunately, 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. Therefore, the designer must strike a balance between
allocating enough sort area to avoid disk sorts for the large sorting
tasks while not overloading the RAM demands on the database server.
You must also keep in mind that the extra sort area will be allocated
and not used by tasks that do not require intensive sorting. Of
course, sorts that cannot fit into the sort_area_size will be
paged out into the TEMP tablespace for a disk sort.
Disk sorts are about 14,000 times slower than
memory sorts. Also, a disk sort consumes resources in the TEMP
tablespace and may impact the performance of other concurrent SQL
sorts because Oracle must allocate buffer pool blocks to hold the
blocks in the TEMP tablespace.
In-memory sorts are always preferable to disk
sorts, and disk sorts will surely slow down an individual task and may
also 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 Oracle block buffer. In short, our
goal is to perform a sort only when it is required to service the
query, and to ensure that the settings for in-memory sorts and disk
sorts are optimal.
Let’s begin with a review of the Oracle initialization parameters that
affect sorting behavior.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
