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