Database-- Oracle, allocating/deallocating/resizing shared memory, Sga_target, Investigating Oracle shm allocation.

We know that, Oracle introduced Sga_target parameter to manage Buffer cache (DB_CACHE_SIZE), Shared pool (SHARED_POOL_SIZE),Large pool (LARGE_POOL_SIZE) and Java pool (JAVA_POOL_SIZE). It s considered as an important concept in Automatic Memory Management (AMM)

As it is well known by the most of the community, I will not go in details about this parameter, except one thing = Reducing the memory usage using sga_target parameter.  

Actually, the reason that made me write this post is, the shared memory.. I was testing some memory boundries and behaviours of the operating system in case of a shared memory deallocation.. I was working on it because the information was needed to analyze an EBS production system performance..   So, I was working on a Linux 64 bit, writing a program to allocate some shared memory memory ... 

I used SYSV compatible shmget and allocated some shared memory.. I could use shmctl to deallocate this shared memory without any problems..

I could use ipcs on Linux to display and saw memory allocation and deallocation activities.

On the other hand, I realized that I could not resize the allocated shared memory on-the-fly. It seems using shmget , you cant do this in Linux. There is no such option to resize a shared memory segment using shmget in Linux.. In Aix,there is a shm_size argument for shmget . It s for resizing. But it seems it is not available for Linux.

So, I used the following C code to allocate and deallocate 1.5G shared memory.

#include <sys/types.h>

#include <sys/ipc.h>

#include <sys/shm.h>

#include <stdio.h>

#include <errno.h>

#include <string.h>

#define SHMSZ 1589934592

int main()

{

int shmid;

int shamerman;

key_t key = 5690;

shmid = shmget(key,SHMSZ,0666 | IPC_CREAT);

fprintf(stdout,"%d",shmid);

shmctl(shmid,SHM_SIZE,589934592);

sleep(3);

if ((shamerman=shmctl(shmid,IPC_RMID,0)) !=0)

{

fprintf(stderr,"ERROR with shmctl (%d: %s)\n", (int)(errno), strerror(errno));

return 1;

}

return 0;

}

I could see the shared memory segment that I allocated in ipcs output..
This server was an Oracle Database server, and I saw the shared memory segments allocated by the running Oracle Database , too. Oracle Database allocated approx 7*2 gb(14gb in total) shared memory segments.. I checked its SGA to be sure.. Both, the sga_target and sga_max_size parameters were set to 14gb. On the other hand, the kernel parameter shmmax was set to 2gb.. That was why Oracle allocated the 14 gb memory segment in 7 pieces..  (Note that : Shmmax describes maximum size of a shared memory segment)

Lets come back to the our actual topic; I was allocating and deallocating the shared memory segments.  I could do it with shmget. But one thing that I cant do with it , is that I could not resize a shared segment on-the-fly. Ofcourse, I could deallocate and reallocate a segment with a new size, but this is not satisfying..

To able to resize a shared memory segment, I decided to use shm_open(create or gain access to a shared memory segment) and ftruncate(truncate a file to a specified length), posix memory operation functions..
So shm_open uses a file based approach and technology for shared memory operations. It basically uses tmpfs,/dev/shm to function. Because it s using a file based architecture, there is an opportunity to use the ftruncate for resizing the file, which is actually tied to a shared memory segment.. By doing this, a shared memory segment can be resized on-the-fly.

I used following C program , which uses shm_open and ftruncate functions to allocate and resize the shared memory segment ;
Note that : this program should be compiled using gcc -lrt ,  by using -lrt argument posix support is added to the gnu c compiler.

#include <sys/types.h>

#include <sys/ipc.h>

#include <sys/shm.h>

#include <stdio.h>

#include <errno.h>

#include <string.h>

#include <sys/mman.h>

#include <sys/stat.h>        /* For mode constants */

#include <fcntl.h>

int main()

{

int *result = NULL;

int integerSize = sizeof(300000);

int descriptor = shm_open("/shmerman",O_CREAT | O_RDWR, S_IRUSR | S_IWUSR);

fprintf(stderr,"ERROR with shmctl (%d: %s)\n", (int)(errno), strerror(errno));

fprintf(stderr,"%d",descriptor);

ftruncate(descriptor, integerSize);

fprintf(stderr,"ERROR with shmctl (%d: %s)\n", (int)(errno), strerror(errno));

result = mmap(NULL, integerSize, PROT_WRITE | PROT_READ, MAP_SHARED, descriptor, 0 );

sleep(10);

fprintf(stderr,"SIMDI DEALLOCATE EDIORUM");

sleep(10);

ftruncate(descriptor, 600000);

return 0;

}

