Disk Sorts - A Subtle Threat to Database Performance - Page 3

How to Fix Disk Sort Problems

So, you've 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.

The next 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:

Notice the 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;

Then, we rerun our query and observe the following performance changes:

By 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).

If you're 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 improve performance?

First, 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 sorting.

You also 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.

Conclusion

Without a 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

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