Oracle Database 11gR2 Performance Tuning Cookbook

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