Tunning Oracle 9i

[cigano]

I would like to know how I should set the SGA and PGA parameters ???

[dr_gori]

Hello Cigano

Unfortunately, I'm not a DBA to inform you in detail about it. I know configuring Oracle's memory is not a standard thing. (Ie do not have a cake recipe ready for this) ...

Below, I will leave a quote that I had kept on the subject of Chiappa: (I hope it's useful)

Someone could tell me a little about SGA and PGA and how to configure them, I believe that based on the physical resources of the machine ...
I have a PIV machine, 2GB of RAM, only 1GB free disc, Win2000 Server, BD Oracle 9.0.1.1.1
) SGA -. Shared pool 144mb, buffer cache 80mb, large pool 4mb, java pool 128mb / pga (manual) max user 700 (I do not know why, I have in the maximum 50) and sort area 1mb.

Thank you very much for those who can help.

Posted by: Chiappa on 29/06/2004

My expensive lost, your assumption is ** false **: The SGA config (and Items that are going to compose PGA), absolutely not only a function of your hardware, but how it will be used. Example: If the application does not use Java, obviously Java_Pool will stay in a minimum (not zero, since Oracle may also need internally), but minimize. Sort_Area_Size, if it is an OLTP application (typically many users doing SQLs, great chances of several having to get Sort simultaneously), a somewhat low Sort_Area, already if it is batch (ie, very few users, but doing monstrous sqls in gigantic tables) , there you can climb, although it is not going to rise for the stratosphere, because typical tables of batch systems are huge, there is no RAM that holds them prum sort only in memory ...
What I recommend is: Study with attention The Concepts and Admin Manual (to learn the concepts, which is every thing, where it is used), then ** Study ** Application (OLTP or batch? How many users sending SQL at the same time - you already know that the maximum is 70, but of these 70, ** how many ** at the same time ??? Which language was developed - Oracle own language or third parties? How does it connect to the bank, natively or via ODBC, Java, Whatever ?

on top of that, having concepts in hand and knowing the detail Application, then yes you can act in the config.

As for SGA and PGA, the Concepts Manual explains, but to be well established, we will specify:

SGA: System Global Area, is basically composed By the bench caches and buffers of the bank (such as referring to the data dictionary, the SQLS cache, the Log Buffer, etc.), plus the data caches (the block buffer cache). It is immediately allocated and reserved as soon as the Banco Starta, and as a rule has to be in RAM alive, not swap file.

PGA: Program Global Area, is the RAM that is being allocated when the user connects to the bank and begins to rotate programs, send SQLs, make SORTS ... that's it.