Strange Behavior with MEMORY_TARGET

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 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.

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.

This article describes how scientists at Sandia National Laboratories in Livermore were able to run a million linux kernels on a single system as virtual machines.

One million kernels!

That’s a whole mess of VMs. Given the enormous amount of growth the computing world has gone through in the last few decades, you have to wonder whether one day we will be able to duplicate the internet as we know it today onto one single machine hosting millions (or billions) of VMs. It’s an interesting concept, at the least!

Perhaps Oracle will find a niche in the million-VM-system world. Oracle 15N – The Neuronetworked Database.

You have to love software marketing.

Imagine buying a new car. In the new car comes leather seats, A/C, and a killer sound system. After driving it around for a few days, you get a reminder from the dealership:

The leather seats, A/C, and sound system are separately licensed products. Please pay the license fees or remove these add-ons from your vehicle.

Ouch! Thank goodness this doesn’t happen in the auto industry. There are, however, two industries where this type of marketing occurs every day: drug dealing and software sales. We’ll focus on software.

This time’s on me

You’ve downloaded your Oracle version of choice and run the installer. You ran DBCA, set up a listener, and you’re good to go. But what’s this? This automatic workload repository is amazing! So much historical data, right at your fingertips! Historic views via detailed reports that resemble Statspack on steroids, stunning visuals in the Performance tab of OEM!

Not so fast, buddy. After your initial taste, you might be hooked. But if you want to keep using these features, it comes with a cost. And that cost is going up, up, up.

This one’s going to cost you extra

As this article notes, the cost of these built-in packs are going up. The diagnostic, tuning, and database configuration packs are going up in price from $3,500 to $5,000.

Interesting…is it coincidence this change comes around the same time as announcements regarding the killer whale modeled Oculus or inifinity loop modeled Infinitas?

Just kidding Larry, you know I love you.

Going through withdrawal

If you can’t get the managerial approval to purchase these packs, the Performance tab will have to be grayed out in OEM and you won’t be able to use any AWR-powered features (AWR, ADDM, ASH). This article has a better breakdown.

However, you’re not left without options. You’ve still got Statspack, which DBAs have been loving since Oracle 8i. Statspack is not as intensive as AWR, but it is still getting better by version and is free to use without purchasing extra packs. You also have the trusty V$ and DBA_ views to aid in your tuning efforts. While not as comprehensive for real-time tuning as ASH, you can glean a great deal of information from them.

Rehab

If you still find you can’t survive without the pretty graphs and in-depth information provided by the tuning packs, there are other possibilities. Third-party tools exist which allow you to delve into your system’s performance woes. Unfortunately many of these tools require AWR, meaning you still have to pay for the Oracle packs to use them. One tool which allows both AWR and Statspack use is Ion for Oracle; I’m a bit partial to it, as I helped write the software.

With Ion, both AWR and Statspack (when possible) can be used to visualize important data:

If you’re interested in looking into Ion, visit the website (not via the contact form on this site).

Conclusion

Don’t take this post to mean I disapprove of Oracle’s methods. It’s an absolutely brilliant marketing method, and most of the shops I’ve worked with were hooked on AWR after the first try. One DBA I met went hardcore on his manager and said (and I quote), “you can have my diagnostics pack when you pry it from my cold, dead fingers.”

However, for those of you stuck without AWR, you’re not alone. And with the increase in price for these packs, you might have a few new friends soon.