RE: hash joins and pga/temp space?

Sorry - I meant 'adjusted by PGA_AGGREGATE_TARGET'

-----Original Message-----
From: Adrian [mailto:adex.turner_at_gmail.com] Sent: 14 November 2006 20:34
To: 'Peter Sylvester'
Cc: '_oracle_L_list'
Subject: RE: hash joins and pga/temp space?

Interesting.

On my Windows laptop (10.2.0.1) its defaulted to 209715200 (200MB).

I only looked at this setting the other day on a customers 10.2.0.2 AIX warehouse and it was set to 2GB (or 4GB, I've got brain fade).

Possibly it was configured in the init.ora, but maybe it is adjusted by SGA_TARGET. I'll look further tomorrow.

Cheers
Adrian

-----Original Message-----
From: Peter Sylvester [mailto:peters_at_mitre.org] Sent: 14 November 2006 20:24
To: Adrian
Cc: '_oracle_L_list'
Subject: Re: hash joins and pga/temp space?

Well, I checked on my system (10.2.0.2 under Solaris) and assuming the units are in bytes, I see
_pga_max_size "Maximum size of the PGA memory for one process" 419430400

(about 400MB)

--Peter

Adrian wrote:

>Re your recollection:
>
>"The memory allocated to a single SQL operator is limited to min(5%
>PGA_AGGREGATE_TARGET, 100MB) for serial operations and for parallel
>operations to min(30% PGA_AGGREGATE_TARGET/DOP, 5% PGA_AGGREGATE_TARGET,
>100MB) (DOP=Degree of Parallelism). As you see you will never get more than
>100MB for one sql operation. This 100 MB limit is in fact set by
>_pga_max_size/2 where _pga_max_size = 200MB by default."
>
>In 10gR2 at least, _PGA_MAX_SIZE is by default set to something like 2GB.

--
http://www.freelists.org/webpage/oracle-l