3 Replies Latest reply on Jan 18, 2013 4:46 PM by Uwehesse-Oracle

    _PGA_MAX_SIZE on DWH

    AnandKumar
      Hi All,

      Oracle DB version :11.2.0.3

      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.

      Thanks in advance,
      Anand.