SGA and PGA Components
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SGA and PGA Components

  1. #1
    Join Date
    Mar 2007
    Posts
    19

    SGA and PGA Components

    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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.
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Mar 2007
    Posts
    19
    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.

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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

  5. #5
    Join Date
    Mar 2007
    Posts
    19

    Thumbs up

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width