sort_area_retained_size與sort_area

SORT_AREA_RETAINED_SIZE
• When the sort completes and the sort area still contains sorted rows to be fetched,
the sort area can shrink to the size specified by the parameter
SORT_AREA_RETAINED_SIZE.
• The memory is released back to the User Global Area (UGA) for use by the same
Oracle server process (not to the operating system) after the last row is fetched
from the sort space.
• The default value for this parameter is equal to the value of the
SORT_AREA_SIZE parameter.

SQL> select emp.deptno, ename

2 from emp, dept

3 where emp.deptno = dept.deptno

4 order by empno;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (ORDER BY) ――――――――SORT_AREA_SIZE

2 1 MERGE JOIN

3 2 SORT (JOIN)――――――――SORT_AREA_RETAINED_SIZE

4 3 TABLE ACCESS (FULL) OF 'DEPT'

5 2 SORT (JOIN) ――――――――SORT_AREA_RETAINED_SIZE

6 5 TABLE ACCESS (FULL) OF 'EMP'

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

478 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

3 rows processed

一个执行计划可以包含多个排序例，如上面的例子，其中包含了两个表的排序-合并联接，然后又执行 ORDER BY 子句的排序，所以，总共构成了三种排序。如果单个服务器正执行排序过程，那么它在执行 ORDER BY 排序时使用：

? SORT_AREA_SIZE 大小的区域（以字节为单位）用于活动排序

? 两个大小由 SORT_AREA_RETAINED_SIZE 指定的区域用于联接排序

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/95530/viewspace-810242/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/95530/viewspace-810242/