Intermediate SQL Color Coded SQL, UNIX and Database Essays


How ORACLE Uses Memory on AIX. Part 2: SGA

In the previous post we discussed memory usage by ORACLE processes and, if you remember, it took us some effort to get to their actual memory usage.

In this post we are going to talk about the other large memory area that ORACLE instance uses – System Global Area or SGA.

In many respects, finding out SGA memory usage is going to be simpler as we have to deal with only one large entity (AIX shared memory segment) instead of many small memory chunks in separate ORACLE processes. But as we will see, this process is still rather “involved” for AIX has a few tricks up its sleeve for shared memory as well.

How much memory is used by ORACLE SGA. Really …

Let’s start with a simple question.

What happens if we try to allocate 12 Gb SGA on a machine with only 8 Gb of physical memory?

There are three typical answers to this question:

  1. ORACLE instance would NOT start as ORACLE would NOT find enough memory for SGA
  2. ORACLE instance WOULD start, but performance would suck as the system will be heavily paging most of the time
  3. ORACLE instance WILL start and nothing of significance will happen to ORACLE or AIX performance

To most people, answer #2: Instance will start, but the system will page is the most logical. After all, this is what virtual memory management is about – letting much bigger workloads run on limited physical resources. But at the same time, there must be a penalty for using (much) more memory than we have and that penalty is: paging …

Still, let’s not rush the answer and rather run this experiment and see for ourselves.

AIX SGA Bigger Than Physical RAM

Wow! According to the results above, answer #3 seems to be correct … But why ? In other words, has ORACLE lied to us by misrepresenting SGA size? Or has AIX lied to us by not reporting obvious paging ?

Well, of course, the answer is – neither and the real reason is, once again, comes from the operating system bag of tricks.

When a process asks AIX to allocate shared memory segment, it receives a pointer to the segment and a promise from AIX that memory will be there when the process needs it (ain’t AIX working like a good salesman here ? 🙂 ). That means that:

AIX Memory Allocation Rule

By default, AIX shared memory is NOT allocated until it is actually used

Once again, svmon will provide the insights for the real usage of memory.

In this demonstration, I’ve made some changes to the original example to illustrate this point even further. Here is the setup:

  1. The system (still) has ~ 8 Gb of physical RAM
  2. I set sga_target to 10 Gb (bigger than physical RAM)
  3. I set sga_max_size to 12 Gb (bigger than sga_target)

Let’s see what happens when we start the database (I skipped some of the resulting output to keep the example short).

SQL> show parameter sga

sga_max_size                      big integer 12G
sga_target                           big integer 10G

AIX> ipcs -bm | grep oracle
m  92274693 0xd9bf0b18 --rw-r-----   oracle      dba 12884930560

AIX> svmon -P $(ps -elf | egrep " ora_smon_${ORACLE_SID} " | grep -v egrep |
awk '{print $4}') | grep shmat

33202  70000030 work default shmat/mmap           s  39274     0    0 39274
131a6  70000001 work default shmat/mmap           s   4078     0    0  4078
f321a  70000023 work default shmat/mmap           s   2842     0    0  2842

2a2    70000009 work default shmat/mmap           s   2055     0    0  2055
1b1a7  7000002b work default shmat/mmap           s   1511     0    0  1511
3b183  7000002c work default shmat/mmap           s     16     0    0    16

bb193  7000002e work default shmat/mmap           s     16     0    0    16
631e8  70000031 work default shmat/mmap           s      1     0    0     1
7b36b  70000003 work default shmat/mmap           s      0     0    0     0
c323c  70000002 work default shmat/mmap           s      0     0    0     0

b3392  70000005 work default shmat/mmap           s      0     0    0     0

AIX> svmon -P $(ps -elf | egrep " ora_smon_${ORACLE_SID} " | grep -v egrep |
awk '{print $4}') | grep shmat | wc -l

As you can see ipcs still reports SGA shared memory segment to be 12 Gb in size, which is expected. The size, however, now matches sga_max_size (and not sga_target). So, right of the bat, we can see that sga_target is a logical (rather than physical) limit.

But svmon results are strange – why do we see multiple memory segments allocated when we really only have 1 SGA ? This requires a bit of explanation.

In AIX, virtual memory manager organizes memory into segments (which are further subdivided into pages). Each segment has a maximum size of 256 Mb and therefore, if you allocate, say 1 Gb of shared memory – AIX gives you 4 segments.

Notice that in the example above, 49 segments are allocated in total. That is: 48 (12*4) +1, where 48, again, matches allocation for 12 Gb. (+1 here is a little weird – whenever sga_max_size is set in Gigabytes (say, 12G as opposed to 12000M) – this additional segment always pops up – I’m guessing, this is the result of rounding issues when translating sga_ parameters from ORACLE to AIX).

