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






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;








29.62.Session Variables
29.62.1.ALTER SESSION SET SQL_TRACE = TRUE
29.62.2.ALTER SESSION SET optimizer_dynamic_sampling
29.62.3.alter session set NLS_DATE_FORMAT='DD-MM-YYYY'
29.62.4.alter session set hash_area_size
29.62.5.alter session set sort_area_size = 102400000
29.62.6.alter session set workarea_size_policy=manual
29.62.7.alter system flush shared_pool
29.62.8.alter system quiesce restricted
29.62.9.alter system resume
29.62.10.alter system suspend