Intermediate SQL Color Coded SQL, UNIX and Database Essays

How ORACLE Uses Memory on AIX. Part 4: Having Fun with 11g Memory_target

This is going to be a long post but don’t be discouraged: most of it will involve snapshots and screen examples, so it shouldn’t be too bad …

Anyway, here is the short recap from the previous 3 posts (Part 1, Part 2, Part 3):

• ORACLE Instance Memory consists of 2 parts: process memory and shared (SGA) memory
• Process memory is a bunch of memory segments allocated in individual ORACLE processes and their collective size is (attempted to be) managed by pga_aggregate_target parameter. AIX improves process memory usage by identifying sharable segments (such as program or shared library text) and not duplicating them for each individual process.
• SGA memory is allocated as a single AIX shared memory segment (which, in reality, turns out to be a bunch of smaller VMM segments) and (in ORACLE 10g) is managed by sga_target and sga_max_size parameters. AIX, by default, helps with shared memory usage by allocating it only as needed. However, you can overwrite this behavior and force AIX to allocate all the shared memory at once and, additionally, put a “pin” on it in order to prevent paging.

If you read these descriptions carefully, you would notice that process and SGA memory, while being two parts of the same coin, are very different from each other: they are allocated by AIX differently, they are managed by ORACLE differently and they, in a sense, almost feel different.

In other words, while these two memory regions are related to each other, they are by no means close relatives: one is a big chunk of almost static memory that lives completely independently from any process and the other: an amorphous and ever changing “haze” of small memory pieces that are completely “privatized” by individual processes …

Still, memory is memory is memory and the ultimate question for every DBA is:

In other words, what we really want to know is the Total Memory that is taken by instance. And the fact that we have to (artificially) deal with two separate memory regions here brings a couple of complications …

The first complication is rather cosmetic: Obviously, dealing with one thing is easier than with two. Yet, sizing two things is still pretty straightforward (plus, it gives us more control), so we can almost dismiss it as merely an inconvenience … if not for the second complication …

The second complication is more fundamental: process and SGA memory regions have rather different purposes: SGA memory is mostly used to cache database blocks while process memory (that is manageable by ORACLE) is used for various temporary areas: sorting, hashing etc … It is unlikely that both SGA and PGA will be simultaneously used to the max at any given time. Yet they are usually both sized to the max as it is likely that they may be used to the max individually at one time or another.

I guess you see where I’m going with this – with Berlin Wall between SGA and PGA memory pools, it is quite possible that one pool will be starving while the other – only lightly used. Traditionally, this situation has been addressed by increasing physical memory of the system to fit both pools (which is a definition of waste), but perhaps there is a better way …

And a better way is indeed what ORACLE introduced with 11g – an ability to manage these two pools together and “shift” memory to the part where it is most needed.

But enough with the theory. Let’s see exactly what ORACLE did.

Let’s start with establishing a baseline – before we see how the memory is “shifted” between SGA and PGA in 11g, let’s see how it is NOT shifted in ORACLE 10g (so that we can better appreciate this new 11g feature).

Here is how we are going to do that:

Here is what SGA memory looks like before we start the test. Notice that only 227 Mb (or ~ 10%) out of requested 2 Gb SGA is allocated.

Let’s now use SGA to the fullest. We could have done it by running a full table scan on our table, but, in this case, we have an even easier way to fill up the SGA - collect table statistics:

After statistic collection conveniently filled up database buffer cache, here is what our SGA looks like:

Notice that SGA is now is using ~ 80% of the requested capacity (the unallocated portion is reserved for things other than buffer cache).

Ok, now let’s test the sorting. To maximize requirements for PGA, we are going to start 10 parallel sessions, each of which will be doing a full sort of the table:

And after a few minutes of work, here is what SGA and PGA memory looks like:

Notice that while PGA grew up to the 2 Gb limit (and slightly beyond that), the allocation of SGA did not change a bit. If anything it actually grew by a few additional Mbs.

Now, to be completely honest to ourselves, let’s try a couple of experiments.

First, let’s resize sga_target down – perhaps, it will give us more breathing room for sorting …

And then, let’s rerun sorting operations again …

Do we see any difference ?

Well, not really … The PGA is still allocated at roughly the (PGA) limit and SGA is still holding steady at its own limit.

And now let’s resize PGA up and see if it has any effect on SGA:

And, once again, the SGA did not budge and stayed fully allocated while PGA used more RAM and SGA, technically, only needed 1 Gb (remember, we told it so in previous exercise).

Ok, after running a number of similar experiments, the following picture emerges:

Notice a couple of things here:

