Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    1

    Arrow Unanswered: How to tune Sort Area Size

    How to tune Sort Area Size

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This should be equal to the next value in you Temporary Tablespace where users sort ....

    The question poses other questions:

    1. How many users
    2. How many sorting operations
    3. How large is the data that is being sorted ...

    etc

    You need to know your data and how it is accessed... start with a meg and see how that works (if you don't have a large number of users and don't have the available resources ....)

    HTH
    Gregg

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Use a temporary tablespace to reduce the frequent allocation and de-allocation of temporary segments.

    Use one or more temporary tablespaces to minimize conflicts in data access.

    Stripe the temporary tablespaces over multiple disks. If the tablespaces are striped over 4 disks instead of 2 disks, you can speed up sort operations twofold.
    SATHISH .

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    To calculate NEXT value, use the Formula

    (n*s + b)

    where,
    n = positive integer,
    s = value of SORT_AREA_SIZE initialization parameter,
    b = value of DB_BLOCK_SIZE initialization parameter.
    SATHISH .

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: How to tune Sort Area Size

    Originally posted by veer_suneel
    How to tune Sort Area Size
    In 9i it would be best IMHO to use PGA_AGGREGATE_TARGET in order to make sort_area_size and its cousin hash_area_size autotuning - automatically giving users a lot of memory to sort&hash when there is plenty free, and less when it is scarce.

    Having of course a TEMP tablespace which is locally-managed , with a uniform extent of 1M or so (with LMT the allocation-deallocation of extents is no longer a concern, and with PGA_AGGREGATE_TARGET the size of sort&hash segments is variable ... so better to have it short).

    Al

Posting Permissions

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