Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SGA tuning

Re: SGA tuning

From: Will Kooiman <wkooiman_at_csac.com>
Date: 1996/10/28
Message-ID: <3275995C.B8@csac.com>#1/1

Le-Chin Eugene Liu wrote:
>
> A few questions regarding SGA...
>
> 1) To ajust the SGA size, is init.ora the place to go? Is there
> any other way to do so?
>
> 2) How does Oracle handle the SGA allocation? Does it grab all the
> memory at startup time?
>
> 3) Is there a guildline to allocate SGA size as proportional
> to the total system memory?
>
> 4) Is there a way/tool to monitor the content of a SGA?
>
> Thanks,
>
> Phil

  1. SGA size is totally controlled by the init.ora. When adjusting the SGA, you don't add more memory to the SGA. You add more memory to things that make up the SGA. (i.e. db buffers, shared pool, etc.)
  2. SGA allocation varies with platform, but in general, Oracle allocates memory for the SGA at database startup time. On Unix/VMS, Oracle creates a "backing store" file or paging file for the SGA. This is required by virtual memory operating systems to give the O/S a place to put the SGA if it must be paged out of main memory. This is why you don't want the SGA to be too big.
  3. The total size of the SGA will depend on a lot of things, but I don't like to create it larger than 50% of the total memory on the machine. Keep in mind I don't compute 50% and set that as a target. You should use bstat/estat (they're in rdbms/admin) to determine your cache hit ratios, and keep increasing the various parameters until you start getting 90% hit ratios. You should also watch the system paging rate to make sure you aren't using too much system memory.
  4. There are a couple of ways to monitor the content of the SGA. bstat/estat is a good all-around tool. There are also tools you can buy. I've only used DB General. It's pretty good.

Hope this is helpful.

Will Kooiman
Computer Systems Authority
wkooiman_at_csac.com Received on Mon Oct 28 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US