alter session set sort_area_size = 102400; : sort_area_size « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. sort_area_size

SQL>
SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL>
SQL> alter session set hash_area_size = 1024;

Session altered.

SQL>
SQL> create table t as select * from all_objects where rownum < 50;

Table created.

SQL>
SQL> analyze table t compute statistics
  2  for table for columns object_id;

Table analyzed.

SQL>
SQL> set autotrace traceonly
SQL> alter session set sort_area_size = 102400;

Session altered.

SQL>
SQL> select *
  2    from t myTable1, t myTable2
  3   where myTable1.object_id = myTable2.object_id
  4  /

49 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    49 |  7448 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    49 |  7448 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |    49 |  3724 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |    49 |  3724 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MYTABLE1"."OBJECT_ID"="MYTABLE2"."OBJECT_ID")


Statistics
----------------------------------------------------------
        244  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
       4375  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

SQL>
SQL> drop table t;

Table dropped.