This time , I checked the file named shmerman, which was located under /dev/shm and saw that its size was 300000b after created and it jumped to 600000b after 10 seconds..(sleep 10)
Note that: You cant see the shared memory segment allocated by shm_open in ipcs output. You can check the segments from the /dev/shm filesystem.

In 11g , we have memory_target and I believe Oracle uses a similar method, that I just mentioned above,  to manage the shared memory . As you know; to be able to use above method, you need to have a /dev/shm..  So /dev/shm is required for using memory_target in Oracle.
Beyond that,  I think and read maybe, that Oracle uses /dev/shm to pass segments from SGA(shared memory) to PGA (process memory).. I think it just gives back the shared memory back to OS ,and OS gives it to the processes in case they needed. Giving back the unused memory should be enough for managing the PGA,as is not a limit and it s is not a fixed memory area resides in memory..

So far so good.. On the other hand, 10g Oracle does not use memory_target , naturally it does not use a /dev/shm based memory access..
ipcs output shows that it uses shmget to get the needed shared segments... Also , I have sent a mail to Tanel Poder regarding this topic, and he said by using strace in Oracle startup; shmget call can be seen..

Following was taken in startup process of an Oracle 10gR2 database.

open("/proc/sys/kernel/shmmax", O_RDONLY) = 2   --> It reads the shmmax to obtain the max segment size.
[pid 12584] shmget(2505607756, 6444548096, IPC_CREAT|IPC_EXCL|SHM_HUGETLB|0600) = -1 ENOMEM (Cannot allocate memory)  --> It tries the Hugetlbs and can not get them as it s not configured.
[pid 12584] shmget(2505607756, 6444548096, IPC_CREAT|IPC_EXCL|0660) = 557059, then it uses small pages to allocate the 6gb sga , which shmid= 557059

shmat(557059, 0x7fbfffdf01, 0) = ?  --> it attaches to the allocated shared memory

It gets the shared memory using shmget call;

Here is the ipcs command output; shmid in the ouput is the same as it s returned from shmget..(557059)

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0xb92f91b4 524290     proddb    660        526385152  27                      

0x95588a4c 557059     proddb    660        6444548096 12                      

0x00000000 360452     proddb    644        151552     2          dest         

0x00000000 393221     proddb    644        122880     2          dest         

0x00000000 425990     proddb    644        790528     2          dest 

So it s clear Oracle 10g uses shmget to allocate the shared memory segments..
The question is that , how it can resize them?
As we know we can decrease the size of sga on-the-fly using sga_target parameter.

I tested and analyzed it to satisfy my curiosity;

1) sga_target = sga_max_size

First I tested this functionality in a Oracle Database, which has 6 gb sga capacity and configured to use all the 6gb of it. (sga_max_size = 6gb, sga_target=6gb)
There parameter lock_sga=FALSE ..  Note that  LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, it prevents paging.. I checked this parameter because. It can mislead us..

After the startup of the instance; I see that shared memory segment size is 6gb. So, it s clear that Oracle allocated 6 gb of shared memory (note that sga_target is 6gb too)

Here is the ipcs command output , just after startup;

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status          
0x95588a4c 557059     proddb    660        6444548096 16                    
0x00000000 360452     proddb    644        151552     2          dest      
0x00000000 393221     proddb    644        122880     2          dest      
0x00000000 425990     proddb    644        790528     2          dest

Look at the following Oracle process.. It has 6325 of virtual memory.. 6 gb + some mbytes.. So Oracle process is prepared to be is able to access 6 gb memory at the present moment.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                        
12606 proddb    16   0 6325m 1.3g 1.2g S  0.0  7.1   0:15.36 oracle

Output of free command show approx 18007-(17064+227)
free -m
             total       used       free     shared    buffers     cached
Mem:         18049      18007         41          0        227      17064
-/+ buffers/cache:        716      17333
Swap:         8001          0       8000

Lets look at the Shared memory components of Oracle.It has a sga configured to 6 gb.
It has 5301600256 bytes 's of buffer cache.  1056964608 bytes of Shared pool and etc..

SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  2093544 bytes
Variable Size            1124077080 bytes
Database Buffers         5301600256 bytes
Redo Buffers               14680064 bytes

SQL> select * from v$sgainfo;

NAME                                  BYTES RES

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

Fixed SGA Size                      2093544 No

Redo Buffers                       14680064 No

Buffer Cache Size                5301600256 Yes

