RE: SORT_AREA_SIZE parameter

10M is enough to completely exhaust the virtual memory on the box. 2M is
enough to completely exhaust the *physical* memory on the box. The key
process is slowing down due to extreme paging I/O. Take a look at "top"
or similar utility and see if there's a huge disparity between the total
process size and the resident size or use iostat to look for heavy I/O to
the swap partitions. There's also vmstat but which statistic to use
varies by OS, OS version, and who wrote the last white paper you read.

If it is a paging/memory problem, here's my quick & dirty suggestions:

If you're using a *NIX and not using raw volumes for datafiles look at
using a mount option that tells the OS to bypass the file buffer cache for
that filesystem. (On Solaris it's "forcedirectio", Veritas has a similar
concept but it's something like mincache or convsync) This prevents
double buffering of file pages in RAM and can help if the machine is close
to paging. Since you're removing what amounts to a secondary cache
behind Oracle's buffer cache, make sure db_block_buffers is adequate
first.

Is your SGA too big? Letting the OS decide what pages need to be in
memory is *WAY* less efficient than letting Oracle decide what blocks need
to be in the SGA.

Shutdown ancillary process on the server and/or get more RAM.

As for the ORA-03232, check out 75183.1 and 125271.1 on Metalink. The
HASH_AREA_SIZE (which is derived from SORT_AREA_SIZE) needs to "fit" the
TEMP extent size.

S-

On Fri, 23 May 2003, Craig Healey wrote:

> Thanks for the replies. I've had to set the parameters back to their> starting positions (SORT_AREA_SIZE=64k, SORT_AREA_RETAINED_SIZE=64k).> I still don't understand why this parameter caused the problem. Guess> I'll have to find time to RTFM. I *think* the temp tablespace next> extent size is 10M. Maybe I need to look at that too. That'll be the> ORA-03232 error I tried to correct. Oh well, Monday is a bank holiday,> so I don't have to think about it for 3 days :-)>> Craig>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steve Rospo
INET: srospo_at_watchmark.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).