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