Shared Pool Size                 1056964608 Yes

Large Pool Size                    16777216 Yes

Java Pool Size                     16777216 Yes

Streams Pool Size                  33554432 Yes

Granule Size                       16777216 No

Maximum SGA Size                 6442450944 No

Startup overhead in Shared Pool   167772160 No

Free SGA Memory Available                 0

So everything is just like expected, here.. we use all the sga , and Free SGA Memory Available =0

2) sga_target < sga_max_size

Next I tested this functionality in a Oracle Database, which has 6 gb sga capacity but configured to use 4gb of  it. (sga_max_size = 6gb, sga_target=4gb)

So here is the  ipcs ouput just after restart.. Oracle still allocates 6gb Sga..  But this does not mean, that this memory is  initialized and taken from physical memory.

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status                    
0x95588a4c 589827     proddb    660        6444548096 14                    
0x00000000 360452     proddb    644        151552     2          dest      
0x00000000 393221     proddb    644        122880     2          dest      
0x00000000 425990     proddb    644        790528     2          dest      

Oracle processes still maps more than 6gb memory.. VIRT column supplies this information.. Virtual memory is a memory map that can be point to physical memory, or a swap disk space, or a memory "to be allocated".

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                        
14231 proddb    16   0 6284m  38m  34m S  0.0  0.2   0:00.06 oracleDEV (LOCAL=NO)                                                                                                            
$ free -m
             total       used       free     shared    buffers     cached
Mem:         18049      18016         32          0        229      17048
-/+ buffers/cache:        737      17311
Swap:         8001          0       8000

       
SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  2093584 bytes
Variable Size            2986348016 bytes
Database Buffers         3439329280 bytes
Redo Buffers               14680064 bytes

SQL> select * from v$sgainfo;

NAME                                  BYTES RES

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

Fixed SGA Size                      2093584 No

Redo Buffers                       14680064 No

Buffer Cache Size                3439329280 Yes

Shared Pool Size                  771751936 Yes

Large Pool Size                    16777216 Yes

Java Pool Size                     16777216 Yes

Streams Pool Size                  33554432 Yes

Granule Size                       16777216 No

Maximum SGA Size                 6442450944 No

Startup overhead in Shared Pool   134217728 No

Free SGA Memory Available        2147483648

As you see above, Oracle allocated 6gb Sga. Buffer cache size is 3.2Gb. , the variable size is 2.78Gb..
It looks like it is same whether we use sga_target = 4gb or sga_target = 6b.. But it is not the same.
Look at Free SGA Memory Available .. It is 2147483648, which is 2gb.

3) Lets reduce the sga_target value from 4gb to 2b of this running instance ..
(sga_max_size=6gb, sga_target = 2gb (reduced from 4gb, without restarting the db)

SQL> alter system set sga_target=2g;            
System altered.

ipcs output shows the same; 6gb shared memory segment is stil allocated.

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status          
0x95588a4c 589827     proddb    660        6444548096 15                    
0x00000000 360452     proddb    644        151552     2          dest      
0x00000000 393221     proddb    644        122880     2          dest      
0x00000000 425990     proddb    644        790528     2          dest

Top command shows the same, VIRTUAL Memory of a given Oracle process is approx. 6gb.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                        
13611 proddb    16   0 6284m  20m  16m S  0.0  0.1   0:00.03 oracle      

Output of free command shows similar amount of free memory..

             total       used       free     shared    buffers     cached
Mem:         18049      18009         40          0        238      17032
-/+ buffers/cache:        738      17310
Swap:         8001          0       8000


SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  2093584 bytes
Variable Size            5133831664 bytes
Database Buffers         1291845632 bytes
Redo Buffers               14680064 bytes

SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2093584 No
Redo Buffers                       14680064 No
Buffer Cache Size                1291845632 Yes
Shared Pool Size                  771751936 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                  33554432 Yes
Granule Size                       16777216 No
Maximum SGA Size                 6442450944 No
Startup overhead in Shared Pool   134217728 No
Free SGA Memory Available        4294967296

As we see;  ipcs ,top -p, free -m and oracle's sga outputs are similar(they point to 6gb shared memory), but Free SGA Memory Available showed in v$sqainfo display 4gb free, as expected..

Not seeing the unused portion of the shared memory in ipcs and top is not an issue. These are tools, so they can be coded that way. No need to investigate..

But, following are the questions;
How can Oracle make use of the advantages brought by sga_target? Actually, how does OS threat these allocated but not used shared memory blocks..

I will try to answer these questions in a few days.