3 Replies Latest reply: Jan 18, 2013 10:46 AM by Uwehesse-Oracle RSS

    _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.
        • 1. Re: _PGA_MAX_SIZE on DWH
          JohnWatson
          If you want to be scientific about it, run your queries with various hinted plans, and query v$sql_workarea to see what memory they need to run optimally.

          Here is an excellent discussion of the problem you may be facing:
          http://www.orafaq.com/forum/mv/msg/185127/571679/0/#msg_571679
          • 2. Re: _PGA_MAX_SIZE on DWH
            Mihael
            This is undocumented parameter and you can use it after Oracle Support suggestion. PGA limitations is 100 Mb per process, so if you use Parallel Execution, you can involve more PGA.
            • 3. Re: _PGA_MAX_SIZE on DWH
              Uwehesse-Oracle
              Instead of using hints or undocumented parameters even, you may consider to use WORKAREA_SIZE_PLOLICY=MANUAL in your critical sessions and give them a large SORT_AREA_SIZE then.

              Kind regards
              Uwe Hesse

              "Don't believe it, test it!"
              http://uhesse.com