Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Daniel Morgan and Syband Bakker

Re: Daniel Morgan and Syband Bakker

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Thu, 27 Jun 2002 18:55:43 +1000
Message-ID: <afek04$v5e$1@lust.ihug.co.nz>

"Jeff" <jeff_at_work.com> wrote in message
news:afcni7$4bp$1_at_cronkite.cc.uga.edu...
> In article <a9bhhu0oegia7dpnjb7hmilej8o0k3cqjl_at_4ax.com>,
postbus_at_sybrandb.demon-verwijderdit.nl wrote:
>
> >
> >Why are you nitpicking? Your statement doesn't contradict my statement
> >does it?
>
> Yeah, it certainly does contradict your (possibly unintentional
mis-)statement
> that the sort area is not in the SGA when "not running dedicated server,"
> implying MTS and shared server processes. In such a case, the sort area
IS in
> the SGA--the UGA, specifically, which is either in the large pool or
shared
> pool.

The sort area *isn't* in the UGA. At least, not all of it is, and not the bit used for the initial sort. The bit that goes into the UGA is the bit referenced by sort_area_retained_size, which is used at the end of the primary sort, during the merge phase -when partial sorts (the 'sort runs') are merged into a single, fully sorted, result set immediately prior to return to the user.

The bit that is used for the primary sort, which is sized by the sort_area_size init.ora parameter, is ALWAYS attached to the Server Process, whether in dedicated or shared server configuration (MTS=Shared Server). And the Server Process is not part of the SGA.

Oracle's standard advice for MTS configuration seems to be to set nsort_area_retained_size to half the sort_area_size. Therefore, in a standard-configured MTS, the bulk of the total amount of memory used to perform sorts is going to be in the PGA, attached to the Server Processes, and thus not in the SGA at all.

So, to nitpick, a more accurate statement would have been "SOME OF the sort area IS the SGA, but most of it isn't".

Regards
HJR
>If that's nitpicking, my apologies. Perhaps the crassness and tone of
> of your response, particularly in the first three lines, made it easier to
do.
>
> Your overall point that a 1G sort_area_size setting is "ridiculous" is
> unquestionable. And your INTENTION(?) to state, that the sort area isn't

> shared amongst other sessions--that sort_area_size is a per SESSION
> setting--would also be valid... if that's what you meant?
Received on Thu Jun 27 2002 - 03:55:43 CDT

Original text of this message

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