I got a help request from a client who was running Oracle 11.1.0.6
64-bit. Their memory_target parameter was set to 5G, with sga_target and all
pool parameters set to 0. pga_aggregate_target was explicitly set to 750MB.
However, Oracle would not allocate more than 64MB RAM to the Buffer Cache.
We looked through multiple snapshots and never found a case where
db_cache_size was above 64MB, despite a staggering amount of disk reads. So
we tried to change the db_cache_size manually to set the minimum:
SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
I wanted to see if I could duplicate the issue, so I tested it on my
11.1.0.6 Windows 64-bit instance. I prepared the system by setting
pga_aggregate_target to 128M, sga_target to 512M, and memory_target = 1648M.
NOTE: Throughout these examples, I’ve removed the irrelevant results from
“show parameter target”.
Setting It Up
SQL> alter system set pga_aggregate_target = 128M;
System altered.
SQL> alter system set sga_target = 512M;
System altered.
SQL> alter system set memory_target = 1648M;
System altered.
SQL> show parameter target
NAME TYPE VALUE
-------------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 1648M
pga_aggregate_target big integer 128M
sga_target big integer 512M
SQL> show sga
Total System Global Area 1720328192 bytes
Fixed Size 2115656 bytes
Variable Size 1426065336 bytes
Database Buffers 285212672 bytes
Redo Buffers 6934528 bytes
The next step was to test the change:
Testing the Change
SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
So I tried turning off memory_target, and look what happens to the PGA:
Turn off memory_target
SQL> alter system set memory_target = 0;
System altered.
SQL> show parameter target
NAME TYPE VALUE
-------------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 0
pga_aggregate_target big integer 1136M
sga_target big integer 512M
The PGA was given all of the extra space! This was pretty strange, so I
went ahead and changed the PGA to 128M and tried my test again:
Try again
SQL> alter system set pga_aggregate_target = 128M;
System altered.
SQL> show parameter target
NAME TYPE VALUE
-------------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 0
pga_aggregate_target big integer 128M
sga_target big integer 512M
SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
Even then, it would not let me grow the buffer cache. I had to disable
sga_target for 11g to finally allow it:
Solution
SQL> alter system set sga_target = 0;
System altered.
SQL> alter system set db_cache_size = 1024M;
System altered.
SQL> alter system set memory_target = 1648M;
System altered.
SQL> show parameter target
NAME TYPE VALUE
-------------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 1648M
pga_aggregate_target big integer 128M
sga_target big integer 0
Is this expected behavior from memory_target? Did I miss something? Is it
a bug in 11.1.0.6? I was not able to find any notes on MOSC regarding
the issue. The Oracle Documentation states that this should work just fine.
When memory_target is enabled, sga_target and pga_aggregate_target should
only work as minimums if explicitly set.
I will play around with it some more if I get the chance. In the
meantime, I have to wonder how many DBAs have confidently set memory_target,
all the while not knowing their individual pools weren’t being sized
properly?
Update 1
I just did another test where I “primed the pump” so to speak. To do so,
I set the sga_target higher. It allowed me to grow the db_cache_size at that
point. However, I then set db_cache_size down to 0, set sga_target back to
512M, and was still able to set db_cache_size back up to 1024M afterwards.
NAME TYPE VALUE
---------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 1648M
pga_aggregate_target big integer 128M
sga_target big integer 512M
SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
SQL> alter system set sga_target = 1300M;
System altered.
SQL> alter system set db_cache_size = 1024M;
System altered.
SQL> alter system set db_cache_size = 0;
System altered.
SQL> alter system set sga_target = 512M;
System altered.
SQL> show parameter target
NAME TYPE VALUE
---------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 1648M
pga_aggregate_target big integer 128M
sga_target big integer 512M
SQL> alter system set db_cache_size = 1024M;
System altered.
SQL> show parameter target
NAME TYPE VALUE
---------------------------- ----------- -----
memory_max_target big integer 1648M
memory_target big integer 1648M
pga_aggregate_target big integer 128M
sga_target big integer 512M
SQL> show parameter db_cache_size
NAME TYPE VALUE
-------------------------------- ----------- -----
db_cache_size big integer 1G
The end looks just like the beginning. The only difference would be an
internal barrier being lifted.
For more on Oracle 11g memory_target:
Oracle 11g memory_target parameter
