We have a very old database which upgraded from Oracle 7, when I was creating an index on this database, the database run out of memory. Before I started to create the index, I did check the free memory of the host, it's 8GB free memory there.
I run the following statement to tuning the index creation.
alter session set sort_area_size=100000000;
alter session set sort_area_retained_size=100000000;
alter session set db_file_multiblock_read_count=128;
We found that each parallel process is allocated about 5G memory as PGA, this is abnormal. Did anyone else hit this problem before? We filed a tar, but Oracle did not give us a solution, just told us that do not specify 128 as the multiple block read count.
After we change the value to 64, it works fine.