Sort_Area_Size

[pthornburg]

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.

[Zaggy]

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

[pthornburg]

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.

[chrisrlong]

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