Oracle memory structures by example

====================

Oracle AAM

====================

Was introduced in Oracle 11.
Oracle Reference for ASMM

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. 
To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). 
The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). 
The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high.
Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting the target memory size too low

In short:When using AMM (Automatic Memory Management) feature of Oracle, one is setting only the MEMORY_TARGET parameter, and letting Oracle handle the memory allocation to the various internal components (SPA and PGA).

====================

Oracle ASMM

====================
Was introduced in Oracle 10.

When using ASMM (Automatic Shared Memory Management) feature of Oracle, one is setting only the SGA_TARGET parameter, and letting Oracle handle the internal SGA memory allocation to the various parts of SGA.

=====================
Example working with AMM
=====================

Setting MEMORY_TARGET

ALTER SYSTEM SET MEMORY_TARGET = 2000M SCOPE = SPFILE;

Then need to restart the instance.

Setting MEMORY_MAX_TARGET 

ALTER SYSTEM SET MEMORY_MAX_TARGET = 2000M SCOPE = SPFILE;

- If it value is not specified, it defaults to MEMORY_TARGET.

- If it value is larger than MEMORY_TARGET - initial allocation is to size defined by MEMORY_TARGET, and can grow to a value set by MEMORY_MAX_TARGET.

- If it value is smaller than MEMORY_TARGET - you are in (not so) deep shit.

The Oracle Instance would not start, after changing the parameter and reboot.

  See below the steps to do in this case.

======================
Example working with AAMM

======================

Set SGA_TARGET to a nonzero value in the initialization parameter file (pfile). 
And set DB_CACHE_SIZE and SHARED_POOL_SIZE to zero, so Oracle would auto allocate memory to these SGA internal memory areas.
If any value is set to any auto tuned pools after switching to ASMM, that value would be a lower memory threshold for that pool.

SQL> ALTER SYSTEM SET SGA_MAX_SIZE=320M SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET SGA_TARGET=160M SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=0;
System altered.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=0;
System altered.



Using ASMM

V$SGA_TARGET_ADVICE

V$SGA_TARGET_ADVICE view gives information that one would need when estimating the size for SGA_TARGET parameter.
It lists SGA_TARGET options, and the effect on DB performance, i.e. on physical reads.
For example:

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
-------- --------------- ------------ ------------------- -------------------
120                 0.75          421                   1               26042
160                 1             421                   1                8223
240                 1.5           421                   1                7340
280                 1.75          421                   1                7340
200                 1.25          421                   1                7340
320                 2             421                   1                7340

====================

Memory target Tables

====================
V$SGA
V$SGAINFO
V$MEMORY_DYNAMIC_COMPONENTS
V$PGASTAT

====================

Memory target SQLs

====================

SELECT name, bytes/1024/1024 as MB 

FROM V$SGAINFO 

WHERE name LIKE 'Maximum SGA%' OR name LIKE 'Free SGA%';

NAME                                 MB

------------------------------------ ----------

Maximum SGA Size                     2038

Free SGA Memory Available            704

SELECT component, current_size/1024/1024 AS SIZE_MB

  FROM V$MEMORY_DYNAMIC_COMPONENTS  

 WHERE component LIKE '%Target%';

COMPONENT                            SIZE_MB

------------------------------------ ----------

SGA Target                           1344

PGA Target                           704

SELECT V$MEMORY_DYNAMIC_COMPONENTS.current_size/1024/1024 AS SIZE_MB 

  FROM V$MEMORY_DYNAMIC_COMPONENTS 

 WHERE V$MEMORY_DYNAMIC_COMPONENTS.component = 'shared pool';

   SIZE_MB

----------

       352

                  

From the above, we can summarize the following:

MEMORY_TARGET: total was 2048 Mb , or 2GB. 

SGA has been allocated 1344MB

PGA has been allocated 704MB.

1344+704=2048.

Out of SGA total of 1344MB, 352MB were allocated to the shared_pool.

===============================================================

Additional commands to check Oracle Memory.

===============================================================
SQL> SELECT * FROM V$SGA;
NAME                      VALUE
------------------        ----------
Fixed Size                  1247780
Variable Size             124319196
Database Buffers           41943040
Redo Buffers                 262144

To sum up the SGA size:
SELECT SUM(value)/1024/1024 AS Mb 
  FROM V$SGA;


SELECT component, current_size
  FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT              CURRENT_SIZE
---------------------- ------------
shared pool                92274688
large pool                  8388608
java pool                   8388608
streams pool               12582912
DEFAULT buffer cache       33554432
KEEP buffer cache           4194304
RECYCLE buffer cache        4194304


sho sga
Total System Global Area 1586708480 bytes
Fixed Size 2228744 bytes
Variable Size 956304888 bytes
Database Buffers 620756992 bytes
Redo Buffers 7417856 bytes

sho parameter memory
NAME              TYPE        VALUE
----------------- ----------- -----
memory_max_target big integer 1520M
memory_target     big integer 1520M

ipcs -m | grep oracle
0x00000000 1230929923 oracle 640 4096 0
0x00000000 1230962698 oracle 640 4096 0
0xc60e6588 1230995467 oracle 640 4096 0

===============================================================

Oracle Instance would not start, after changing MEMORY_TARGET.

===============================================================

alter system set memory_target = 1024M scope = spfile;

Connected to an inactive instance.
SQL>startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>alter system set memory_max_target = 2G scope = spfile;
ORA-01034: ORACLE not available

Steps in this case:

Find spfile and pfile

These should be under $ORACLE_BASE/admin/SID/pfile/

sqlplus / as sysdba

sql> create pfile='/full/path/to/file/myinit.ora' from spfile='/full/path/to/file/spfileSID.ora';

create pfile='/software/oracle/admin/igt/pfile/pfile_20201201_initigt.ora' from spfile='/software/oracle/admin/igt/pfile/spfileigt.ora';
File created.

Manually backup '/full/path/to/file/spfileSID.ora' to '/full/path/to/file/spfileSID.ora_YYYYMMDD'

Manually edit MEMORY_TARGET and/or MEMORY_MAX_TARGET so that MEMORY_MAX_TARGET = MEMORY_TARGET.

memory_max_target = nM
memory_target = mM

For example:

Before
*.memory_max_target=31457280000

*.memory_target=1073741824

After

*.memory_max_target=31457280000

*.memory_target=4294967296

sql> startup pfile='/full/path/to/file/myinit.ora';

sql> create spfile='/full/path/to/file/spfileSID.ora' from pfile='/full/path/to/file/myinit.ora';

From now on, you should be able to startup the database as usual.

sql> shutdown immediate;
sql> startup;

Appendix

Using Automatic Memory Management