Payday loan very quickly they often decide Emergency Payday Loans Emergency Payday Loans not refusing to new one.

Home > oracle > set sort_area_size & sort_area_retained_size twice in 10g version

set sort_area_size & sort_area_retained_size twice in 10g version

September 28th, 2009

在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

  1. March 4th, 2014 at 10:05 | #1

    vay cong so hien dai…

  2. March 16th, 2014 at 00:59 | #2

    Definitely believe that which you said. Your favorite reason seemed to be on the net the simplest thing to be aware of. I say to you, I definitely get irked while people consider worries that they plainly don’t know about. You managed to hit the nail upon the top as well as defined out the whole thing without having side effect , people can take a signal. Will likely be back to get more. Thanks

  3. April 20th, 2014 at 15:04 | #3

    I was in fact amazed by the extreme anger I felt upon discovering my stalker.
    On the other hand, the page must be informative, speaking about the products and services the
    business is offering along with the contact information.
    Apart from ads, the business page can be created
    to enrich the product.

  4. April 20th, 2014 at 17:44 | #4

    You need to be a part of a contest for one of the greatest websites on the web.
    I’m going to recommend this blog!

Comment pages
  1. June 13th, 2013 at 21:32 | #1