Re: sort_area_size

The large pool should also be used for parallel query message queues. 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:

1. Use MTS
2. Have min_parallel_servers set
3. Use RMAN
4. Have parallel_automatic_tuning set to TRUE.
5. 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 - 08:32:08 CDT