Remote DBA Plans
Remote DBA Service

Removing Sorts by Adding Indexes

Oracle Tips by Burleson Consulting

One of the best ways to remove full-table scans and unnecessary sorts is by adding indexes. When operating in first_rows mode, Oracle will always try to resolve an order by clause by using an index in lieu of a sort operation. However, you must always remember that while an index will retrieve the rows far faster than a sort, the index scan will have slower overall throughput than the sort operation. We will go into greater detail on this topic in Chapter 20.

Unnecessary Sorts

There are many cases when Oracle performs a sort operation even though a sort is not required. This generally happens when one of the following conditions is present:

Missing index Many Remote DBAs are not aware that a column index is required for a query until they begin SQL tuning.
Sort merge join Anytime a sort merge join is requested, a sort will be performed to join the key values. In many cases, a nested loop join is a better choice because it is more efficient and does not require sorting or full-table scans.
Using the distinct clause Using the distinct clause on a query will always invoke a sort to remove the duplicate rows. There are many documented cases of SQL that have a distinct clause even though there can never be duplicate rows in the result set.

Once we have ensured that a sort is legitimate and cannot be removed with an index, we can then turn our attention to monitoring the overall sorting activity on our instance.

Monitoring Sorting Activity

The STATSPACK utility provides a great historical reference to sorting activity in Oracle. Upon increasing sort_area_size, you can track the reduction in disk sorts over time. The following STATSPACK query uses the stats$sysstat table. From this table we can get an accurate picture of memory and disk sorts.

rpt_sorts_alert.sql

set pages 9999;

column mydate heading 'Yr. Mo Dy Hr.' format a16
column sorts_memory format 999,999,999
column sorts_disk    format 999,999,999
column ratio         format .99999

select
   to_char(snap_time,'yyyy-mm-dd HH24')                mydate,
   newmem.value-oldmem.value                           sorts_memory,
   newdsk.value-olddsk.value                           sorts_disk,
   ((newdsk.value-olddsk.value)/(newmem.value-oldmem.value)) ratio
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot    sn
where
   newdsk.snap_id = sn.snap_id
and
   olddsk.snap_id = sn.snap_id-1
and
   newmem.snap_id = sn.snap_id
and
   oldmem.snap_id = sn.snap_id-1
and
   oldmem.name = 'sorts (memory)'
and
   newmem.name = 'sorts (memory)'
and
   olddsk.name = 'sorts (disk)'
and
   newdsk.name = 'sorts (disk)'
and
   newmem.value-oldmem.value > 0
and
   newdsk.value-olddsk.value > 100
;

Note that this query restricts output to those cases where there are more than 100 sorts. This restriction can be used to make the script behave as an alert mechanism to let you know when the number of sorts has been exceeded. Here is the output from the script. Here you can clearly see the numbers of memory sorts and disk sorts as well as the ratio of disk to memory sorts:

Yr. Mo Dy Hr. SORTS_MEMORY   SORTS_DISK   RATIO
---------------- ------------ ------------ -------
2000-12-20 12          13,166          166 .01261
2000-12-20 16          25,694          223 .00868
2000-12-21 10          99,183          215 .00217
2000-12-21 15          13,662          130 .00952
2000-12-21 16          17,004          192 .01129
2000-12-22 10          18,900          141 .00746
2000-12-22 11          19,487          131 .00672
2000-12-26 12          12,502          147 .01176
2000-12-27 13          20,338          118 .00580
2000-12-27 18          11,032          119 .01079
2000-12-28 16          16,514          205 .01241
2000-12-29 10          17,327          242 .01397
2000-12-29 16          50,874          167 .00328
2001-01-02 08          15,574          108 .00693
2001-01-02 10          39,052          136 .00348
2001-01-03 11          13,193         153 .01160
2001-01-03 13          19,901          104 .00523
2001-01-03 15          19,929          130 .00652

This report can be changed to send an alert when the number of disk sorts exceeds a predefined threshold, and we can also modify it to plot average sorts by hour of the day and day of the week.

Some Remote DBAs will locate periods of high sorting activity and then go to the stats$sql_summary table and extract the SQL for that snapshot for detailed analysis. Next, let�s look at how we can locate repeating periods of high disk sorting activity.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.