Sorting (Part 1)

The statement below joins DBA_TABLES to itself without a join condition. This forces Oracle to sort a large amount of data:

SQL> show user
USER is "ANDREW"
SQL> select a.* from dba_tables a, dba_tables b
  2  order by a.owner, a.table_name
  3  /

The statement will take some time to return any output. While it is executing, you can see the sort space usage increasing by querying V$SORT_USAGE every few seconds like this:

SQL> show user
USER is "SYSTEM"
SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

no rows selected

SQL> exec sys.dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

    BLOCKS
----------
      4480

SQL> exec sys.dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

    BLOCKS
----------
      8320

SQL> exec sys.dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

    BLOCKS
----------
     12160

SQL>
 
To get the values in bytes, simply multiply the figures by the DB_BLOCK_SIZE entry from V$PARAMETER.