Craig,
Rather than change EVERYTHING back, can you, on initial login of that
process, set the sort_area_size and sort_area_retained_size back to the
original figures?
i.e.,
alter session set sort_area_size=65536;
alter session set sort_area_retained_size=65536;
and then let it process? This would set that one session back to the
original configuration without affecting the others.
And yes, 10M is a bit higher as the sort_area_size is allocated in the
PGA so *every* connected process got that much memory allocated. A
good, generic rule of thumb is to set the sort_area_size to the same
size as your temporary tablespace extent sizes and then tune, if
necessary, from there.
Rachel
- Craig Healey <c.healey_at_hhsuk.com> wrote:
> 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).
>
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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:55:42 CDT
