Re: SGA tuning

The init.ora parms control the size of the buffer cache and shared pool. My understanding is that Oracle grabs memory to service the SGA when it needs it and up to it maximum size. There is a parameter called pre_page_sga that can be set however that allocates the entire SGA from memory at instance startup.

I have read a guidline that says one third of real system RAM should be allocated to SGA but this is very dependent on the type of activity going through your system. Whatever figure you choose, the system should be able to support it in real memory. If over allocation causes your system to page the SGA, it will double up the caching of data that Oracle is designed to do therefore degrading performance.

To monitor whether the SGA has been sufficiently allocated, you can monitor system activity using the utlbstat and utlestat scripts. These will collect oracle system stats over a period of time and produce a report, allowing you to look at the hit ratio on the buffer cache and the shared pool. If the hit ratio is low (< 80 or 90%), it may be worth allocating more memory to these areas.

Regards
Simon Kelly
Database Consultant
ITnet Ltd

Le-Chin Eugene Liu <philt_at_u.washington.edu> wrote in article <54s5nt$n7r_at_nntp1.u.washington.edu>...
> 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
>
  Received on Mon Oct 28 1996 - 00:00:00 CST