Interview Question : Can we reserve memory for PGA,If AMM is enabled?

As we know from 11g , Oracle introduced Automatic memory Management also called AMM by using two parameters

memory_target

memory_max_target

So Oracle Server will distribute across SGA and PGA with in the size of memory_target If AMM is enabled.

To Enable AMM, Set the memory_target > 0 with in the size of memory_max_target or equal to.

In this case, If AMM enabled, the SGA_TARGET,PGA_AGGREGATE_TARGET can set to 0 to distribute the amount of memory required by SGA and PGA with in the size of memory_target.

We can find the current values assigned for SGA and PGA also for memory_target by using

sql>show parameter memory_target

sql>show parameter memory_max_target

sql>show parameter pga_aggregate_target

sql>show parameter sga_target

Now as we found the value to be 0 for PGA as AMM enabled . We can reserve with in the size of memory_target.

Answer: Yes, We can reserve the amount of memory for PGA/SGA with in the size of memory_target.

Using command,

sql>alter system set pga_aggregate_tartet=200m scope=both;

Note: But if required more than 200m,oracle can distribute to pga with in the size of memory_target when needed by PGA.

About PGA : PGA more used for sorting. Sorting is usually performed in a memory area called the PGA (configured via the PGA_AGGREGATE_TARGET parameter). If memory is insufficient to accommodate sorting, then the sort over flows to disk within the temporary tablespace.