sort_area_retained_size與sort_area_size

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@]