oracle排序sort area 内存不足会用到临时表空间

排序:
使用到排序的操作有: create index,order by ,group by ,收集统计信息时,都可能会用临时表空间
排序操作首先会在sort area 内存中进行排序,一旦sort area 内存不足,则会使用到临时表空间 sort in disk
oracle 9i 目前排序在pga 中用 自动管理
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 25165824
SQL> show parameter work
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
一般把这个参数设大点
以下例子说明sort area 内存不足 用到临时表空间 sort in disk
ALTER SESSION SET workarea_size_policy =MANUAL --改为手动
SQL> [color=#cc3333]alter session set sort_area_size=10000000;
SQL> set autotrace traceonly
SQL> select owner,object_name from dba_objects order by object_id;
8655 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'USER$'
8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11 3 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'USER$'
13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5443 consistent gets
0 physical reads
0 redo size
248492 bytes sent via SQL*Net to client
6839 bytes received via SQL*Net from client
578 SQL*Net roundtrips to/from client
[color=#cc3333]1 sorts (memory)
[color=#cc3333]0 sorts (disk)
8655 rows processed
[color=#cc3333]SQL> alter session set sort_area_size=100;
Session altered.
SQL> set autotrace traceonly
SQL> select owner,object_name from dba_objects order by object_id;
8655 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'USER$'
8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11 3 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'USER$'
13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Statistics
----------------------------------------------------------
7 recursive calls
34 db block gets
5445 consistent gets
[color=#cc3333]185 physical reads --物理读
0 redo size
248492 bytes sent via SQL*Net to client
6839 bytes received via SQL*Net from client
578 SQL*Net roundtrips to/from client
[color=#cc3333]0 sorts (memory)
[color=#cc3333]1 sorts (disk)
8655 rows processed