• PGA is really pretty dynamic, even in 10g – it can be sized UP and DOWN and this operation is dynamic and almost immediate (depending on workload)
• SGA is somewhat dynamic as well – it can be sized UP (and is almost immediate if there is a pressing need), but it cannot be sized DOWN - the memory SGA grabs will stay with SGA forever (or, at least, until the next database restart)

But, a more important note is that – there is really no communication and no relation between SGA and PGA in ORACLE 10g, in other words:

Let’s see if anything changed with ORACLE 11 …

With 11g, the SGA/PGA is presumably managed as one memory area, so to keep things honest, we are going to set 11g memory_target to the same size as combined SGA+PGA in our 10g example and we are going to repeat the tests:

Let’s look at the state of SGA memory initially:

Notice the first major change – VMM segments are now allocated for the entire 4 Gb memory_target – that is, ORACLE gives itself an option to use all of the instance memory for the SGA (of course, it is extremely unlikely that this will ever happen). Also notice that potential MAX size of 7 VMM segments that are all (or mostly) zeroes (7 * 256 Mb = 1792 Mb) almost exactly matches current dynamic value of PGA Target.

Now let’s give it a spin and load our test table into memory …

Here we are actually faced with a slight problem: Neither dbms_stats.gather_table_stats() nor SELECT /*+ full(t) */ * FROM t seem to fill the 11g cache completely – instead, only a small portion of cache is used and memory allocation remains largely the same (this is consistent with ORACLE documentation that states that full table scan blocks are kept at the end of buffer cache and recycled).

By itself, this is a hell of the new feature, but it does screw our test … So, as a workaround, we are going to take a slightly longer road and fill database cache by lots of smaller “a few blocks only” SQLs:

Ok, now we are talking: buffer cache is fully allocated (again, the unallocated portion is reserved for shared pool etc) …

and we are ready to test memory shifting. Let’s start sorting sessions …

Well, the result is a bit disappointing – the SGA size was reduced, but NOT with a bang: a grand total of 12 Mb – from 2047 Mb to 2035 Mb – it is hardly worth mentioning …

Still, let’s see if we can influence ORACLE to make a “right” decision here by setting sga_target to a smaller value …

The command executed in a split second (which means that nothing was really done). And from the look of it there was indeed no immediate benefit. If anything, SGA gained memory yet again …

This is our last resort: let’s size PGA target up to give more memory to PGA explicitly:

And now we finally have results that we expected all along: SGA was sized WAY down to 713 Mb, while PGA was allowed to grow beyond 2 Gb.

Results of these tests seem to indicate the following:

• Shifting memory between SGA and PGA in ORACLE 11g does work
• However, by default, this process is very slow and gradual. Memory is not constantly moved back and forth between SGA and PGA (that would kill performance for sure …)
• Still, if necessary it is possible to override default ORACLE behavior and “move” memory to the other pool immediately.
• If immediate changes are needed, sizing target region UP (rather than sizing the other region DOWN) will give you results much faster

The last point begs for an explanation and I believe this is a result of ORACLE being lazy (or, perhaps, smart) … In other words, ORACLE would not size memory region DOWN immediately, because it does not have to (remember, that PGA and SGA targets are minimum requirements). On the other hand, sizing SGA (or PGA) UP makes ORACLE do it at once (and, in my tests, at least for SGA, it happened with NO workload present). In this case, ORACLE really has to do it as a new minimum requirement has to be met.

Honestly, what happens is rather weird: without manual tweaking, both SGA and PGA memory are allocated to the MAX, at least, according to svmon:

However, when manually tweaked (i.e. when you increase pga_aggregate_target), the database behaves exactly like it does with pre_page_sga=FALSE. In this particular example, part of SGA memory is de-allocated (and “shifted” to PGA).

This one is simple. When you attempt to start the database, it gives you this nice error:

Which also means that you cannot use memory_target with AIX large pages.

It must come as no surprise though as you cannot have it both ways

• Either your want SGA to be entirely in physical memory (and untouchable)
• OR your want SGA to be friendly and, in some cases, give way for PGA … (which involves quite a bit of “touching” )

This concludes my memory story … I hope that these modest explanations have been useful and gave you some insight of how ORACLE uses AIX memory.

If you want to know more, please, refer to these other excellent documents that describe memory behavior for AIX as well as other UNIX operating systems.

Tanel Poder on Memory_Target in Linux

Tom Kyte on Memory Target

Tanel Poder on ORACLE Memory Usage in Solaris

AIX White Paper on Multiple AIX Page Support

Overview of AIX Process Memory Regions

AIX Performance Presentation by Steve Nasypany that includes an excellent overview of memory usage