Can ORACLE 11g memory_target work with AIX large pages ?
One of my readers recently asked me if ORACLE 11g can use memory_target along with AIX large pages … (Thanks Randolf!)
At the first blush this seems to be impossible. The main reason, of course, is that memory_target and large pages are used for the purposes that are, in fact, completely opposite!
- The goal of 11g memory_target is to flow memory efficiently between ORACLE SGA and PGA to the place where it is most needed. This means that SGA may reduce its size from time to time, releasing memory to the operating system
- AIX large pages, on the other hand, are designed to never leave physical memory
I hope, you can see a contradiction here …
As if this was not enough, when you actually try to load SGA into large pages (by setting lock_sga=TRUE) and also enable memory_target, ORACLE would give you a rather direct negative reply:
So, given these reasons (and this evidence), it is only natural to conclude that memory_target and large pages are simply incompatible with each other and cannot be used together.
But, not so fast … As with any complex software, ORACLE has plenty of back doors, “support only” options and hidden parameters that can alter database behavior, sometimes dramatically.
One of these back doors can be used here and it’s described in detail in Metalink note: 728444.1. In essence, a bug that was introduced in ORACLE 10.2.0.4 prevented AIX large pages from being used by SGA. As a workaround, ORACLE suggested to use external environment variable for setting SGA page size before starting the database as in:
While this accomplished the goal of allocating large pages for the SGA, it also had one unintended consequence: large pages could now be used along with memory_target.
But wait … Is this necessarily a problem ? Why do I believe that this was not what ORACLE had in mind ?
To see why, let’s go back to the original requirement for memory_target: use memory efficiently by moving it between SGA and PGA as needed. Notice that this requires memory to be allocated and deallocated dynamically and, as a result, size of SGA can grow and shrink with time.
Let’s test whether this is really the case.
First, we will run this test on a “normal” system where SGA is allocated out of regular pages. Initially our database is set with the following parameters:
- memory_target=1000M
- sga_target=700M
- pga_aggregate_target=200M
This is what SGA memory looks right after startup:
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
1011804 m 9.50 0.00 0.00 9.50
1008e45 m 12.00 0.00 0.00 12.00
120dc65 m 13.19 0.00 0.00 13.19
12012a4 m 130.88 0.00 0.00 130.88
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL: 165.56 0.00 0.00 165.56
Requested SGA: 1000.01 Segments: 4
Notice several things here:
- AIX allocated only ~ 165Mb out of 1000Mb that was requested. This is expected as, by default, AIX only “really” allocates memory that has been used and, well, most of the RAM has NOT been used yet
- The memory_target was set to 1000 Mb. This memory is supposed to be shared between SGA and PGA, and yet SGA has allocated enough shared segments to potentially grab 100% of memory_target (4 segments, 256 Mb MAX each). Of course, the likelihood of that happening is pretty small
Ok, let’s add some load to our database to use more SGA pages …
SQL> ALTER SESSION SET events '10949 trace name context forever, level 1';
SQL> SELECT COUNT(1) FROM t;
… and see where it can lead us:
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
1011804 m 9.50 0.00 0.00 9.50
12012a4 m 159.06 0.00 0.00 159.06
120dc65 m 200.00 0.00 0.00 200.00
1008e45 m 256.00 0.00 0.00 256.00
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL: 624.56 0.00 0.00 624.56
As you can see, SGA allocated more pages and is now using ~ 625 Mb, most of it in buffer cache. Let’s now use the memory_target main feature and redirect this memory into PGA …
SQL> ALTER SYSTEM SET pga_aggregate_target = 700M;
.. and this is what SGA looks like after these operations are completed:
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
1008e45 m 0.00 0.00 0.00 0.00
1011804 m 9.50 0.00 0.00 9.50
120dc65 m 108.00 0.00 0.00 108.00
12012a4 m 108.44 0.00 0.00 108.44
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL: 225.94 0.00 0.00 225.94
Notice that most of the memory in SGA was deallocated to presumably benefit PGA areas …
Just to confirm that we’ve done everything correctly, let’s check SGA and PGA sizes as reported by ORACLE:
2 WHERE component LIKE '%Target';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
SGA Target 314572800
PGA Target 734003200
And, as you can see, ORACLE number for SGA roughly matches what AIX actually allocates.
This is how memory_target is supposed to work. Let’s see whether anything changes if we add AIX large pages on top of it …
AIX> export ORACLE_SGA_PGSZ=16m
AIX> dbstart.pl
Let’s look at the state of ORACLE SGA just after the startup:
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
1178b93 L 240.00 240.00 0.00 240.00
11cb41d L 256.00 256.00 0.00 256.00
103d181 L 256.00 256.00 0.00 256.00
13c9d3f L 256.00 256.00 0.00 256.00
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL: 1008.00 1008.00 0.00 1008.00
As was expected with large pages – ALL SGA segments are allocated to the MAX and SGA is using 1 Gb of memory, or, theoretically 100% of memory_target. Does it mean that PGA is really 0% and processes do not use memory ? Of course, not … Below is the process snapshot from our instance and, as you can see, some memory is definitely being used (and will be used even more if we add some load to them):
Vsid InMem Paging Virtual COMMAND
---------- ---------- ---------- ---------- ------------------------
5832868 3.01 0.00 3.01 ora_diag_test11
60293328 3.01 0.00 3.01 ora_psp0_test11
18809280 3.01 0.00 3.01 ora_reco_test11
65274060 3.02 0.00 3.02 ora_mman_test11
59637856 3.02 0.00 3.02 ora_vktm_test11
28573750 3.05 0.00 3.05 ora_qmnc_test11
66715900 3.13 0.00 3.13 ora_mmnl_test11
20709740 3.18 0.00 3.18 ora_q000_test11
14942550 3.38 0.00 3.38 ora_dbrm_test11
25755826 3.51 0.00 3.51 ora_dia0_test11
52953116 3.57 0.00 3.57 ora_ckpt_test11
37224472 3.90 0.00 3.90 ora_smon_test11
13959586 6.03 0.00 6.03 ora_fbda_test11
46268428 7.57 0.00 7.57 ora_dbw0_test11
22741498 8.05 0.00 8.05 ora_pmon_test11
12714338 8.83 0.00 8.83 ora_mmon_test11
51183838 18.26 0.00 18.26 ora_lgwr_test11
-1 200.16 0.00 0.00 TEXT SEGMENT
---------- ---------- ---------- ---------- ------------------------
Vsid InMem Paging Virtual COMMAND
---------- ---------- ---------- ---------- ------------------------
TOTAL: 287.71 0.00 87.55 Processes: 17
Let’s do the same exercises with this memory as in the “normal” example.
We will first run some load to “increase” SGA … This is the state of SGA after the load is completed:
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
1178b93 L 240.00 240.00 0.00 240.00
11cb41d L 256.00 256.00 0.00 256.00
103d181 L 256.00 256.00 0.00 256.00
13c9d3f L 256.00 256.00 0.00 256.00
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL: 1008.00 1008.00 0.00 1008.00
And, as you can see, there is really no change …
And after that, we will switch SGA and PGA sizes and check SGA again:
SQL> ALTER SYSTEM SET pga_aggregate_target = 700M;
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
1178b93 L 240.00 240.00 0.00 240.00
11cb41d L 256.00 256.00 0.00 256.00
103d181 L 256.00 256.00 0.00 256.00
13c9d3f L 256.00 256.00 0.00 256.00
---------- -- ---------- ---------- ---------- ----------
Vsid Pg InMem Pin Paging Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL: 1008.00 1008.00 0.00 1008.00
And once again, there is no change …
Just to confirm that we’ve done everything correctly, let’s check SGA and PGA sizes as reported by ORACLE:
FROM v$memory_dynamic_components
WHERE component LIKE '%Target';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
SGA Target 314572800
PGA Target 734003200
ORACLE thinks that its SGA is only using 300Mb of RAM … and this is clearly a lie!
The bottom line
Let’s go back to our original question: Can ORACLE use both memory_target and AIX large pages at the same time ?
As you have seen, the answer is: Yes, but this is also the case where the letter of the answer defeats the spirit of the answer …
When large pages are used, all the benefits that memory_target is supposed to provide are gone: SGA memory is no longer dynamic and will never be released to be a part of PGA.
So, can you use them both: Yes. Is it of any use: No.
And that’s the bottom line.
August 20th, 2010 - 14:02
Nice!
March 8th, 2011 - 23:54
And if you have ever seen how much kernel CPU heavy dynamic memory management really consumes, you’ll realize why hugepages were designed to be fixed in memory and never paged.
That way one can be sure that CPU is not going to be taken away to do memory maintenance at a time when it is most in demand.
Always remember: memory management is not a “free” operation: it requires heavy CPU and memory bandwidth usage. You initiate that in an already overloaded system and I can guarantee you an immediate thrashing.
Have a look here for an introduction to what it all means:
http://en.wikipedia.org/wiki/Thrashing_%28computer_science%29
March 9th, 2011 - 16:50
Noons, thank you for your comment – I agree – memory management is definitely not free.
However, since large pages are, well … large, and hence there are fewer of them (for the same amount of memory), wouldn’t managing them be easier for the system (than, say, managing small pages) ? Do you see where I am going here ?
Anyway, my post was about MEMORY_TARGET feature in ORACLE that is all about “dynamic memory” (which should “flow” between SGA and PGA). When large pages are used, memory becomes “static” and cannot flow … in other words, using MEMORY_TARGET with large pages while technically possible, is useless.
Cheers,
Maxym Kharchenko
April 28th, 2011 - 17:09
Maxym:
Very interesting indeed. What version of 11g and AIX were you testing with? We’re upgrading to 11.2.0.2 and I’m trying to decide whether to use memory_target or not. Currently we’re using 10.2.0.4 on AIX 6.1 with large pages and SGA locked. What commands are you using to show memory use? I would like to see which instance process are using small, medium, and large pages.
Thanks!
John
May 1st, 2011 - 13:05
Hello John.
We were running all kinds of configurations: AIX 5.3, 6.1 and ORACLE 10.2, 11.2.0.1, 11.2.0.2 (I have to say – “were” … as I recently changed jobs and my environment is mostly Linux now).
The decision of whether to use memory target depends mostly on whether you want to allow dynamic memory resize ops for your db instance. Most of the shops disallow that as it can lead to system freezes when memory is “resized out of” shared pool and database suddenly needs to (re)parse lots of of cursors. Memory_target might be ok though for development or non-critical systems as it simplifies memory management.
Also, keep in mind that memory target and large pages do not mix very well, essentially to maximize their respective benefits you have to pick either one or the other …
As for the tools, svmon is probably the best tool to look into details of AIX memory usage – feel free to search this website for examples of svmon use.
Cheers,
Maxym Kharchenko