CODEWITHSUNDEEP
oracle-databasememory
user234568
user234568

Reputation: 815

SGA and PGA in Oracle Database Configuration

I know that SGA ( that contain data and control information for one Oracle Database instance) stands for System Global Area and PGA (that contains data and control information exclusively for use by an Oracle process) stands for Program Global Area but, I don't really understand the function of the variables does to the database. How would it help when retrieving data if SGA is configured like 10 times larger than PGA ?

Upvotes: 1

Views: 10315

Answers (1)

sandman
sandman

Reputation: 2108

The SGA is a memory structure on the server that contains pools to hold code, SQL, classes, cursors, etc. and caches to hold data. So when a client sends a query to the server, the code and data sits in the SGA to get processed by the RDBMS on the server.

The PGA is a shared memory area for a user server process and is used for temporary storage and work areas. Oracle uses the PGA and temp tablespaces to work to get to a result set which is passed back to the client, then the PGA for the session is freed.

There is no ratio between the two. The SGA is sized according to how much code and data is getting sent to the server, and the PGA is dynamic according to how many processes are active. If there are thousands of processes, the PGA can easily be double the SGA. The SGA is sized VERY carefully though; making it bigger does not necessarily make it better for performance reasons.

There is also a UGA (User Global Area) which is the memory area for each client (non-server) process.

Upvotes: 5

Related Questions