How do I limit Oracle's memory use?

Question

I would like to use Oracle's automatic memory management, with a limit of around 4GB. (Past experience has shown this to be plenty for my dev PC and that it is easier to get an idea about any performance issues with less than that.)

I believe these are the instructions to follow:

Oracle 12 automatic memory management

It says in short, to enable automatic memory management, set memory_target and (optionally) memory_max_target. The former parameter should be a dynamic value and the latter a harder limit, that can only be changed when stopping/starting the database.

I set these as follows as sysdba:

SQL> alter system set memory_target = 4G scope = spfile;

System altered.

SQL> alter system set memory_max_target = 8G scope = spfile;

System altered.

and check the values with

SQL> show parameter target;

Scope can be just "memory" for the current uptime, "spfile" or "both"; I pick the spfile as I will be restarting:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

That, however, was too simplistic:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 13104M

It is not hard to revert, but in my opinion also not immediately obvious how to get the memory adjustment through, so I will post what I did below and the senior posters can correct/shred me. In either case I think it is nice to have it in one place here.

Answer 1

The problem was that when the database was installed, conflicting other parameters were set. So I needed to export those from the "spfile" to a "pfile", back them up, edit them, test them and import from the pfile to the spfile:

SQL> create pfile='some/file/path' from spfile;

File created.

Then I set the conflicting parameters to zero:

...
--*.pga_aggregate_target=3270m
*.pga_aggregate_target=0m
...
--*.sga_target=9811m
*.sga_target=0m
...

I then try starting up again:

SQL> startup pfile='some/file/path';

ORACLE instance started.
...

And check the memory_target and memory_max_target parameters as above. Once happy with things, to keep the settings I did:

SQL> create spfile from pfile='some/file/path';

File created.

Answer 2

To answer your original question in title:

Starting with 12c, one can easily limit both SGA and PGA. SGA could be limited in earlier releases also by sga_max_size or other parameters from which the database calculated sga_max_size.

The problematic part was usually the PGA, with no simple limit value just some workarounds, but that can be now easily limited by using the pga_aggregate_limit parameter.

Answer 3

The value would always get set to 6576M, no matter what value I set for memory_max_target (1G, 1050M, 2GB, etc.). It took me too long to notice I had to also lower the pga_aggregate_target and sga_target. The default values for the two were set at (4928, 1639) which adds up to value that was set beyond what I wanted (4928 + 1639= 6567).

Finally got the desired result of 1GB memory_max_target. Had to change all 3 properties.

• SETTING: memory_max_target (1GB; 1000MB <== this is my goal), sga_target (74.9% = 749), pga_aggregate_target (24.9% = 249) <== these must also be changed.

SQLPlus Configuration

Open command line and run the commands to enter SQLPLUS as sysdba.

sqlplus / as sysdba

show parameter target

ALTER SYSTEM SET pga_aggregate_target = 249M SCOPE=SPFILE;

ALTER SYSTEM SET sga_target = 749M SCOPE=SPFILE;

ALTER SYSTEM SET memory_max_target = 1G SCOPE=SPFILE;

shutdown immediate

startup

These articles were helpful. https://mrochadba.wordpress.com/2014/08/27/memory-target-oracle-11g/ and https://stackoverflow.com/questions/20395506/reduce-memory-usage-from-16gb-to-8gb-oracle

ORIGINAL (show parameter target)

NAME                                 TYPE        VALUE

archive_lag_target                   integer     0

db_big_table_cache_percent_target    string      0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 0

memory_target                        big integer 0

parallel_servers_target              integer     80

pga_aggregate_target                 big integer 1639M

sga_target                           big integer 4928M

target_pdbs                          integer     9

NEW (show parameter target)

NAME                                 TYPE        VALUE

archive_lag_target                   integer     0

db_big_table_cache_percent_target    string      0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 1G

memory_target                        big integer 0

parallel_servers_target              integer     80

pga_aggregate_target                 big integer 249M

sga_target                           big integer 752M

target_pdbs                          integer     2

Answer 4

You already found out how to solve the problem, but here is the way one can solve such errors.

Error messages can be found in the "Oracle Database Error Messages" manual. There you can read

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least stringM

Cause: The specified value of MEMORY_TARGET was less than the sum of the specified values for SGA_TARGET and PGA_AGGREGATE_TARGET.

Action: Set MEMORY_TARGET to at least the recommended value.

I Think this explains very clearly the problem.

The same explanation you can read if you type

$ oerr ora 838
00838, 00000, "Specified value of MEMORY_TARGET is too small, needs to be at least %sM"
// *Cause: The specified value of MEMORY_TARGET was less than the sum of the
//         specified values for SGA_TARGET and PGA_AGGREGATE_TARGET.
// *Action: Set MEMORY_TARGET to at least the recommended value.

on your oracle machine.

In the manual Oracle Database Reference all parameters are described and you can read

MEMORY_TARGET should be set higher than or equal to the sum of the current sizes of the SGA and PGA

Of course there are other memory related parameters that must obey some constraints, e.g. if you set SGA_MAX_SIZE it must be not larger than MEMORY_MAX_TARGET and not smaller than SGA_TARGER.

One thing you should keep in mind is that oracle always reserves SGA_MAX_SIZE memory when you start it. If SGA_MAX_SIZE is not set but MEMORY_MAX_TARGET is, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET.

So in your example Oracle always reserves a 8G memory block when you start it, even if your MEMORY_TARGET is 4G. So if you are low on memory on your database server you should set the MEMORY_MAX_TARGET paramter carefully.