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...