-
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
-
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)
--- 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|