Oracle-L: RE: Limited SORT_AREA

From: Stephen Andert <StephenAndert_at_firsthealth.com>
Date: Tue, 28 May 2002 10:56:04 -0800
Message-ID: <F001.0046D0FC.20020528105604@fatcity.com>

Dennis,

I had to rebuild some indexes on a big table. I started one with a standard session and it took over 6 hours and didn't finish (I killed it). I set my sort_area_size to 2GB or something like that using ALTER SESSION (plenty of memory free on that machine) and each one finished in 10 minutes. Pretty impressive,

Stephen Andert

>>> DWILLIAMS_at_LIFETOUCH.COM 05/28/02 11:30AM >>> 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
--------------------------------------------------------------------

--

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

Author: Stephen Andert
INET: StephenAndert_at_firsthealth.com

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

RE: Limited SORT_AREA_SIZE