7

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.

5

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.
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.