| 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
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
 |
 |