Administration Guide

Sorting

Sorting is often required for a query, and the proper configuration of the sort heap areas can be crucial to the query's performance. Sorting is required when:

• No index exists to satisfy a requested ordering (for example a SELECT statement that uses the ORDER BY clause)
• An index exists but sorting would be more efficient than using the index
• Creating an index (if the indexsort configuration parameter is set to yes).

Different Types of Sorting

Sorting involves two steps:

1. A sort phase
2. Return of the results of the sort phase.

How the sort is handled within these two steps results in different categories or types by which we can describe the sort. When considering the sort phase, the sort can be categorized as "overflowed" or "non-overflowed". When considering the return of the results of the sort phase, the sort can be categorized as "piped" or "non-piped".

Overflowed and Non-Overflowed

If the information being sorted cannot fit entirely into the sort heap (a block of memory that is allocated each time a sort is performed) it overflows into temporary database tables. Sorts that do not overflow always perform better than those that do.

Piped and Non-Piped

If sorted information can return directly without requiring a temporary table to store a final, sorted list of data, it is referred to as a "piped sort". If the sorted information requires a temporary table to be returned, it is referred to as a "non-piped sort". A piped sort always performs better than a non-piped sort.

The following combinations of the two sort categories are possible:

• A non-overflowed, piped sort (the best performing)
• A non-overflowed, non-piped sort
• An overflowed, piped sort
• An overflowed, non-piped sort (the worst performing)

Tuning the Parameters that Affect Sorting

The following situations affect the performance of sorting:

• The settings for the following configuration parameters:

  "Sort Heap Size (sortheap)"

  Specifies the amount of memory to be used for each sort

  "Sort Heap Threshold (sheapthres)"

  Controls the total amount of memory for sorting available across the entire instance for all sorts.

• Statements that involve a large amount of sorting
• Missing indexes that could help avoid unnecessary sorting
• Application logic that does not minimize sorting
• Parallel sorting, which improves the performance of sorts but can only occur if the statement uses intra-partition parallelism (see "Enabling Parallel I/O").

Looking for Indicators of Sorting Performance Problems

To tell if you have an overall problem with sorting, look at the total CPU time spent sorting compared to the time spent on the whole application. The database system monitor can help (see "Using the Database System Monitor"). In particular, the Performance Monitor (know by "Snapshot Monitor" and "Event Monitor"), available from the Control Center on the Windows 95, Windows NT, and OS/2 operating systems, shows total sort time by default, along with other times such as I/O and lock wait.

If total sort time is a large proportion of the other times then look at the following values, which are also shown by default:

Percentage of overflowed sorts

This variable (on the performance details view of the Snapshot Monitor) shows the percentage of sorts that overflowed. If the percentage of overflowed sorts is high, increase the sortheap and/or sheapthres configuration parameters if there were any post-threshold sorts. (To determine if there were any post threshold sorts, use the Snapshot Monitor.)

Post threshold sorts

If post threshold sorts are high, increase sheapthres and/or decrease sortheap.

In general, make the overall sort memory available across the instance (sheapthres) as large as possible without causing excessive paging. It is possible for a sort to be done entirely in sort memory. However, if this causes the operating system to perform excessive page swapping to accommodate that sort memory you can lose the advantage of a large sort heap. So, whenever you adjust the sorting configuration parameters, use an operating system monitor to track any changes in system paging.

Also note that in a piped sort, the sort heap does not get freed until the application closes the cursor associated with that sort. So a piped sort can use up memory until the cursor is closed.

Techniques for Managing Sorting Performance

You can use the database system monitor and benchmarking techniques to help set the sortheap and sheapthres configuration parameters. Do the following for each database manager and its databases:

• Set up and run a representative workload.
• For each applicable database, collect average values for the following performance variables over the benchmark workload period:
  • Total sort heap in use
  • Active sorts

  These performance variables are shown on the performance details view of the Snapshot Monitor.

• Set sortheap to the average total sort heap in use for each database.
• Take the largest sortheap value for all databases in a database manager, and set sheapthres to the average number of active sorts multiplied by the average size of the sort heap. This is a recommended initial setting. You can then use benchmark techniques to refine this value.

You can also identify particular applications and statements where sorting is a significant performance problem:

• Set up event monitors at the application and statement level to help you identify applications with the longest total sort time.
• Within each of these applications, find the statements with the longest total sort time.
• Tune these statements using a tool such as Visual Explain.
• Ensure that appropriate indexes exist. You can use Visual Explain to identify all the sort operations for a given statement. Then investigate whether or not an appropriate index exists for each table accessed by the statement.

[ DB2 List of Books | Search the DB2 Books ]