排序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> 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
          1  sorts (memory)
          0  sorts (disk)
       8655  rows processed

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
        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
          0  sorts (memory)
          1  sorts (disk)
       8655  rows processed