Oracle database operating system memory allocation management for PGA – part 3: Oracle 11.2.0.4 and AMM: Quiz
This is a series of blogposts on how the Oracle database makes use of PGA. Earlier posts can be found here (PGA limiting for Oracle 12) and here (PGA limiting for Oracle 11.2).
Today a little wednesday fun: a quiz.
What do you think will happen in the following situation (leave a response as comment please!):
-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.
Run the pga_filler script (which can be found here (PGA limiting for Oracle 12)), with grow_until set to 2100000000 (approximately 2.1G).
I’ll try to create a blogpost on the outcome and an explanation on short notice!
Frits Hoogland Weblog 
You don’t say how much memory is on the server Frits but I’m going to assume “enough” – over 4GB.
I think that, being Oracle 11, the value for PGA_AGGREGATE_TARGET will have defaulted to around 400MB, granule size determining the specific size – but that will be ignored as it is only seen as a not-at-all honoured aim and the PGA will escalate in size until you get some sort of PGA memory error, probably ORA-4030, maybe stating some particular area cannot be extended, causing your PGA_FILLER script to abend, The database will remain up, the PGA will be “large” (I would guess larger than the SGA) but any further work will be prone to similar PGA errors until you reboot. Flushing won’t help.
If you are tight for system memory you might just crash the DB… *and* the server?
I’m pretty sure my answer will be wrong but I thought I’d get the ball rolling…
I think this depends also on tmpfs file system size. If this size is too small Oracle instance should crash at startup.
Otherwise I expect “very strange behaviour and process deaths with the AMM feature and high demand for memory” interesting enough to be posted on your blog.
Oracle will unlock, transfer and lock memory from SGA to PGA as needed from your PL/SQL collection (that doesn’t respect PGA_AGGREGATE_TARGET – not relevant on this case) until memory is available on the server or until you reach memory_max_target.
Since you don’t have memory_max_target set, you will probably end-up with some sort of PGA memory allocation error or weird behavior, when Oracle can’t find another granule to allocate.
It is probably the wrong answer anyway 🙂
Pingback: Oracle database operating system memory allocation management for PGA – part 4: Oracle 11.2.0.4 and AMM | Frits Hoogland Weblog
I guess the script will run fine PGA will grow >= 2.1Gb.
Oracle 11.2 will *try* to shrink some SGA components in response.
And it may cause some issues (indirectly) with some undersized SGA components..