I am facing very peculiar problem in tuning one specific table.
I have one table which had 150 Million Records (47 GB size, non partitioned) and another approximately of same size also non partitioned.
I need the data from both these tables and almost all the records will be meeting the join condition which is equijoin, so i prefer using Hash join with parallel 16.
When i montor the process both the tables are ready within 30 mins but HASH join takes more than 8 hours to complete.
I have explored following options for this but with no success.
1. Workarea polciy to be auto and PGA_AGGREGATE_TARGET 3 GB
2. Workarea policy manual, HASH_AREA_SIZE 100M with parallel 32 at session level
Can anyone please throw some light on improving this hash join performance.