Re: Memory Sorts

Hi Roman

This would be something like between 1.5 and 2 times the sum of the total number of bytes involved in the sort. The number of bytes is basically the total number of bytes returned by the select expression. Allowing for NULLs a number counts for 50 percent of the length (as numbers are packed), a varchar2 also for 50 percent of the length and a date column for 7 bytes. This times the number of records. You can experiment with the vsize function to determine the 'real' number of bytes. I usually don't go into that much detail, my rule of thumb is that 64k is usually insufficient. In normal cases 256k should do and of course you can always determine the number of disk sorts over time with utlbstat and utlestat (in $ORACLE_HOME/rdbms/admin) and increase sort_area_size accordingly.

Hope this helps,

Sybrand Bakker, Oracle DBA

Roman Gelfand wrote:

> What is the formula for sort_area_size based on a particular query?
>
> For example, I am doing an ordered select on a view which is a four table
> join. How do you calculate sufficient memory size needed for sort area so
> that sort is done in memory and why?
>
> Thanks,
>
> Roman
Received on Fri Sep 11 1998 - 13:23:49 CDT