一、SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的区别
| 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
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%';
251
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
