Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: SORT_AREA_SIZE parameter

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 23 May 2003 06:24:52 -0800
Message-ID: <F001.005A1644.20030523062452@fatcity.com>


Could this be a bug?
Also, to experiment with sort sizes, you do not need to reboot server/bounce the database. Those parameters can be set at session level. You can consider logon trigger to change their values for the user ids that will run your critical processes.

-----Original Message-----
Sent: Friday, May 23, 2003 6: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: http://www.orafaq.net
--

Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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). Received on Fri May 23 2003 - 09:24:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US