Sorts

Oracle uses a Sort to perform a number of different functions:

MAX and MIN functions
ORDER BY clauses
GROUP BY clauses
DISTINCT / UNIQUE clauses
Sort-Merge joins
UNION, INTERSECT, and MINUS set operators
WHERE col IN uncorrelated sub-queries
CUBE clauses (an extra sort in addition to the GROUP BY)
OVER clauses (Analytic functions)

When Oracle performs a sort, all of the matching rows are written to a temporary area (the Sort Area), sorted, and then read back in to the next stage of execution. Small sorts will occur in memory, but a large sort will need to be written out to disk. Whilst the cost of sorting the rows is significant, the IO involved in the read and the write steps can really blow a query out.

If your Explain Plan contains a SORT, consider whether it really needs it.

Do you have a DISTINCT select when you are already selecting Unique / Primary Key columns, ROWID, or ROWNUM? The DISTINCT clause could be removed without affecting the rows returned (note that the rows would be returned in a different order).
Are you using a UNION? A UNION returns distinct rows from two or more SELECTS. If you can already guarantee uniqueness (or you don't care about uniqueness), change the UNION to a UNION ALL. UNION ALL does not remove duplicates, so it does not do a sort.

If your Explain Plan contains two or more sorts, can they be reduced to one or avoided altogether?

Using a MINUS set operator? MINUS sorts both SELECT queries before merging. If the columns you are selecting comprise a unique / primary key, or ROWID, then you may be able to rewrite it as a hash outer join and avoid the sort.

SELECT col1
FROM   table_a
WHERE  col5 > 20
MINUS
SELECT col1
FROM   table_b
WHERE  col2 LIKE 'XYZ%'

... becomes ...

SELECT a.col1
FROM   table_a a, table_b b
WHERE  a.col5 > 20
AND    b.col2 LIKE 'XYZ%'
AND    a.col1 = b.col1 (+)    -- outer join
AND    b.rowid IS NULL        -- return rows that filed the outer join

Using an INTERSECT set operator? If the columns you are selecting comprise a unique / primary key, or ROWID, then you may be able to rewrite it as a hash join and avoid the sort.

SELECT col1
FROM   table_a
WHERE  col5 > 20
INTERSECT
SELECT col1
FROM   table_b
WHERE  col2 LIKE 'XYZ%'

... becomes ...

SELECT a.col1
FROM   table_a a, table_b b
WHERE  a.col5 > 20
AND    b.col2 LIKE 'XYZ%'
AND    a.col1 = b.col1

Got a DISTINCT or GROUP BY plus an ORDER BY ... DESC? Normally not a problem; Oracle will sort only once. However if the ORDER BY contains a DESC condition for descending order sort, then the ORDER BY will be processed in a separate sort. Consider whether you can do without the DESC clause.

Got GROUP BY plus an ORDER BY? Make sure all of your non-aggregate columns in the SELECT clause are exactly the same in the GROUP BY clause. If not, Oracle will perform two separate sorts.

SELECT customer, discount_pct/100, sum(amt)
FROM sales
GROUP BY customer, discount_pct
ORDER BY 1,2

SELECT STATEMENT CHOOSE
    SORT ORDER BY
        SORT GROUP BY
            TABLE ACCESS sales

SELECT customer, discount_pct/100, sum(amt)
FROM sales
GROUP BY customer, discount_pct/100
ORDER BY 1,2

SELECT STATEMENT CHOOSE
    SORT GROUP BY
        TABLE ACCESS sales

Selecting from a view? If the view contains a sort (eg. DISTINCT, GROUP BY) and your query over the view also contains a sort, you may be sorting the same data twice. Check your Explain Plan. Perhaps you could just select from the base table of the view and perform just one sort?

CREATE VIEW sales_summary AS
SELECT customer, dept, sum(sales_amt) as total_sales
FROM sales
GROUP BY customer, dept;

SELECT customer, sum(total_sales)
FROM   sales_summary
GROUP BY customer;

SELECT STATEMENT CHOOSE
    SORT GROUP BY
        VIEW SALES SUMMARY
            SORT GROUP BY
                TABLE ACCESS SALES

SELECT customer, sum(total_sales)
FROM   sales
GROUP BY customer;

SELECT STATEMENT CHOOSE
    SORT GROUP BY
        TABLE ACCESS SALES