Optimizing Sort Operations - Oracle Database 11gR2 Performance Tuning - page 202

In the third recipe, we will investigate what happens when we perform the top n queries,
queries which return the first n elements of a sorted set—and how to tune such statements.
In the fourth recipe, we will see the use of aggregate functions—with or without the group-by
clause, and some tips on improving them.
The fifth recipe is about set operations and their equivalent join and anti-join queries. The
last recipe is about troubleshooting temporary tablespace performance issues.
Sorting—in-memory and on-disk
In this recipe, we will see how to diagnose in-memory and on-disk sort, and the differences
between optimal, one-pass, and multi-pass sort.
Getting ready
We will use a SQL script from SQL*Plus environment to test in-memory and on-disk sort
(without displaying tons of data on the screen).
Open a text editor (for example, vi on UNIX systems or notepad for Windows) and save the
following script as 2602_05_TestSort.sql in a directory of your choice (the home directory,
for example):
CONNECT sh@TESTDB/sh
SET LINESIZE 120
SELECT * FROM v$sysstat WHERE name like '%sorts%';
-- Setting small sort area
ALTER SESSION SET WORKAREA_SIZE_POLICY = 'MANUAL';
ALTER SESSION SET SORT_AREA_SIZE = 1000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE = 1000;
SET TERMOUT OFF
SPOOL /dev/null
SELECT prod_id, cust_id, time_id FROM sales ORDER BY amount_sold desc;
SPOOL OFF
SET TERMOUT ON
SELECT * FROM v$sysstat WHERE name like '%sorts%';
-- Automatic sort area
ALTER SESSION SET WORKAREA_SIZE_POLICY = 'AUTO';
SET TERMOUT OFF
SPOOL /dev/null
SELECT prod_id, cust_id, time_id FROM sales ORDER BY amount_sold desc;
SPOOL OFF
SET TERMOUT ON
SELECT * FROM v$sysstat WHERE name like '%sorts%';