Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SORT_AREA_SIZE parameter

RE: SORT_AREA_SIZE parameter

From: Jamadagni, Rajendra <>
Date: Fri, 23 May 2003 05:56:06 -0800
Message-ID: <>

if you thins changing sort_area_size caused the problem, then in your thousand_times_a_day process do an alter session to set sort_area_size back to 64K ... that way you have best of both worlds.


Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Friday, May 23, 2003 7:27 AM
To: Multiple recipients of list ORACLE-L

I wonder if anyone can help me (sorry for the length of the post). Oracle 8.1.7 on Windows 2000 Server.
Bespoke OLTP application written in VB
We first got ORA-00600: internal error code, arguments: [srsget_1], [], [], [], [], [], [], []
which I figured out meant our SORT_AREA_RETAINED_SIZE was set incorrectly.
SORT_AREA_RETAINED_SIZE was the same as SORT_AREA_SIZE which was 65536. I set it to 0, and the error stopped.
However, in investigating the initial problem I came across references to increasing the size of SORT_AREA_SIZE itself, to increase the speed of sorts by doing more of them in memory. The application runs like a dead dog, so any speed increase is more than welcome. (Tune SQL? don't even go there!).
Well, I changed SORT_AREA_SIZE from the initial 64k to 10M and scheduled a reboot. We have about 150 users, and once around 100 of them logged on, we started getting ORA-04030 errors indicating out of memory. No problem, 10M was probably a bit high (no, we don't have another machine we can run tests on, so I resorted to guessing a figure that seemed to speed up a test SQL statement reasonably). I set it to 2097152 (2M) and rebooted again.
Now for my problem. Since the last reboot on Thursday morning a key process that generates and prints delivery notes has seen a marked decrease in speed. My manager wants it sorted immediately because it is affecting production. No one timed the process before the reboot, but they estimate it took about a minute to do 1 batch (they do maybe a thousand batches a day). Timing now gives two and a half minutes per batch. We also installed Apsense (sp?) software, which monitors and caps CPU usage based on user-defined rules. It's not working as it's supposed to, and we are going to remove it. The VB code for the process is very badly written, and runs a lot of SQL including numerous triggers. No one else is complaining unduly of slow processes (though they might just not have noticed). Is there anything that I might have affected with the setting changes? My manager wants to put everything back the way it was before I changed anything, suffer the ORA-00600 errors as the process they occur in is not so critical.

Thanks for reading this far. I know there is nothing very specific about the above, but any help about where to look, or possible problems would be appreciated.

Craig Healey

Please see the official ORACLE-L FAQ:
Author: Jamadagni, Rajendra

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).

Received on Fri May 23 2003 - 08:56:06 CDT

Original text of this message