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: Thomas Day <tday6_at_csc.com>
Date: Fri, 23 May 2003 08:17:05 -0800
Message-ID: <F001.005A1833.20030523081705@fatcity.com>

What's your total RAM on the machine and how big is your SGA (and is this the only instance on that server)? I'm sure that there's a lot more to this conversation that you've elided to make the post this short. Try setting SORT_AREA_RETAINED_SIZE to be the same as SORT_AREA_SIZE. Otherwise, whenever you do a sort Oracle has to allocate 2M of the SGA (and then release it when the sort is done). Talk to the SA about getting more of the RAM allocated to your SGA but don't go over 70% of available RAM (I try to stay under 40% --- I don't want to go anywhere near the swapfile).

                                                                                                                                       
                      "Craig Healey"                                                                                                   
                      <c.healey                To:      Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      @hhsuk.com>              cc:                                                                                     
                      Sent by: root            Subject: SORT_AREA_SIZE parameter                                                       
                                                                                                                                       
                                                                                                                                       
                      05/23/2003 07:26                                                                                                 
                      AM                                                                                                               
                      Please respond                                                                                                   
                      to 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


This email and any files transmitted with it are confidential and intended solely
for the use of the individual or entity to whom they are addressed and may contain
confidential and/or privileged material. Any review, retransmission, dissemination
or other use of, or taking of any action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited. Statements
and opinions expressed in this e-mail may not represent those of the company.

If you have received this email in error please notify system.administrator_at_hhsuk.com

This footnote also confirms that this email message has been swept by MIMEsweeper
for the presence of computer viruses (www.mimesweeper.com)


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Craig Healey
  INET: c.healey_at_hhsuk.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: tday6_at_csc.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 - 11:17:05 CDT

Original text of this message

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