SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的区别以及设置

一、SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的区别

SQL> select emp.deptno, ename

2from emp, dept

3where emp.deptno = dept.deptno

4order by empno;

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE

10SORT (ORDER BY)――――――――SORT_AREA_SIZE

21MERGE JOIN

32SORT (JOIN)――――――――SORT_AREA_RETAINED_SIZE

43TABLE ACCESS (FULL) OF 'DEPT'

52SORT (JOIN)――――――――SORT_AREA_RETAINED_SIZE

65TABLE ACCESS (FULL) OF 'EMP'

Statistics

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

0recursive calls

0db block gets

14consistent gets

0physical reads

0redo size

478bytes sent via SQL*Net to client

503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

3sorts (memory)

0sorts (disk)

3rows processed

SQL>

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

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

SORT_AREA_RETAINED_SIZE指定的区域用于联接排序

二、SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的设置

在10g version中，如果是manul PGA管理模式下，alter session set sort_area_size和sort_area_retained_size要设两次才能生效。

SQL>alter session set sort_area_size= 500000000;

SQL>alter session set sort_area_size= 500000000;

对于parallel sort,设置两次还不能对slave进程生效，可以通过设置隐藏参数 _sort_multiblock_read_count使其生效

SQL>alter session set sort_area_size= 500000000;

SQL>alter session set “_sort_multiblock_read_count”=4;

我测试了正在使用的10203和10204版本，都可以重现

大家也可以试试自己的版本是不是有这一问题，测试步骤如下：

首先建一个test表，大约几十M吧。

测试过程如下：

我们系统上default sort_area_size=4M, sort_area_retained_size没有设，默认和sort_area_size一样

sqlplus xxxx/xxxx

SQL*Plus: Release 10.2.0.3.0 – Production on Sun Sep 27 08:25:09 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select sid from v$mystat where rownum=1;

SID

———-

19975

SQL> !ora sesstat 19975 memory

NAME VALUE

————————————————————- ———-

session uga memory 200704

session uga memory max 203328

session pga memory 609024

session pga memory max 609024

workarea memory allocated 0

sorts (memory) 24

6 rows selected.

SQL> alter session set sort_area_size=500000000; — set to 500M

Session altered.

SQL> select count(*) from (select * from test order by 1,2,3,4,5,6);

COUNT(*)

———-

857152

SQL> !ora sesstat 19975 memory

NAME VALUE

—————————————————————- ———-

session uga memory 4337344

session uga memory max 4337344 — 可以看到这里使用的是默认的4M sort_area_size

session pga memory 5212192

session pga memory max 5212192

workarea memory allocated 0

sorts (memory) 24

6 rows selected.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

sqlplus xxxxx/xxxx

SQL*Plus: Release 10.2.0.3.0 – Production on Sun Sep 27 08:26:50 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select sid from v$mystat where rownum=1;

SID

———-

19975

SQL> !ora sesstat 19975 memory

NAME VALUE

—————————————————————- ———-

session uga memory 200704

session uga memory max 203328

session pga memory 609024

session pga memory max 609024

workarea memory allocated 0

sorts (memory) 24

6 rows selected.

SQL> alter session set sort_area_size=500000000;

Session altered.

SQL> / — 再设置一遍

Session altered.

SQL> select count(*) from (select * from test order by 1,2,3,4,5,6);

COUNT(*)

———-

857152

SQL> !ora sesstat 19975 memory

NAME VALUE

—————————————————————- ———-

session uga memory 57545920

session uga memory max 57545920

session pga memory 58019184 — 这里就是使用的我们设置的500M了

session pga memory max 58019184

workarea memory allocated 0

sorts (memory) 25

6 rows selected.

 sort_area_retained_size 实验

sqlplus xxxx/xxxxx

SQL*Plus: Release 10.2.0.3.0 – Production on Sun Sep 27 08:47:09 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> alter session set sort_area_size=500000000;

Session altered.

SQL> show parameter sort

NAME TYPE VALUE

———————————— ———– ——————————

nls_sort string

sort_area_retained_size integer 0

sort_area_size integer 500000000

SQL> alter session set sort_area_retained_size=10000000;

Session altered.

SQL> select count(*) from (select * from test order by 1,2,3,4,5,6);

COUNT(*)

———-

857152

ora sesstat 19975 memory

NAME VALUE

—————————————————————- ———-

session uga memory 57548992

session uga memory max 57548992

session pga memory 58019184 — sort_area_size是500M,但是 sort_area_retained_size还是默认值

session pga memory max 58019184

workarea memory allocated 0

sorts (memory) 27

6 rows selected.

sqlplus xxxxx/xxxxxx

SQL*Plus: Release 10.2.0.3.0 – Production on Sun Sep 27 08:48:19 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> alter session set sort_area_size=500000000;

Session altered.

SQL> alter session set sort_area_retained_size=10000000;

Session altered.

SQL> alter session set sort_area_retained_size=10000000;

Session altered.

SQL> select count(*) from (select * from test order by 1,2,3,4,5,6);

COUNT(*)

———-

857152

SQL>ora sesstat 19975 memory

NAME VALUE

—————————————————————- ———-

session uga memory 10358720

session uga memory max 10358720

session pga memory 58001672 — 这里sort_area_retained_size就起作用了

session pga memory max 58001672

workarea memory allocated 0

sorts (memory) 25

6 rows selected.

设置的大小可以参考

SQL>select name,value from v$sysstat where name like 'sort%';