Sort_Area_Size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Sort_Area_Size

  1. #1
    Join Date
    Apr 2000
    Location
    Keller, TX, USA
    Posts
    9

    Question

    I have a data warehouse with 6 users. I have a 16K db_block_size, and we are also running Parallel Query with parallel_min_servers=8 and parallel_max_servers=32. How do I figure the correct sort_area_size? Where can I find the appropriate documentation for this? So far, I have been unsuccessful.

    Thank you for your help.
    pthornburg

  2. #2
    Join Date
    Apr 2001
    Posts
    219
    First you need to ask yourself a couple of questions, like:

    1. Max size of data sorted
    2. Avg. size of data sorted
    3. How often will your max sort size will be hit
    4. How can you go to disk for sorts
    5. How much memory do you have left for all your user sessions that will need sorts.

    To figure out roughly sort size:
    (avg_row_size*num_of_rows)

    avg_row_size = Is the average size of the row that goes into the sort, not the total avg row size of all tables in the query.

    num_of_rows = Similiar to above.

    Also, remember that sort size is a session level parameter, so to figure out total sort memory use this formula:
    active_sorting_sessions*(avg_row_size*num_of_rows)

    Here is a link to 8i init parameters
    http://otn.oracle.com/doc/oracle8i_8...a76961/toc.htm

    I hope this helps


  3. #3
    Join Date
    Apr 2000
    Location
    Keller, TX, USA
    Posts
    9

    Smile Sort_Area_Size

    Thanks, that does help. But as an additional note, I've been reading that when you're using parallel query, your sort_area_size should be set to sort_area_size x 2 x (degree of parallelism). Does this sound plausible?

    Thanks.
    pthornburg

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Again, not my strong suit, but that sounds more like the formula for determining the high-water mark of memory *used* for sorts. *Each* thread is going to use the amount of memory specified in SORT_AREA_SIZE, I believe. Therefore, multiplying its size by the number of threads would not make sense.

    From Docs:

    SORT_AREA_SIZE
    The recommended values for this parameter fall in the range from 256KB to 4MB.

    This parameter specifies the amount of memory to allocate *per query server process* for sort operations.
    [I added *'s]

    Thoughts,

    - Chris

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