Sizing SGA

[anuragmin]

Hi,
What is the proper way one should go about sizing the sga / shared pool.

I increased the shared pool in different occasions to improve the library cache and the data dictionary cache but where should one stop.

The Oracle Documentation says
"The purpose of the SGA is to store data in memory for fast access, the SGA should always be within the main memory. If pages of the SGA are sapped to disk, then its data is no longer quickly accessible. On most operating systems the disadvantages of excessive paging significantly overweighs the advantage of the large SGA".

My question is how does one determine that SGA has reached its limit and any more increase in SGA size can result in paging/swapping.

Any Ideas.

Thanks
Anurag

[dknight]

I was taught to give 35% of the memory on a server to the SGA if it is the only (major) application running on the server when setting up the database. Tuning should be done from that point.

On Unix, I monitor swapping with the 'top' command.

Good luck.

[anuragmin]

I have 1 Gig of RAM on a Win NT server with Oracle 8.0.5 database as the only major application installed.

My Total SGA is 258 MB.

The server has some other applications installed like Oracle Express and OAS 4.0.8 but they are not used and if ever used very rarely.

How did you come up with a 35% figure.

Also my datadictionary cache, library cache and hit ratio are all tuned up.

Now in such a scenario will increasing the SGA help????

Though I have one issue of uneccessary parse calls in certain cases for which I need to increase the shared pool and which inturn increase the SGA.

Please suggest.

Thanks
Anurag