memory calculation used by Oracle

Hi all,

I'm trying to resolve an ora-4030 problem on Oracle 817-aix

The unix process is limited to 32M.
When using standard hash_area_size=16m and sort_area_size=8m the query failed with a pga of 30M.

If limiting hash_area_size=1M and sort_area_size=1m the sql works and the max pga is 11M.

The query does a lot of sorting (group by and sort merge) and hash joins

My theory is the following:
I used all the memory allocated for hash_area_size and sort_area_size.

In the case of 1M
I used 1M for hash_area_size and sort_area_size and I ended up with a PGA of 11
My query needs 11 -2 = 9M

In the case of hash_area_size =16 and sort_area_size =8 The memory left for the query is
30-(16+8)=6M

So I missed 3M when running with hash_area_size =16 and sort_area_size =8 for my query to complete

Am I wrong ?

To make sure how can I know how much space I'm using in the hash_area_size and sort_area_size ?

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187 stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca> Received on Fri Jul 25 2003 - 10:59:59 CDT