Oracle Database 11.2.0.2 Swap Usage on RHEL 5.1

We are having a database server where we have Oracle Database 11.2.0.2 being used for Oracle Business Intelligence. Every Night, we have an ETL job scheduled which runs for around 5 hours pulling database from various other Applications and inserting into the BI database. This server is actually having total of 3 databases.

Server is having total of 252 G of RAM (A Lot).

Inspite of that, from last few days, I noticed that during the ETL run, server consumes almost all the SWAP memory that is available and we have 35 G of SWAP.

When I checked the free memory on the server, we were still having lots of free memory on the server as shown by “free -m” (a lot was free under buffers/cache). So really worry was why Oracle is being allocated memory out of SWAP.

SGA for the BI database is 70G and we are NOT using AMM. We are not using Hugespages as of now. Problem was not there until few days back when we had actually added one more database to this server with SGA of 30G.

I decided to have OSWATCHER captures the data during the ETL. One thing that I noticed was that during the ETL, as the memory need for the Oracle database increases, the amount of Paging activity really shoots up. Pagetable size was around 2G.

Over a discussion with one of Linux Admin, he pointed out that, there is a tendency of Linux to use swap when paging activity goes instantly higher and Server treat it as if whole of the memory would be consumed and to be on safer side, server allocates memory from Swap so server does not go out of memory at any point. Though I really doubt this theory and really believe it can be some sort of bug or something on the server side, but took it as a point where I can actually avoid paging activity. Enabling Huge pages was not possible because it would have require lot more testing on both DB side and Server side.

I did the following things then..

1. Changed “max locked memory” on server to 100G (Earlier it was 50G).
2. Changed “oracle soft memlock” and “oracle hard memlock” to 100G
3. Increased “shmall” kernal parameter to higher value (Calculated it as per the RAM/memory size of the server).
4. Set lock_sga=true on the database instance (To ensure that memory is locked/reserved for Oracle Database instance).

After making these changes, what we saw was consumption of SWAP reduced to 4G from 35G and 4G that is still used is for another DB instances that we have on this server.

So I still believe I may be missing something in my understanding here, but I am sure that Amount of paging make server opt for SWAP memory although server may have sufficient memory available as free.