Oracle database operating system memory allocation management for PGA – part 4: Oracle 11.2.0.4 and AMM
This is the 4th post in a series of posts on PGA behaviour of Oracle. Earlier posts are: here (PGA limiting for Oracle 12), here (PGA limiting for Oracle 11.2) and the quiz on using PGA with AMM, into which this blogpost dives deeper.
As laid out in the quiz blogpost, I have a database with the following specifics:
-Oracle Linux x86_64 6u6.
-Oracle database 11.2.0.4 PSU 4
-Oracle database (single instance) with the following parameter set: memory_target=1G. No other memory related parameters set.
In this setup, I run the pga_filler script (source code here), which creates a collection until the session statistic ‘session pga memory’ exceeds the grow_until variable, which for this case I set to 2100000000 (approximately 2.1G).
So: the instance is set to have AMM (memory_target) with a size of 1GB, which is supposed to be the total amount memory which this instance uses, and a session runs a PL/SQL procedure which only stops if it has allocated 2.1GB, which is clearly more than configured with the memory_target parameter. Please mind a collection, which the anonymous procedure uses to allocate memory, is outside of the memory areas for which Oracle can move data to the assigned temporary tablespace (sort, hash and bitmap memory areas).
After startup of the instance with only memory_target set to 1G, the memory partitioning looks like this:
SYS@v11204 AS SYSDBA> select component, current_size/power(1024,2), last_oper_type from v$memory_dynamic_components where current_size != 0; COMPONENT CURRENT_SIZE/POWER(1024,2) LAST_OPER_TYP ---------------------------------------------------------------- -------------------------- ------------- shared pool 168 STATIC large pool 4 STATIC java pool 4 STATIC SGA Target 612 STATIC DEFAULT buffer cache 424 INITIALIZING PGA Target 412 STATIC
This is how v$pgastat looks like:
SYS@v11204 AS SYSDBA> select * from v$pgastat; NAME VALUE UNIT ---------------------------------------------------------------- ---------- ------------ aggregate PGA target parameter 432013312 bytes aggregate PGA auto target 318200832 bytes global memory bound 86402048 bytes total PGA inuse 78572544 bytes total PGA allocated 90871808 bytes maximum PGA allocated 93495296 bytes total freeable PGA memory 2818048 bytes process count 57 max processes count 58 PGA memory freed back to OS 3211264 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 0 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 8479744 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) 18 SYS@v11204 AS SYSDBA> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 0
Okay, so far so good. v$memory_dynamic_components shows the PGA Target being 412M, and v$pgastat shows the aggregate PGA target setting being 412M too. I haven’t set pga_aggregate_target (as shown with ‘show parameter pga’), because I am using memory_target/AMM for the argument I hear the most in favour of it: one knob to tune.
Next up, I start the pga_filler script, which means the session starts to allocate PGA.
I keep a close watch using v$pgastat:
SYS@v11204 AS SYSDBA> select * from v$pgastat; NAME VALUE UNIT ---------------------------------------------------------------- ---------- ------------ aggregate PGA target parameter 432013312 bytes aggregate PGA auto target 124443648 bytes global memory bound 86402048 bytes total PGA inuse 296896512 bytes total PGA allocated 313212928 bytes maximum PGA allocated 313212928 bytes
This shows the pga_filler script in progress by looking at v$pgastat from another session. The total amount of PGA allocated has grown to 313212928 (298M) here.
A little while later, the amount of PGA taken has grown beyond the PGA target (only relevant rows):
total PGA inuse 628974592 bytes total PGA allocated 645480448 bytes maximum PGA allocated 645480448 bytes
However, when looking at the memory components using v$memory_dynamic_components, it gives the impression PGA memory is still 412M:
SYS@v11204 AS SYSDBA> select component, current_size/power(1024,2), last_oper_type from v$memory_dynamic_components where current_size != 0; COMPONENT CURRENT_SIZE/POWER(1024,2) LAST_OPER_TYP ---------------------------------------------------------------- -------------------------- ------------- shared pool 168 STATIC large pool 4 STATIC java pool 4 STATIC SGA Target 612 STATIC DEFAULT buffer cache 424 INITIALIZING PGA Target 412 STATIC
You could argue PGA is explicitly mentioned as ‘PGA Target’, but then: the total of the memory area’s (PGA Target+SGA Target) do show a size that roughly sums up to be equal to the memory_target.
A little while later, this is what v$pgastat is showing:
total PGA inuse 991568896 bytes total PGA allocated 1008303104 bytes maximum PGA allocated 1008303104 bytes
Another glimpse at v$memory_dynamic_components shows the same output as above, PGA Target at 412M. This is the point where it get’s a bit weird: the total amount of PGA memory (according to v$pgastat) shows it’s almost 1G, memory_target is set at 1G, and yet v$memory_dynamic_components show no change at all.
Again a little further in time:
total PGA inuse 1325501440 bytes total PGA allocated 1342077952 bytes maximum PGA allocated 1342077952 bytes
Okay, here it get’s really strange: there’s more memory allocated for PGA memory alone than has been set with memory_target for both PGA and SGA memory structures. Also, v$memory_dynamic_components shows no change in SGA memory structures or exchange of memory from SGA to PGA memory.
If v$pgastat is correct, and memory_target actively limits the total amount of both SGA and PGA, then the session must allocate memory out of thin air! But I guess you already came to the conclusion too that either v$pgastat is incorrect, or memory_target does not limit memory allocations (as at least I think it would do).
Let’s dump the PGA heap of the active process to see the real memory allocations of this process:
SYS@v11204 AS SYSDBA> oradebug setospid 9041 Oracle pid: 58, Unix process pid: 9041, image: oracle@bigmachine.local (TNS V1-V3) SYS@v11204 AS SYSDBA> oradebug unlimit Statement processed. SYS@v11204 AS SYSDBA> oradebug dump heapdump 1 Statement processed.
(9041 is the PID of the process running PL/SQL)
Now look into (the relevant) data of the PGA heap dump:
[oracle@bigmachine [v11204] trace]$ grep Total\ heap\ size v11204_ora_9041.trc Total heap size =1494712248 Total heap size = 65512 Total heap size = 1638184
Okay, this is clear: the process actually took 1494712248 (=1425M) plus a little more memory. So, memory_target isn’t that much of a hard setting after all.
But where does this memory come from? There ought to be a sort of combined memory effort together with the SGA for memory, right? That was the memory_target promise!
Let’s take a look at the actual memory allocations of a new foreground process in /proc/PID/maps:
[oracle@bigmachine [v11204] trace]$ less /proc/11405/maps 00400000-0bcf3000 r-xp 00000000 fc:02 405855559 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle 0bef2000-0c0eb000 rw-p 0b8f2000 fc:02 405855559 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle 0c0eb000-0c142000 rw-p 00000000 00:00 0 0c962000-0c9c6000 rw-p 00000000 00:00 0 [heap] 60000000-60001000 r--s 00000000 00:10 351997 /dev/shm/ora_v11204_232652803_0 60001000-60400000 rw-s 00001000 00:10 351997 /dev/shm/ora_v11204_232652803_0 ... 9fc00000-a0000000 rw-s 00000000 00:10 352255 /dev/shm/ora_v11204_232685572_252 a0000000-a0400000 rw-s 00000000 00:10 354306 /dev/shm/ora_v11204_232718341_0 3bb3000000-3bb3020000 r-xp 00000000 fc:00 134595 /lib64/ld-2.12.so 3bb321f000-3bb3220000 r--p 0001f000 fc:00 134595 /lib64/ld-2.12.so 3bb3220000-3bb3221000 rw-p 00020000 fc:00 134595 /lib64/ld-2.12.so 3bb3221000-3bb3222000 rw-p 00000000 00:00 0 3bb3400000-3bb3401000 r-xp 00000000 fc:00 146311 /lib64/libaio.so.1.0.1 ... 3bb5e16000-3bb5e17000 rw-p 00016000 fc:00 150740 /lib64/libnsl-2.12.so 3bb5e17000-3bb5e19000 rw-p 00000000 00:00 0 7f018415a000-7f018416a000 rw-p 00000000 00:05 1030 /dev/zero 7f018416a000-7f018417a000 rw-p 00000000 00:05 1030 /dev/zero 7f018417a000-7f018418a000 rw-p 00000000 00:05 1030 /dev/zero 7f018418a000-7f018419a000 rw-p 00000000 00:05 1030 /dev/zero 7f018419a000-7f01841aa000 rw-p 00000000 00:05 1030 /dev/zero 7f01841aa000-7f01841ba000 rw-p 00000000 00:05 1030 /dev/zero 7f01841ba000-7f01841ca000 rw-p 00000000 00:05 1030 /dev/zero 7f01841ca000-7f01841da000 rw-p 00000000 00:05 1030 /dev/zero 7f01841da000-7f01841ea000 rw-p 00000000 00:05 1030 /dev/zero 7f01841ea000-7f01841fa000 rw-p 00000000 00:05 1030 /dev/zero 7f01841fa000-7f018420a000 rw-p 00000000 00:05 1030 /dev/zero 7f018420a000-7f018421a000 rw-p 00000000 00:05 1030 /dev/zero 7f018421a000-7f018422a000 rw-p 00000000 00:05 1030 /dev/zero 7f68d497b000-7f68d4985000 r-xp 00000000 fc:02 268585089 /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so ...
When I run the pga_filler anonymous PL/SQL block, and strace (system call trace) utility, I see (snippet):
mmap(0x7f0194f7a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194f7a000 mmap(0x7f0194f8a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194f8a000 mmap(0x7f0194f9a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194f9a000 mmap(0x7f0194faa000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194faa000 mmap(0x7f0194fba000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194fba000 mmap(0x7f0194fca000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194fca000 mmap(0x7f0194fda000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194fda000 mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_NORESERVE, 6, 0xea000) = 0x7f0194e6a000 mmap(0x7f0194e6a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194e6a000 mmap(0x7f0194e7a000, 131072, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194e7a000 mmap(0x7f0194e9a000, 131072, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194e9a000 mmap(0x7f0194eba000, 131072, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194eba000
So, when looking back, it’s very easy to spot the SGA memory, which resides in /dev/shm in my case, and looks like ‘/dev/shm/ora_v11204_232652803_0’ in the above /proc/PID/maps snippet.
This means that the mmap() calls are simply, as anyone would have guessed by now, the PGA memory allocations. In the maps snippet these are visible as being mapped to /dev/zero.
When looking at the mmap() call, at the 5th argument, which is the number 6, we look at a file descriptor. In /proc/PID/fd the file descriptors can be seen, and file descriptor 6 is /dev/zero, as you probably suspected. This way the allocated memory is initial set to zero.
By now, the pga_filler script finishes:
TS@v11204 > @pga_filler begin pga size : 3908792 last pga size : 2100012216 begin uga size : 1607440 last uga size : 2000368 parameter pat : 0
Taking the entire 2.1G I made the collection to grow to. With memory_target set to 1G.
Conclusion
The first conclusion I made is that PGA memory is very much different than SGA/shared memory. Anyone with a background in Oracle operating-system troubleshooting will find this quite logical. However, the “promise” AMM/memory_target made, in my interpretation, is that the memory would be used seamless. This is simply not the case. Shared memory is in /dev/shm, and PGA is mmaped/allocated as private memory.
Still, this wouldn’t be that much of an issue if memory_target would limit memory in a rigid way, and memory could, and actually would, very easily float between PGA and SGA. It simply doesn’t.
Why don’t we see Oracle trying to reallocate memory? This is the point where I can only guess.
– Probably, Oracle would try to grow the shared pool if it has problems allocating memory for SQL, library cache, etc. This probably hasn’t happened in my test.
– Probably, Oracle would try to grow the buffer cache if it can calculate a certain benefit from enlarging it. This probably hasn’t happened in my test.
– The other SGA area’s (large and java pool) probably are grown if these are used, and need more space for allocations. This probably didn’t happen in my test.
– For the PGA, a wild guess is the memory manager calculates using the workarea sizes (sort, hash and bitmap areas), which are not noticeably used in my test.
Another conclusion and opinion is AMM/memory_target is not a set once and forget option. In fact, it isn’t that much of a difference from using ASMM from a DBA perspective: you carefully need to understand the SGA size, and you carefully need to (try to) manage the PGA memory. Or reasoned the other way around: the only way you can sensibly set memory_target is if you know the correct SGA size and the PGA usage. Also having Oracle manage the memory area’s automatically is not unique to AMM: Oracle will reallocate (inside the SGA) if it finds it necessary, with AMM, ASMM and even manual set memory area’s. But the big dis-advantage of AMM (at least on linux, not sure about other operating systems) is that huge pages can’t be used, which has a severe impact on “real life” databases, in my experience. (Solaris CAN use huge pages with AMM(!)).
A final word: of course I tested a very specific situation. In most real-life cases there will be multiple sessions, and the PGA manageable memory areas will be used. However, the point I try to make is memory_target is simply not a way to very easily make your database be hard limited to the value set. Probably, in real life, the real amount of memory used by the instance will in the area of the value set with memory_target, but this will be subject to what memory areas you are exactly using. Of course it can differ in a spectaculair way if collections or alike structures are used by a large number of sessions.
Frits Hoogland Weblog 
Hello, Frits,
Very interesting ! 🙂
Thank you for sharing your experience !
very good explaination.. thanks