RE: SORT_AREA_SIZE parameter

My guess...

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).