When troubleshooting performance problems, an Oracle DBA can employ a number of different techniques to uncover the root cause of database slowdowns. These methods include observing key ratios, using wait events, viewing performance details for heavy resource-hungry sessions, and tracking down inefficient SQL. The most in-vogue practices focus on bottlenecks (waits) and SQL interrogation, but smart DBAs also know when to put into play their favorite ratio and session scripts or monitors to fully expose a suspected performance problem.

I have to confess that when working through the database tuning process, I have often overlooked a subtle, but nasty threat to database performance - disk sorts. Excessive disk sort activity can make a wreck out of well-optimized SQL statements and give a black eye to even the most robust database server. How can this happen? More importantly, is your database suffering from disk sort problems? Let's take a look at this performance thief and see how to both find and fix disk sort activity on a database.

What is a Disk Sort?

All DBAs know that various operations and SQL statement executions can create sort activity in the database. For example, include one of Oracle's aggregation functions (MAX, MIN, etc.) in a SQL statement and you've introduced a sort operation to the database. A required sort is not necessarily a bad thing if it is efficiently performed. A short list of some of the most common SQL commands/clauses that can cause sorts include:

• CREATE INDEX, ALTER INDEX ... REBUILD
• DISTINCT
• ORDER BY
• GROUP BY
• UNION
• INTERSECT
• MINUS
• IN, NOT IN
• Aggregation functions (MAX, MIN, AVG, SUM)
• Certain unindexed joins
• Certain correlated subqueries

A sort can become problematic if it requires disk I/O for it to complete. When a sort operation occurs, Oracle attempts to perform the sort in a memory space that is assigned by the DBA. In Oracle versions below Oracle9i, this memory sort area was controlled by the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE initialization parameters. For Oracle9i and above, Oracle recommends the use of PGA_AGGREGATE_TARGET. If a sort is too large to be contained within this memory space, Oracle will continue the sort on disk - specifically, in the user's assigned temporary tablespace. This is where performance problems can begin to develop.

Most DBAs will tell you that they understand the impact of excessive physical I/O on their database and that they strive to keep it to a minimum. However, when they refer to physical I/O, they typically think about physical read activity needed to satisfy a SQL statement's request, but totally forget about disk sorts. They shouldn't. In my opinion, a disk sort can far outweigh the unwanted effects of regular disk I/O read activity because a disk sort involves both physical reads and physical writes. First, Oracle must perform physical writes to a session's temporary tablespace for the sort activity it cannot handle in memory. Then, the database must turn right around and read that information back from disk to build the result set requested by the SQL query. So in essence, it's a double whammy especially for large result sets that are the product of heavy sort requests.

Next

Back to DBAsupport.com