| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SORT_AREA_SIZE parameter
Craig:
What is your current sort area retained size? We have located several bugs in 8.1.7.0.0 on Windows that correspond to this issue. I would recommend that you set your SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE parameter to no greater than 2MB and keep both at the same value. This will eliminate the errors you are receiving.
By keeping the same size, you eliminate the process from reallocating sort area. This reallocation could be one of the culprits for the marked performance degradation. Cary & Jonathan: I remember that 8.1.7.4.x solved this problem, but will this provide a pronounced affect for the app?
Thank You
Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: Stephen.Karniotis_at_Compuware.com Web: www.compuware.com -----Original Message----- Sent: Friday, May 23, 2003 7:27 AM To: Multiple recipients of list ORACLE-L Subject: SORT_AREA_SIZE parameter
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).
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Karniotis, Stephen
INET: Stephen_Karniotis_at_compuware.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 - 08:57:01 CDT
 |
 |