Home > Oracle RAM usage > Empirical Oracle 11g Process RAM Usage

Empirical Oracle 11g Process RAM Usage

As we were starting to get ready for 10g to 11g database upgrade, I got curious – how much more RAM the 11g may require ? I have heard a story from one of my colleagues about a busy system which ran into severe paging after 9i to 10g upgrade. It turned out that on average every 10g database server process was taking more memory than 9i. This extra memory was private process memory, different from SGA and PGA. With several thousands database connections the upgraded system ran into RAM shortage of several GB. They ended up with having to buy more RAM in a hurry.

How can I estimate the required RAM ahead of time?

Many Oracle docs mostly deal with SGA and PGA sizing. The usual prescription is this: leave 20% to the OS kernel and other system processes, and give the 80% to SGA (and PGA). Apparently, this is not the whole picture and we have to account for Oracle processes private memory.

So the formula becomes:

Oracle RAM = SGA + PGA + PM

How do we estimate PM ?

I will be using empirical approach. I will start a database session and run OS utilities on the server process.

Private Oracle process memory can be used for many things : to hold pl/sql variables, sql cursors, various session-specific parameters etc. One big consumer will be sort areas. Now you might ask – is not sort areas already accounted for as PGA ? And you are right, it is. We need to be careful not to double count sort areas. So for the above formula to make sense, the PM should not include sort areas. For this reason I will just connect my session to the database and then do nothing. This way the session does not use sort area. The session also does not yet allocate memory for anything application-specific. This session will actually give me a base benchmark, the lowest RAM a database session  can consume. Multiplying this number by a number of database sessions will give me ability to calculate absolute minimum memory necessary just to connect my application to the database.

 The measurements were taken on following virtual machine :

Windows 7  Home Premium 64-bit SP1+VMware Player 3.1.4 + Oracle Enterprise Linux 6.1 64-bit+ Oracle Database 11g Enterprise Edition 11.2.0.2.0 – 64 bit

Method 1

top -p 
     PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
   2884 oracle    20   0 1256m  43m  39m S  0.0  1.7   0:00.09 oracle

In the output above RES shows Resident size and SHR shows portion of Resident size which may be shared with other processes (code, libraries)
This gives Private process memory = RES – SHR = 43-39=4m on Oracle 11g
Method 2

cd /proc/
grep Private_Dirty smaps | grep -v "0 kB" | awk 'START {s=0} {s=s+$2} END{print s}'
  3956   (~ 4m on 11g)

Method 3

ps -p -o size=
3944   (~ 4m on 11g)

Method 4

pmap -x 2884
2884: oracleemrep (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 180228 21852 0 r-x-- oracle
000000000b601000 1820 348 72 rwx-- oracle
000000000b7c8000 300 204 196 rwx-- [ anon ]
000000000cf06000 396 356 356 rwx-- [ anon ]
0000000060000000 4 0 0 r-xs- ora_emrep_557069_0
0000000060001000 4092 360 144 rwxs- ora_emrep_557069_0
0000000060400000 4096 8 8 rwxs- ora_emrep_557069_1
...
pmap -x 2884 | grep rwx- | awk 'START{s=0}{s=s+$3}END{print s}'
4036 (~ 4m on 11g)

As we can see, the minimum private RAM is about 4M per oracle server process.
With 5000 db sessions this translates to a sizable 20G RAM on top of SGA, PGA and OS usage. And this is just for starters, before application even starts doing anything !

By comparison, in 10g minimum per-process private memory was about 2.5M This means that if you had 5000 db sessions, after 11g upgrade you would need additional 5000*1.5M = 7.5G RAM.

Advertisements
Categories: Oracle RAM usage
  1. May 7, 2013 at 8:04 am

    You’ve made some decent points there. I checked on the net to learn more about the issue and found most individuals will go along with your views on this site.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: