Originally posted by: SystemAdmin
I have some questions about how AIX manages the RAM on a system when Oracle is running both with and without the SGA locked. I understand that the recommendations from both Oracle and IBM are to leave it unlocked, and my testing bears that out quite heartily, but as a group (our AIX and Oracle admins) would like to better understand what's happening here.
Scenario 1 - System 1 - AIX 6.1 LPAR with 12VPs and an EC of 6, and 128GB RAM. Oracle 11gR2
With SGA set to 87GB and unlocked, and PGA set to 13GB (only using 4GB at the time of testing), the system runs beautifully, little to no paging activity, and memory utilization right in line with what we would expect.
Scenario 1 - System 2 - AIX 6.1 LPAR with 4VPs and an EC of 1.3, and 32GB RAM. Oracle 11gR2
With SGA set to 26GB and unlocked, and PGA unknown we see the exact same behavior. No paging, etc.
Scenario 2 - System 1
With SGA set to 87GB and locked, and PGA set to 13GB (again, only using about 4GB), the system performance goes right downhill. When Oracle starts, paging activity goes through the roof (2-3k pages/sec in, 3-5k pages/sec out, 3M pagescans/sec, etc) but eventually settles down until we attach some load to the box, where it starts paging like crazy again.
Scenario 3 - System 2
With SGA set to 13GB and locked, and PGA unknown we see similar activity (even with a much lower percentage of RAM allocated to SGA). Again, as soon as the database starts, paging goes up and performance goes down. It settles down after a bit, but as soon as any load is introduced, away it goes again.
I've been doing a lot of reading (much more than I ever intended) about AIX and Oracle memory tuning and have found that Scenario 1 for both system bears out what I'm reading. With SGA unlocked, everyone runs happily all along, regardless of the initial SGA setting (unless, of course, the SGA starts trying to utilize more memory than is available on the box). But, as soon as we lock the SGA, regardless of the amount of memory being utilized overall, AIX begins paging like crazy.
It's the SGA-locked behavior that I'm having trouble understanding. Everything I have read about the subject says that the SGA should remain unlocked (as we do), but locking it is an option if there are performance issues (like system paging, etc). All of my experience has shown me that, unless you are running a very low SGA setting, locking the SGA is a huge performance hit, and would cause more problems than it solves.
Can anyone help shed some light on the processes that occur in these situations that might help us understand better?
For reference, both systems are running AIX 6.1 TL6 SP5 and Oracle 11.2.0.2+.
#AIX-Forum