UGA and PGA
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: UGA and PGA

  1. #1
    Join Date
    Mar 2002
    Posts
    301
    Uga and Pga:

    1. The sort area size parameter of the database has been set to 50mb and the sort area retained size parameter has been set to 0.

    As soon as the connection is established the amount of memory allocated for UGA and PGA are as follows:

    NAME VALUE
    ------------------------ ----------
    session uga memory 64904
    session uga memory max 64908
    session pga memory 139852
    session pga memory max 139852

    2. Declare a bind variable of varchar2 type and then
    observe the memory allocation for UGA and PGA.

    SQL> var a varchar2
    SQL> @uga_pga

    NAME VALUE
    -------------------------- ----------
    session uga memory 64904
    session uga memory max 86096
    session pga memory 156876
    session pga memory max 156876

    You can note a increase in the value of UGA memory max and Session PGA memory.

    3. Declare a couple of bind variables of number type and then observe the values
    for UGA and PGA.

    SQL> var b number
    SQL> @uga_pga

    NAME VALUE
    ------------------------- ----------
    session uga memory 64904
    session uga memory max 86096
    session pga memory 156876
    session pga memory max 156876

    SQL> var c number
    SQL> @uga_pga

    NAME VALUE
    ------------------------ ----------
    session uga memory 64904
    session uga memory max 86096
    session pga memory 156876
    session pga memory max 156876

    It is observed that there is no increase in the amount of memory allocated.

    4. Add 2 more bind variables using Varchar2 datatype. In this case also there is no
    increase in the memory.

    SQL> var d varchar2
    SQL> @uga_pga

    NAME VALUE
    ------------------------- ----------
    session uga memory 64904
    session uga memory max 86096
    session pga memory 156876
    session pga memory max 156876

    SQL> var e varchar2
    SQL> @uga_pga

    NAME VALUE
    ------------------------ ----------
    session uga memory 64904
    session uga memory max 86096
    session pga memory 156876
    session pga memory max 156876

    It looks like Oracle manages all the bind variables declared in the space it
    allocated initially(21188 bytes). Is it so?

    5. Issue the following statement and then see the memory allocation:

    select object_name from user_objects
    order by object_type, object_name
    /

    SQL> @uga_pga

    NAME VALUE
    ------------------------ ----------
    session uga memory 69140
    session uga memory max 94568
    session pga memory 190924
    session pga memory max 190924

    It is observed that the Session pga memory value shoot up to 186 Kb from 156 Kb which
    determines so much amount of space was utilised for sorting.
    The value of UGA also increased by 10k(inspite of having sort area retained size
    to 0).

    In this case what would have made Oracle to increase the value of UGA.

    6. Alter the value for sort_area_retained_size for your session and issue the query and then check the memory.

    SQL> alter session set sort_area_retained_size=1000000 ;

    Session altered.

    SQL> select owner, object_name, object_type from dba_objects
    2 order by 1,3,2
    3 /

    SQL> @uga_pga

    NAME VALUE
    ------------------------ ----------
    session uga memory 69144
    session uga memory max 1128636
    session pga memory 3719204
    session pga memory max 3719204

    It is observed that the session uga memory max shoot up to 1mb(the value for sort_area_retained_size) and the value for

    session pga memory shot up to 3.5mb
    (so much amount of space was utilised from the available 50mb).

    Questions: why is the value for session uga memory does not increase?
    The values stored in UGA are: Session state(variables, cursor info, sort_area_retained_size etc).
    The values stored in PGA: sort_area_size
    Is there any other structures stored in UGA and PGA?
    Say No To Plastics

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Vijay,
    Pretty longish check and test Q.
    Well I did see that you kinda figured out why the bind variables do go about expediting the whole fetch process.

    How your Q's can be summed up as are:

    What's the purpose of a sort_area_size and the retained size.


    When you ask why it doesn't increase, the session uga because after sorting is done it falls back or so to speak freed when the sorting is over that's why it's not increasing.

    BTW: You assumtion that pga contains only sort area is kinda wrong. It's one of the many many structures that're there.




    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

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