Oracle-L: Re:RE: Limited SORT_AREA

From: <dgoulet_at_vicr.com>
Date: Tue, 28 May 2002 12:24:32 -0800
Message-ID: <F001.0046D3BF.20020528122432@fatcity.com>

One item to remember on this note of using alter session is that if a pile of your SQL*Plus users have the alter session privilege and set sort_area_size to a large value at the same time, you can soon run out of memory. At which time all of the above listed users will come down complaining about a slow system, which is of course not your fault, but then it is your fault since everything is a database issue.

Dick Goulet
Having a really rotten Monday, or is it Tuesday.

I hate Monday's,
especially when they fall on Tuesday.

____________________Reply Separator____________________
Author: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date:       5/28/2002 10:30 AM

Arun - No, I just needed to rebuild our largest table this weekend and re-create the indexes afterward. Fortunately with SORT_AREA_SIZE at 150-meg., the indexes build in a reasonable amount of time, so it was merely an inconvenience. I was able to enjoy one day of the weekend. I just wondered for next time I do this. Since I only perform this task once every couple of years, I'll probably forget most of the details before I do it again. I just wondered if anyone had a reason why I couldn't go past 150-meg. But I appreciate your suggestion because there are obviously many times when bouncing the instance is inconvenient. But that is the best part of this list, the opportunity to get ideas from the great minds. Most days it is pretty humbling.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Tuesday, May 28, 2002 1:05 PM
To: Multiple recipients of list ORACLE-L

Are u getting that error quite frequently

-----Original Message-----
Sent: Tuesday, May 28, 2002 1:52 PM
To: Multiple recipients of list ORACLE-L

Arun - Thanks for the tip of using ALTER SESSION. I'll try that next time, and I hope next time isn't too soon.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Tuesday, May 28, 2002 12:32 PM
To: Multiple recipients of list ORACLE-L

Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got.

-----Original Message-----
Sent: Tuesday, May 28, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L

This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received:

oracle.fin7>maxmem

Memory starts at: 5369596416 (1400d8a00)
Memory ends at:   6399795195 (17d751ffb)
Memory available: 1030198779 (3d6795fb)

So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas?

Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E
4-cpu.
4-gig. of system memory.
shared_pool_size = 400-meg
This is the only instance on this server.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Arun Chakrapani
INET: ArunC_at_1800FLOWERS.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Re:RE: Limited SORT_AREA_SIZE