Re: Combined SGA size and Total Memory

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 23 Jun 2004 20:33:31 -0400
Message-ID: <20040624003331.GA1807@medo.noip.com>

On 06/23/2004 08:04:03 PM, Thomas Biju wrote:
> R3JhZW1lLA0KTm8sIEknbSBub3QgdGFsa2luZyBhYm91dCBpbmNyZWFzaW5nIHRoZSBzaXplIG9m

I hate when I have to decode email. It should be sent in ASCII format, no MIME encoding. Nevertheless, you wrote:

Graeme,
No, I'm not talking about increasing the size of SGA simply because we have memory. The reason for the question was basically different. We typically create new databases on the same HP or Sun server which currently host anywhere from 6 to 23 databases... At what point should we alert the management for a memory upgrade? My point was there should not be any rule of thumb such as SGA should be less than 30% of total RAM, more memory requirement should be based on the load on the server.... Thanks,
Biju

There shouldn't be a rule of thumb. There are utilities called top, sar, iostat, vmstat and glance telling you how much time are CPUs spending in the kernel mode. When CPU is in the kernel mode, it is executing kernel code, not user code (unless your system has been badly hacked), which means that the system is not performing work for the user. There are several things that are done in kernel mode, most notable of which are servicing interrupts or paging. System services interrupts for I/O. The architecture of a Unix minicomputer (unless it's an HP SuperDome, in which case it's a completely different situation) is such that there are several interrupts for each I/O request. When you issue command like "commit", it is going to cause several interrupt requests. Interrupts are not a problem. I used to work on HP 9000/N system with 16 CPUs, which could seamlessly perform up to 5000 I/O requests per second against EMC Symmetrix storage array. When you start seeing 30% of your total CPU power being utilized in the kernel mode, you are paging. It is at this point that you should start tuning your virtual memory subsystem or start considering adding memory. There is a very good book named "Optimizing Oracle for Performance" in which authors introduce a creature called "performance analyst" (I had a private email discussion about that with one of the authors), who should be capable of both diagnosing performance problems and giving the overview of the bottlenecks, in layman's terms, to the damagement. In my opinion, an almost perfect person like that (another example of almost perfect people was Mary Poppins) should also have extensive experience with diagnosing Unix performance problems and know how to tune kernel and present the effects of his or her work to the damagement. Mary Poppins is not to be confused with Mary Bobbins, who once kept her watchful eye on little Lisa, Bart and baby Simpson.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jun 23 2004 - 19:30:16 CDT