UGA and PGA

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?