SGA and PGA Components

[mindScape]

Hi,

I have been trying to put SGA and PGA components into proper perspective and I am having a difficult time because all information that I have come across seems to use different names for what appears to be by description the same component or some documentation will breakdown the primary components into smaller subcomponents and not categorize the main components.

If that sounded like a confusing mouthful, then you can understand my level of difficulty trying to get a grasp on the basics.

For instance, I have read this definition:

PRIVATE SQL AREA - stores SQL statement information and its execution plan (located in the PGA for DEDICATED SERVERS and SGA for SHARED SERVERS)

So this is good until I come across another definition like this:

SHARED SQL AREA - stores SQL statement and its execution plan.

Now I'm confused. Is the PRIVATE SQL AREA actually referred to as a SHARED SQL AREA when on a SHARED SERVER? It would seem so since the definitions are identical

Can anyone provide me with a more clear and easy break down for what components make up the SGA and what make up the PGA at the top level or tell me where I can find this information?

So far this is what I believe is correct:

[SGA]
PRIVATE SQL AREA (IF ON A SHARED SERVER)
LIBRARY CACHE
ROW CACHE
SHARED POOL
BUFFER CACHE
REDO LOG BUFFERS

[PGA]
PRIVATE SQL AREA (IF ON A DEDICATED SERVER)
CURSORS
SESSION MEMORY


If you see any mistakes in what I have here, please correct me by all means. I just want to simplify the grouping of the components for each memory section. (SGA and PGA) I can dissect the subcomponents if I clearly know what the base components are

Anyone able to help out here??

Thanks.

[sambavan]

To simply state:

UGA (User Global Area)
1. User Session data (It comprise the data of security and resource usage)
2. Cursor state ( it is the runtime memory values for the SQL-stmt, such as rows retrived)
3. Sort data

In a dedicated environment
PGA will compise
1. Stack space (Local variables for the process)
2. UGA

In a shared server environment
PGA
1. Stack space

The UGA moves into Shared pool. If you don't have the large pool configured, then the resource is allocated out of shared pool.


Hope this would clear your confusion.

[mindScape]

Thanks for replying sambavan.

I understand very clear what you are saying but it still kinda side-steps the original question.

You offered some clarity for PGA components by introducing another component that I really didn't ask a definition for but thats okay.

My understanding is that
SGA (System Global Area) has some well defined memory structures within it. What are they? Since you didn't comment on the information I provided for this memory area, so I presume that you are agreeing with me for what it contains and there is nothing more to add?

PGA (Program Global Area) has some well defined memory allocations as well. Which you are saying are the

1. Stack space (Local variables for the process) and
2. UGA (User Global Area)
a. User Session Data
b. Cursor State
c. Sort Data

So then I could modify my model based on what you have said like this?

[SGA]
PRIVATE SQL AREA (IF ON A SHARED SERVER)
LIBRARY CACHE
ROW CACHE
SHARED POOL
BUFFER CACHE
REDO LOG BUFFERS

[PGA]
PRIVATE SQL AREA (IF ON A DEDICATED SERVER)
CURSORS --> Cursor State (Part of UGA)
SESSION MEMORY --> User Session Data (Part of UGA)
SORT AREA --> (Part of UGA)
STACK SPACE

Does this model look accurate and in agreement to ORACLE Documentation?

Thanks.

[dbasan]

Can anyone provide me with a more clear and easy break down for what components make up the SGA and what make up the PGA at the top level or tell me where I can find this information?


I just want to simplify the grouping of the components for each memory section. (SGA and PGA) I can dissect the subcomponents if I clearly know what the base components are

Goto the link and scroll down to cncpt024.gif for the grouping, also read the whole page for SGA and PGA definitions.

Also google for more

[mindScape]

Thanks dbasan, that's more like what I was looking for in a nutshell.
Thanks to you both for your helpful responses.