Blog 
Automatic Memory Management – it’s great isn’t it?

As you might have read in my previous blogs I did some tests with Oracle 10g and 11g on Linu 6 Update 1 – and it works even though there is no official support from Oracle. Due to a presentation for the German Oracle User Group (DOAG) I did some tests with Automatic Memory Management and in this blog I like to share some experiences with you.

SPFILE in ASM

As you probably know there is one single parameter  in Oracle 11g to control the memory usage for both SGA and well as PGA: memory_target. So for my tests I tried to give both of my instances in the RAC environment 2 GB on memory instead of 1 GB.

SQL> ALTER SYSTEM SET memory_max_target = 2G scope=spfile sid='*';

System altered.

% srvctl stop database -d ORASE
% srvctl start database -d ORASE
PRCR-1079 : Failed to start resource ora.racvm1.db CRS-5017: The resource action "ora.racvm1.db start" encountered the following error: ORA-00845: MEMORY_TARGET not supported on this system .
For details refer to "(:CLSN00107:)" in "/u01/app/grid/11.2.0/grid/log/obelix/agent/crsd/oraagent_oracle/oraagent_oracle.log".
STARTUP WARNING: You are trying to use the MEMORY_TARGET feature.
This feature requires the /dev/shm file system to be mounted for at least 2097152000 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1866305536 and used is 208990208 bytes. Ensure that the mount point is /dev/shm for this directory. 
memory_target needs larger /dev/shm.

Yes you know: it’s the new shared memory structure used with Oracle 11g (see chapter 2). Some more specific information can be found in the alert-file.

WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 2147483648 bytes. 
/dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. 
Current available is 1866305536 and used is 208990208 bytes. Ensure that the mount point is /dev/shm for this directory.

Unfortunately I forgot that ASM has an SGA as well so is using approx 200 MB on shared memory. I now have to choices to go ahead:

1. decrease memory_max_target for my RAC instance so the total of both ASM and RAC instance fit onto the current shared memory (208990208 Byte).
2. Increase the Shared-Memory area.

For the first point I was really in trouble – why? Because how can you change a server parameter without starting the instance? As the spfile for the RAC database is stored inside ASM “CREATE pfile= … FROM spfile” doesn’t work. Nor is it possible to directly edit the spfile. If you have some hints how to read or change values for an spfile inside ASM without starting the instance please let me know. Here’s my first recommendation: 1. Never make changes to the spfile without copying it to a readable and writable init.ora:

SQL> CREATE pfile='/tmp/initRAC.ora' FROM spfile;

2. Change the parameter for one instance at a time. Because this allows you to startup the remaining instance and revert the changes. So my only choice was to increase the shared memory.

Posix-Style Shared Memory Management

To use memory_target and memory_max_target the operating system has to switch to a Posix-Style shared memory management instead of the old System-V style to manage both the SGA as well as the PGA with one single structure. To use 2 GB memory for my RAC instances I changed the parameter for tmpfs in /etc/fstab accordingly.

shmfs      /dev/shm    tmpfs   size=3g        0 0

It doesn’t matter that I used 3 GB as this is only a upper limit for the shared memory. Okay so let’s reboot the server to use the new shared memory value…

# df -k Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg_..._root      26391624   16380736   8670268  66% /
tmpfs                         2026656    1267368   759288   63% /dev/shm
/dev/sda1                      495844      50942    419302  11% /boot

After the reboot the size of the shared memory is reset to 2026656 KB – so approx. 2 GB again. What happened? As I’m running an unsupported environment I had no chance to find a solution at Oracle support – but I found some hints in the Linux forum. With Linux 6 there is one minor difference in the file /etc/rc.d/rc.sysinit. To allow the shared memory go grow accordingly to the entry in /etc/fstab you need to remove the “-f” for the mount command:

Old: mount -f /dev/shm >/dev/null 2>&1
New: mount /dev/shm >/dev/null 2>&1

And now it works! After the reboot the correct value for shm is used and my instances were restarted again.

3.  Memory usage of various caches

The parameter memory_max_target is used to define the upper limit for the shared memory but not the real size. While starting the instance the value the Oracle kernel checks if that amount of memory is available but for the allocation memory_target is used instead. So you can increase and decrease memory_target as long as it fits into memory_max_target without restarting the instance.

SQL> ALTER SYSTEM SET MEMORY_TARGET=2000M sid='ORASE1';

In the next step the various caches like shared pool or buffer cache can be resized automatically or manually. Let’s look into v$sgainfo for some details:

SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2229944 No
Redo Buffers                        5173248 No
Buffer Cache Size                 352321536 Yes
Shared Pool Size                  385875968 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 2087780352 No
Startup overhead in Shared Pool   137554960 No
Free SGA Memory Available        1308622848

So the “new” area is marked as “Free SGA memory” – that’s what I expected. After some more tests I realized the neither the shared pool nor the buffer cache is increasing. So I’m going to increase the buffer cache manually.

SQL> ALTER SYSTEM SET db_cache_size=900M sid='ORASE1';
 ALTER SYSTEM SET db_cache_size=900M sid='ORASE1'
*
ERROR at line 1: ORA-02097: parameter cannot be modified because
specified value is invalid ORA-00384: Insufficient memory to grow cache

Why? “Free SGA Memory Available” in v$sgainfo is misleading as it isn’t free memory but the PGA.

SQL> SELECT * from v$pgastat
     WHERE name LIKE '%target parameter%';
NAME                              VALUE      UNIT
--------------------------------  ---------- ------------
aggregate PGA target parameter    1308622848 bytes

But how can I decrease the PGA? Let’s try this:

 SQL> alter system set pga_aggregate_target=200M;

Nice command but without any positive impact as with automatic memory management (also with sga_target in Oracle 10g) the defined values are lower limits and not target values. So in this case the PGA cannot decrease below 200 MB but as the current value is approx. 1.3 GB it makes no difference. So from what I know the only way to increase the buffer cache is to increase the whole SGA first.

SQL> ALTER SYSTEM SET sga_target=1500M sid='ORASE1';

System altered.

SQL> SELECT * FROM v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2229944 No
Redo Buffers                        5173248 No
Buffer Cache Size                1140850688 Yes
Shared Pool Size                  385875968 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 2087780352 No
Startup overhead in Shared Pool   137554960 No
Free SGA Memory Available         520093696

As you can see the additional 500 MB are allocated for the buffer cache – hurray.

4. Tips

In addition to the already given recommendation to make a backup before changing server parameters I would recommend to set a lower limit for the most important caches (PGA, buffer cache and shared pool). This eliminates negative impacts of poor written software (e.g. not using bind variables). In one of my recent projects the PGA had a size of 3 GB, shared pool was approx. 1.5 GB but only 500 MB were left for the buffer cache. So here is one example how to set the limits for the caches:

memory_max_target    = 3G
memory_target        = 2G
db_cache_size        = 1G
shared_pool_size     = 500M
pga_aggregate_target = 200M

The total memory is 2 GB where the buffer cache has a minimum of 1 GB, the shared pool is minimum 500 MB and the PGA 200 MB. So approx. 300 MB can be “automatically” allocated and deallocated according to the application. Differences between memory_target and memory_max_target are only useful if you are running more databases on one single server. With the adjustment of memory_target one instance can use more memory (e.g. for a batchrun) for a short period of time while other instances can use the same memory for the rest of the time. I would appreciate comments and additional hints – especially how to make changes to and spfile in ASM if you are not able to start the instance.