We have a DWH database of 2.5 Tb size, physical memory of OS is 64 GB and with SGA 52 Gb and PGA 8 Gb.
Most of the queries are going for Nested loops and when we use USE_HASH hint in the query, it is running faster.
We have huge fact tables and few huge dimensions tables as well.
I was going through the underscrore parameter "_pga_max_size" which defaults to 200 m.
By increasing this parameter we can control the optimizer to use more hashing rather than going for other methods (like NL).
1. Is my above statement correct ?
2. If yes, what could be ideal value for this parameter.