Strange Behavior with MEMORY_TARGET

Share Button

I got a help request from a client who was running Oracle 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:

I wanted to see if I could duplicate the issue, so I tested it on my 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

The next step was to test the change:

Testing the Change

So I tried turning off memory_target, and look what happens to the PGA:

Turn off memory_target

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

Even then, it would not let me grow the buffer cache. I had to disable sga_target for 11g to finally allow it:


Is this expected behavior from memory_target? Did I miss something? Is it a bug in I was not able to find any notes on Metalink 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.

The end looks just like the beginning. The only difference would be an internal barrier being lifted.

Share Button


  1. is it possible those __size play a role?
    much agreed the practice in your “Update 1”, set the sga_target higher first.

  2. Steve, thanks for your post. I also second your result on memory target does not work as expected. I have just read this article but I also ran several tests just like you did regarding sga and pga. the result was totally unexpected. pga was taking most of the memory than sga and whatever was available for sga, shared pool took almost 70% of sga, leaving small amount of memory for db cache causing high io. I finally turned off memory target and set automatic sga(10g feature). So far it has been running fine. This test were done on Oracle on HPUX.

  3. I encountered the same problem while configuring data guard. Thus is how I resolved the issue. my database was down and won’t startup due to this.
    1 I created a file from spfile
    2 Open the file which is a text file at oracle home/ dbs .Edited memory related parameters.
    3 rename my spfile
    4 Startup the database with file, I got an error that the value of my memory target was too low and it suggested a new value.
    5 I changed the memory target to the suggested value.
    6 I started the database with file, this time it came up.
    7 I created spfile from file.
    8 shutdown immediate
    9 startup. it came up with the spfile and ever since, its been working fine.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.