alter session set sort_area_size = 102400000 : Session Variables « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

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

SQL> create table myTable
  2  as
  3  select rownum id, a.*
  4    from all_objects a
  5   where 1=0
  6  /
SQL>
SQL>
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> alter session set sort_area_size = 102400000;
SQL>
SQL> alter session set hash_area_size = 204800000;
SQL>
SQL> select a.object_type, b.object_name
  2    from myTable a, myTable b
  3   where a.last_ddl_time = b.last_ddl_time;

Execution Plan
----------------------------------------------------------
Plan hash value: 2666611345

--------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    67 |  3082 |     3 |
|*  1 |  HASH JOIN         |         |    67 |  3082 |     3 |
|   2 |   TABLE ACCESS FULL| MYTABLE |    82 |  1640 |     1 |
|   3 |   TABLE ACCESS FULL| MYTABLE |    82 |  2132 |     1 |
--------------------------------------------------------------

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

   1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> alter session set sort_area_size = 65536;
SQL>
SQL> alter session set hash_area_size = 131072;
SQL>
SQL> select a.object_type, b.object_name
  2    from myTable a, myTable b
  3   where a.last_ddl_time = b.last_ddl_time;

Execution Plan
----------------------------------------------------------
Plan hash value: 2666611345

--------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    67 |  3082 |     3 |
|*  1 |  HASH JOIN         |         |    67 |  3082 |     3 |
|   2 |   TABLE ACCESS FULL| MYTABLE |    82 |  1640 |     1 |
|   3 |   TABLE ACCESS FULL| MYTABLE |    82 |  2132 |     1 |
--------------------------------------------------------------

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

   1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table myTable;