Anyway, forgetting about rounding issue for the moment, the total size of all SGA segments should be 48 * 256 Mb = 12 Gb. But this is the requested size that, if you remember, AIX promised but may have not necessarily delivered. In other words, it will only fully materialize when all requested memory is actually used.

So, how much memory are we really using right now ? This is where other svmon numbers come in.

Let’s look at the first segment.

33202  70000030 work default shmat/mmap           s  39274     0    0 39274

It allocates 39274 small pages (s = 4K), which translates to, roughly, 159 Mb, out of possible 256. The next segment allocates 4078 pages or ~16 Mb and so on … Notice that memory allocation across segments is not uniform: it quickly drops down for further segments down the line. As you might have already guessed, this means that ORACLE does not yet need this memory and hence it has not yet been allocated.

When I summed these numbers up, I got total memory allocation of ~ 900 Mb, far smaller than the requested 12 Gb (your mileage may vary, of course).

Which explains why the system is NOT paging –it does not need to as the entire ORACLE instance memory fits into physical memory with ease … (which is further corroborated by the absence of in paging space shmat pages in svmon report (3rd column))

Also notice that, at the end, some segments do not allocate any pages, as in the example below.

7b36b  70000003 work default shmat/mmap           s      0     0    0     0
c323c  70000002 work default shmat/mmap           s      0     0    0     0

These are the segments that have been allocated beyond sga_target. In other words, when sga_max_size > sga_target, AIX allocates some metadata (segments) for ORACLE, but does not fill them up and hence memory is NOT used. Thus, regardless of what ipcs reports, setting sga_max_size beyond sga_target is a pretty safe operation that does not actually use memory (but see below!).

There is a number of caveats here as usual.

First of all, for segments below sga_target, some memory will always be allocated (even if it is one page). My guess is – ORACLE needs to touch some pages in every shared segment that it can work with and that includes all segments up to sga_target.

Second, the examples above describe the “default” system setup. The rules of ORACLE memory allocation can be adjusted both on ORACLE side and AIX side, which might change allocation picture entirely (we will discuss these changes and their implications in the next post)

Third, and this is important, remember that you set certain SGA size for a reason. And that reason is – you want ORACLE instance to work efficiently and, this is achieved, to a large degree, by having enough memory and effectively using it. That means that if you size your SGA properly – memory WILL be used eventually. Up to and including your sga_target setting. And before ORACLE 11g this memory can never be released (short of restarting the instance). So, do not assume that you can run five instances with 4 Gb SGA each on a system with 8 Gb of physical RAM without suffering the consequences … eventually 🙂

And lastly, this is not to say that you can allocate ANY amount of RAM to the instance just because that memory may not be used (and hence allocated) immediately. At some point you will hit the dreaded ORA-00064: object is too large to allocate on this O/S error and you might or might not easily get around that … My guess is: ORACLE did it mostly to discourage people from requesting 100 Tb SGAs on a 2 Gb machine … 🙂

This is, of course, all well and good – it is nice to know that memory is not used unless requested and you can enjoy plenty of resources in the system, for some time at least … But you have probably heard that sizing SGA properly from the start and potentially “pinning” it in physical memory is much better as it allows AIX to NOT start paging out the most critical memory at the most inopportune times.

In the next post, we are going to discuss ORACLE and AIX ways to do just that.

Useful Commands

# ORACLE command to show which AIX shared memory segment
# is "attached" to the instance
AIX> sysresv

# Basic information about SGA shared memory segment
# (use syresv to find out segment id or key)
AIX> ipcs –ma | grep segment_id

# The list of VMM segments (virtual segment ids) that comprise SGA segment
AIX> ipcs -bmS1 | grep segment_id

# Detailed information about individual SGA segments
AIX> svmon -S $(ipcs -bmS1 | grep segment_id | perl -pe "s/(\S+\s+){7,7}//")

# The same information, but through a different avenue
# All ORACLE instance processes attach THE SAME (SGA)
# shared memory segments.
# So, we check shmat attachments in any process (i.e. SMON) for details
AIX> svmon -P $(ps -elf | egrep " ora_smon_${ORACLE_SID} " |
grep -v egrep | awk '{print $4}') | grep shmat

# All VMM shared memory segments that belong to $ORACLE_SID
# You can download the script from the TOOLS area on this site
Comments (3) Trackbacks (0)
  1. Very good job!

  2. very useful info, wish to have a continuity on the post, as with best ways to maintain oracle instances on AIX, thanks,

Leave a comment

No trackbacks yet.