Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Shanghai , China
    Posts
    63

    Unanswered: 100MB Sort Area Size

    I am tuning a oracle8.0.5 database . I am surprised discover that in the original initial file , the sort area size is set 100MB (even though the temporary tablespace is only 100MB) .

    I think it's very wrong as the sort area size is created in UGA and it may consume more Server memeory .


    How do you think ? Any suggestion
    Oracle is an ocean . I am just a little fish

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    You're right... that definately seems wrong. Sort area size is also allocated per user, so you will quickly run out of memory.

    The default setting for this is only 64K. According to Oracle, this value is adequate for most OLTP operations. DSS and large batch jobs would benefit from a larger size, but nothing like 100MB. I usually set my sort area on our DSS machines to 2MB or 4MB. This is only on our decision supprt systems. They all have 4GB or more of RAM and only half a dozen or so concurrent users.

    As a side note: In 9i, Oracle reccomend that you do not set the sort area size unless you are using the shared server option. Instead, you should use PGA_AGGREGATE_TARGET which allows automatic sizing of the SQL working areas. SORT_AREA_SIZE is only there for backward compatibility.

  3. #3
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: 100MB Sort Area Size

    Hi.

    In tuning there aren't right or wrong settings, it's only a matter of balance your target with your resources.

    The value for sort_area_size as large as 100M can be tolerated quite well by some hi-end and ram fat machines.

    Using that sort area size can end up in no sorting to disk at all, so 100M temp can stay unused. But the first time a sort operation need disk you can be in some trouble (if and only if TEMP datafiles are not in autoextend mode).

    Take a look at your server if you have enough ram to keep the load.

    see you soon
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

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