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

Re: sort_area_size

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 31 Jul 2002 05:32:01 +1000
Message-ID: <ai6pij$4co$1@lust.ihug.co.nz>

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0207300532.46b63e2a_at_posting.google.com...
> The large pool should also be used for parallel query message queues.

As I said ..."it is SORT OF true". Meaning that the original poster raised two issues, and I saw no reason to complicate the answer with something he didn't ask about.

Regards
HJR
> The shared pool may be drubbed up to 100 meg for parallel message
> queues otherwise (based on the value of _parallel_min_message_pool
> which is the minimum size of shared pool memory to reserve for pq
> servers and defaults to 106954752 bytes.
> I have also seen a situation in 8.1.7 on AIX where a 15 table join
> failed and caused excessive CPU spin resulting in the need to kill the
> processes issuing the query if the large pool was not enabled. There
> are undocumented parameters to force the use of the large pool for
> parallel query without turning on MTS. Just setting the size of the
> large pool using large_pool_size will not enable its use, you must do
> one of the following:
>
> a. Use MTS
> b. Have min_parallel_servers set
> c. Use RMAN
> d. Have parallel_automatic_tuning set to TRUE.
> e. Use the parallel query undocumneted parameter to turn on the large
> pool message queues. The parameter is:
>
> _PX_use_large_pool Use Large Pool as source of PX
> buffers
>
> If the "free memory" equals the size of the large pool, it is not
> being used. If you don't specifically set it, it doesn't show up in
> v$sgastat.
>
> Mike
>
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<ai3cjl$lq$1_at_lust.ihug.co.nz>...
> > S-A-S is a coponent of the UGA (User Global Area).
> >
> > The UGA is a component of the PGA (Program Global Area).
> >
> > When running in dedicated server mode, the PGA (and the UGA) is entirely
> > separate from the Shared Pool. But in MTS, the UGA cannot be part of the
> > PGA, and must instead be shifted somewhere global. By default, the UGA
(and
> > hence the S-A-S) gets shunted into the Shared Pool. *If* a Large_pool
has
> > been configured, then the UGA gets shunted there instead.
> >
> > Therefore, it is NOT true that the sort_area_size will be allocated from
the
> > large_pool if you are running Dedicated Server.
> >
> > It is sort of true that the Large Pool only makes sense in MTS and when
> > running RMAN.
> >
> > If you are running RMAN or MTS without a large pool, *then* the shared
pool
> > gets used for the memory allocations.
> >
> > Regards
> > HJR
> >
> > "Daud" <daud11_at_hotmail.com> wrote in message
> > news:f0bf3cc3.0207290154.91001cb_at_posting.google.com...
> > > Oracle 8.1.7.3.0 on HPUX 11.0
> > >
> > > It is true that when large_pool_size is set, the sort_area_size will
> > > be allocated from it instead of shared_pool_size. Or is
> > > large_pool_size only use for MTS and RMAN.
> > >
> > > rgds
> > > Daud
Received on Tue Jul 30 2002 - 14:32:01 CDT

Original text of this message

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