O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required