Automatic PGA Memory Management- Memory Structures

Jan 22, 2024

Automatic PGA Memory Management- Memory Structures

In almost all cases, you should be using automatic PGA memory management. The entire goal of automatic PGA memory management is to maximize the use of RAM while at the same time not using more RAM than you want. You can enable the automatic management of the PGA in two ways:

•\ Set MEMORY_TARGET to zero, and then set PGA_AGGREGATE_TARGET to a nonzero value. The PGA_AGGREGATE_TARGET parameter controls how much memory the instance should allocate, in total, for all work areas used to sort or hash data. Its default value varies by version and may be set by various tools such as the DBCA. In this mode, WORKAREA_SIZE_POLICY is set to AUTO (which is its default value).

•\ Use the AMM feature by setting MEMORY_TARGET to a nonzero value, and leave PGA_AGGREGATE_TARGET set to zero. This effectively lets Oracle manage the allocation of memory to the PGA. However, if you’re in a Linux environment that uses HugePages, you should not be using the AMM method to manage memory (more on this in the“System Global Area (SGA) Memory Management” section of this chapter).

The prior two techniques are discussed in the following subsections.

Setting PGA_AGGREGATE_TARGET

Most of the databases I’ve worked on in the past several years use automatic PGA memory management and automatic SGA memory management. For my test database, automatic PGA memory management and automatic SGA memory management are enabled as follows (you’ll use memory sizes appropriate for your environment, given the workload and amount of physical memory available):

$ sqlplus / as sysdba
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set pga_aggregate_target=300M scope=spfile; SQL> alter system set sga_target=1500M scope=spfile;

Then restart the instance to instantiate the parameters (using startup force here which shuts down abort and restarts the instance):

SQL> startup force;

You don’t have to enable both PGA and SGA memory management together (as shown in the prior example). You could enable one for automatic management and leave the other for manual management. I usually don’t implement it that way, but you can do it.

Also, some places I’ve worked have set the PGA_AGGREGATE_LIMIT parameter as well. In most scenarios, you don’t need to set this parameter as it will default to a reasonable value. If for some reason you need more control, then feel free to set it. Keep in mind if you set this parameter too low, you’ll get an ORA-00093 error, and your instance will fail to start. In this situation, you’ll need to create a text-based init.ora file, and restart your instance, and re-create your spfile (see Chapter 3 for details on how to do this).

Setting MEMORY_TARGET

The automatic memory management of the PGA is enabled as follows (adjust the memory sizes per your environment):

$ sqlplus / as sysdba
SQL> alter system set memory_target=1500M scope=spfile;
SQL> alter system set pga_aggregate_target=0 scope=spfile;
SQL> alter system set sga_target=0 scope=spfile;

And at this point, you can restart your instance to instantiate the parameters. If you want to give Oracle recommendations on minimal values to use for the SGA_TARGET and PGA_AGGREGATE_TARGET, you can set these to a nonzero value (as long as the sum of these is less than the value of MEMORY_TARGET):

SQL> alter system set sga_target=500M scope=spfile;
SQL> alter system set pga_aggregate_target=400M scope=spfile;

Note This PGA_AGGREGATE_TARGET parameter is optional for pluggable databases. When this parameter is set in a pluggable database, it specifies the target aggregate PGA size for the pluggable database.

Now that we’ve covered enabling automatic PGA memory management, let’s next look at how the PGA memory is allocated.