by Robin Schumacher
Even though
database vendors continue to proclaim the fact that they have “self-healing”
databases, performance problems continue to plague many critical database
systems. Added complexity in the database engines and a reduced DBA staff has
added to the administrator’s burden of keeping databases up and running at a
rapid pace.
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.
