Re: SGA and PGA

I also agree with the prior reply.

Java can be set to 0 if you are not using java. The cache can be adjusted dynamically as needed, many small oltp systems don't need much, just watch for excessive IO, or the wait stats db_file_scattered_read (full table scans from disk) and db_file_sequential_reads (index scans from disk).

The buffer cache is where oracle reads blocks into and stores them for reading and manipuation. The PGA advisor will let you know after a while of usage if you need to increase it or decrease it. The cache advisor will give you a good direction on how much memory to use, but you have to run the database in production for a little while to get good data. I would keep the SGA_MAX set to your 2500m and set the cache to 1000m. Let the advisor tell you how to move it next until you are comfortable then you can readjust the SGA_MAX to only a little bigger than you need. Keep in mind OLTP should need a huge cache because it is characterized by specific queries and only a few full table scans, usually for reports.

You can do extensive performance analysis later with stats pack or many of several custom queries, but only do that if the app seems slow, that is the real test.
aprinsloo_at_sagetelecom.net wrote:
> Oracle 9i Standard Edition
> Red Hat Linux 5
>
> I'd appreciate any help on the following senario and how to divide up
> Oracle mem.
>
> We have 4G total on server and I have allocated based on Oracle
> recomendations for an OLTP system 640 meg to PGA (PGA_AGGREGATE_TARGET
> = (<Total Physical Memory > * 80%) * 20% ).
>
> As a starting point should I set SGA max size to 2500 meg and leave
> the rest to OS? What would I allocate to shared pool, buffer cache,
> large pool and java pool within this area?
>
> Last question, Is buffer cache equivalent to MS Sql server data cache,
> if not what it be in Oracle?
>
> Thx in advance.
Received on Thu Oct 26 2006 - 22:40:14 CDT