Automatic Shared Memory Management Advice

From: ohaya <ohaya_at_cox.net>
Date: Mon, 05 Dec 2005 19:24:00 -0500
Message-ID: <4394DA20.49055AFD@cox.net>

Hi,

I am doing some testing with Oracle 10gAS Rel. 2 on Solaris 9. I'm doing this testing on a cobbled together used Sun Blade, and this is just for doing some installation testing. This machine has about 1.1GB of memory right now.

I was able to get the 10gAS installation done, including the infrastructure, web cache, and portal, and things seem to be running ok.

My understanding is that an instance of Oracle 10g database gets installed with 10gAS, and I've been wondering if it might be possible to reduce the amount of memory that the database uses, as when the whole 10gAS installation is running, I think I'm running pretty close to the 1.1GB.

I've been doing research, and ran across information about Automatic Shared Memory Management in 10g, and I was wondering if things would work ok if I used that, and tried to reduce the SGA_TARGET?

In particular, I'm looking at:

http://www.oracle.com/technology/pub/articles/10gdba/week17_10gdba.html

About 1/2 way down that page, it says:

"Now suppose the host memory available to Oracle is reduced from 500MB to 300MB, meaning we have to reduce the size of the total SGA. We can reflect that change by reducing the target SGA size."

The above seems to imply that I can reduce the physical memory use in conjunction with ASMM?

Right now, EM is showing:

Maximum SGA size: 268 MB
Shared Pool: 14 4MB
Buffer Cache: 48 MB
Large Pool: 8 MB
Java Pool: 64 MB
Other: 1 MB

SQL> select sum(value) from v$sga;

SUM(VALUE)

---

 281018368

Per the above article, I'm thinking of something like:

• Enable ASMM in EM
• Use sqlplus: alter system set sga_target = 200M scope=both;

In other words, to try to reduce the physical memory from ~268MB to ~200MB.

Obviously, I've never done anything like this before :(, and I'm wondering what the possible downsides might be? The main thing that I *DON'T* want to do is damage my installation, as it's already taken me a number of days to get to this point.

Thanks,
Jim Received on Mon Dec 05 2005 - 18:24:00 CST