RE: hash joins and pga/temp space?

  • From: Adrian <ade.turner@xxxxxxxxx>
  • To: "'Peter Sylvester'" <peters@xxxxxxxxx>
  • Date: Tue, 14 Nov 2006 20:35:28 -0000

Sorry - I meant 'adjusted by PGA_AGGREGATE_TARGET'

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


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

I only looked at this setting the other day on a customers 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.


-----Original Message-----
From: Peter Sylvester [mailto:peters@xxxxxxxxx] 
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 ( 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)


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
>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.


Other related posts: