by Robin Schumacher
How to Fix Disk Sort Problems
done some investigation and are horrified to find lots of disk sort activity on
your database. Is there anything you can do to make things better?
The first step is to try to eliminate any needless sort
activity regardless of whether it is done in memory or disk. For example,
UNION ALL does not cause a sort in a query whereas UNION does (to eliminate
duplicate rows). DISTINCT oftentimes is inappropriately coded and might
possibly be eliminated from certain queries.
step is to begin investigating the appropriate use of the Oracle initialization
parameters that affect sort operations. Before doing this, you might wonder if
setting such parameters can really make a difference. Let's take a look at an
Oracle 8.1.7 database that has only the default 65K SORT_AREA_SIZE parameter
set. We will run a query that causes a disk sort and examine its performance
metrics. When first run, its performance metrics look like this:
numbers for physical reads and physical writes. If you only reviewed the physical
reads statistic, you might think the query is performing physical read
activity for data to satisfy the query. However, when you also include the physical
writes statistic, you can then confirm the fact that the query is instead
involved in a disk sort and that is what is causing the physical I/O activity.
Of course, the sorts (disk) statistic definitively answers the question
about whether disk sorting is active for the query.
Since this is Oracle 8i, let's dynamically alter the
session to include a larger sort area size (1MB). We can do this by issuing
the following commands:
ALTER SESSION SET SORT_AREA_SIZE=1024000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=1024000;
rerun our query and observe the following performance changes:
enlarging the amount of memory available to the session for sort operations, we
have been able to kiss all physical I/O related to disk sorts goodbye, with the
end result being a reduction in overall response time of 75% (eight seconds
down to two).
using Oracle9i or above, then you will want to work with the new
PGA_AGGREGATE_TARGET initialization parameter that replaces all the pre-9i
parameters that were devoted to sorting. Nevertheless, whether you are working
with Oracle8i or 9i, take care and do not be overly generous with memory sort
areas as each session is assigned this amount, so for databases with large
numbers of users, memory can quickly become a rare commodity on servers that do
not have an abundance of RAM.
What if you
have increased the amount of memory devoted to sorting, but you still have disk
sorts occurring on your system? Keep in mind that for large databases,
sometimes disk sorts are unavoidable. In these cases, what can you do to help
ensure that you are intelligently managing your temporary tablespaces. This
equates to using true TEMPORARY tablespaces and assigning them to non-RAID5
disks on your server. Even if the hardware vendor states that no write penalty
is possible for their flavor of RAID5 (because of their caching, etc.), I would
still go for non-RAID5 for any write intensive files like those used for disk
want to place your temporary tablespaces on their own fast physical
drive/controller combination if at all possible. If you have a large user
community, consider creating multiple temporary tablespaces on different
devices and assigning half the users to one tablespace and the other half to
the other temporary tablespace to reduce contention. Finally, be sure you
monitor temporary tablespace activity to see if you have got things well
balanced on your server.
doubt, disk sorts are a near-silent performance vampire that can rob a database
of good response times. However, by intelligently diagnosing the presence of
disk sorts and then doing what you can to reduce or eliminate them altogether,
you can remove this subtle threat to your database's performance.
About the Author
Schumacher is vice-president of product management at Embarcadero Technologies,
Inc., a leading supplier of database software tools. Robin has numerous years
of experience with database engines and has been a feature writer and software
reviewer for many database-related publications. He is the author of a new
book entitled Oracle Performance Troubleshooting from Rampant Press and
can be emailed at Robin.Schumacher@embarcadero.com.