linux sort 内存,排序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