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:
- 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).
- 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.