RE: SORT_AREA_SIZE parameter

Craig,

I assume that you are using the Cost based Optimizer (i.e. your init.ora parameter OPTIMIZER_MODE defaults to CHOOSE) - if so, please be aware that a number of init.ora parameters, including SORT_AREA_SIZE affect the execution plan that the CBO produces. For e.g. HASH_AREA_SIZE defaults to 2 X SORT_AREA_SIZE, which will make Hash Joins look less expensive that they actually are, and that particular SQL may take a different path leading to your problem... As Rachel indicated, you should set it per program. This can be easily achieved using the System ON DATABASE trigger....

It is true that a thread on Metalink Forums seems to indicate that SARS should be set to SAR as a workaround, so why not set both values back to 64 k...

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my future!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Craig Healey [mailto:c.healey_at_hhsuk.com]
> Sent: Friday, May 23, 2003 4: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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).