set sort_area_size & sort_area_retained_size twice in 10g version

在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.

135 thoughts on “set sort_area_size & sort_area_retained_size twice in 10g version

  1. Hey There. Today using windows live messenger. It is an very savvy post. I’m going to make sure to take note of the item are available returning to get more information of one’s valuable information and facts. Was looking for publish. Let me unquestionably gain.

  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. 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. Pingback: oregon scientific atc9k action camera

  5. I usually do not drop many comments, but i did some searchning and wound up here
    eagles home set sort_area_size & sort_area_retained_size twice in 10g version.
    Andd I actually do have 2 questions for you if youu don’t mind.
    Could it be only me or does it seem like some oof the comments
    appear like the are left bby brain dead individuals?:-P And,
    if you are posting at other social sites, I would like tto keep up wit
    everything new you have too post. Would you list of the complete urls of all your community pages like your linkedin profile, Facebook
    page orr twitter feed?

  6. Spot on with this write-up, I seriously feel this sitee needs
    a lot more attention. I’ll pfobably be back again to read through more, thanks for thhe information!

  7. I like the helpful info you provide in your articles.
    I’ll bookmark your blog and checck again here regularly.

    I am quite certain I will learn many new stuff right here! Good luck for the next!

  8. Wonderful site. Lots of useful info here. I¡¦m sending it to several pals ans additionally sharing in delicious. And obviously, thanks to your sweat!

  9. Someone necessarily assist to make critically articles I would state. This is the very first time I frequented your website page and thus far? I amazed with the analysis you made to create this particular put up incredible. Great process!

  10. Whats Going down i’m new to this, I stumbled upon this I’ve discovered It positively helpful and it has helped me out loads. I am hoping to give a contribution

  11. Fantastic goods from you, man. I’ve understand your stuff previous to and you are just extremely fantastic. I really like what you have acquired here, really like what you are stating and the way in which you say it. You make it entertaining and you still take care of to keep it sensible. I can’t wait to read far more from you. This is really a tremendous site.

  12. Hiya, I am really glad I have found this info. Nowadays bloggers publish just about gossips and internet and this is actually irritating. A good site with interesting content, this is what I need. Thank you for keeping this web site, I’ll be visiting it. Do you do newsletters? Can not find it.

  13. Simply desire to say your article is as astounding. The clarity in your post is just great and i can assume you’re an expert on this subject. Fine with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please continue the gratifying work.

  14. Good ¡V I should certainly pronounce, impressed with your website. I had no trouble navigating through all the tabs as well as related information ended up being truly simple to do to access. I recently found what I hoped for before you know it in the least. Quite unusual. Is likely to appreciate it for those who add forums or anything, website theme . a tones way for your customer to communicate. Excellent task..

  15. Hi, Neat post. There is an issue with your website in web explorer, would test this¡K IE still is the market chief and a huge element of folks will pass over your great writing due to this problem.

  16. Wonderful paintings! This is the kind of info that are meant to be shared around the web. Shame on the search engines for no longer positioning this submit upper! Come on over and discuss with my website . Thank you =)

  17. I have to get across my admiration for your generosity supporting persons who really want assistance with the area. Your very own dedication to getting the message all over has been extraordinarily functional and has in every case enabled ladies like me to reach their endeavors. Your new helpful publication signifies a great deal to me and far more to my colleagues. With thanks; from each one of us.

  18. I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all web owners and bloggers made good content as you did, the internet will be much more useful than ever before.

  19. magnificent submit, very informative. I wonder why the opposite specialists of this sector do not understand this. You must proceed your writing. I’m confident, you have a great readers’ base already!

  20. Thank you for any other wonderful post. The place else may anybody get that type of information in such an ideal way of writing? I’ve a presentation next week, and I am at the look for such info.

  21. Thanks a bunch for sharing this with all folks you really understand what you’re speaking about! Bookmarked. Please also talk over with my web site =). We may have a link change arrangement between us!

  22. Hiya, I am really glad I have found this info. Nowadays bloggers publish just about gossips and internet and this is actually irritating. A good site with interesting content, this is what I need. Thank you for keeping this web site, I’ll be visiting it. Do you do newsletters? Can not find it.

  23. Good article and right to the point. I don’t know if this is really the best place to ask but do you folks have any thoughts on where to employ some professional writers? Thanks in advance :)

  24. Wonderful paintings! This is the kind of info that are meant to be shared around the web. Shame on the search engines for no longer positioning this submit upper! Come on over and discuss with my website . Thank you =)

  25. Excellent blog right here! Additionally your website rather a lot up fast!
    What host are you the use of? Can I am getting your
    associate link on your host? I wish my site loaded
    up as quickly as yours lol

  26. “I just want to say I am new to weblog and actually savored this page. Most likely I’m want to bookmark your blog post . You actually have excellent articles and reviews. Thank you for sharing your website page.”

  27. We’re giving free product samples of high-end sweets.
    To be eligible, simply respond to our comment together with your
    address and we will mail it out within the next business day.

  28. Can I simply just say what a relief to discover somebody who genuinely understands what they’re discussing on the net.

    You definitely know how to bring a problem to light and make
    it important. A lot more people need to check this out and understand this side
    of the story. I can’t believe you aren’t more
    popular since you definitely possess the gift.

  29. Howdy very cool site!! Man .. Excellent .. Wonderful .. I’ll bookmark your web site and take the feeds also¡KI am glad to search out numerous useful info here within the post, we want work out extra strategies on this regard, thanks for sharing. . . . . .

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>