Sort_area_size advice
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Sort_area_size advice

  1. #1
    Join Date
    Apr 2003
    Location
    Kuala Lumpur
    Posts
    59

    Sort_area_size advice

    I have installed 9.2.0.1 as dedicated server option. I have a few question here. Pls read the below text from Oracle guide.

    1. How do I check my Oracle is running in automatic memory management mode?
    2. I'm using PGA target, so sort_area is it used?
    3. sort_area_retained_size is currently 0. Will performance improve if I increase the size?
    4. If I disconnected the session, the freed memory of sort_are_retained_size will be freed right?

    Thanks.

    -----

    Configuring SORT_AREA_SIZE
    Tuning sort operations using SORT_AREA_SIZE is only relevant for configurations running the Oracle shared server option or for configurations not running under the automatic memory management mode. In the later case, Oracle Corporation strongly recommends switching to the automatic memory management mode, because it is easier to manage and often outperforms a manually-tuned system.

    Considerations with SORT_AREA_RETAINED_SIZE
    With dedicated connections, the freed memory is not released to the operating system, rather the freed memory is made available to the session for reuse.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    1. SHOW PARAMETER WORKAREA_SIZE_POLICY

    2. SORT_AREA_SIZE is not used when you are using PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY IS TRUE. But there was a bug regarding this (1961633) which was fixed in release 2.

    3. Oracle does not recommend use of SORT_AREA_RETAINED_SIZE (with Auto PGA Manage) unless you are using shared server.

    4. The memory is released back to UGA not to the OS.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Apr 2003
    Location
    Kuala Lumpur
    Posts
    59
    Thanks Sanjay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width