eagle’s home

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

alter session set sort_area_size= 500000000;
alter session set sort_area_size= 500000000;

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

parallel sort:

alter session set sort_area_size= 500000000;
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.

Eagle Fan oracle