RE: UGA and PGA basic question

Hi Harvinder,  

The concepts you want to keep straight here are what actually goes into the PGA and the UGA. PGA is process memory. It's private to the user's background process. Anything there cannot be seen by another process. UGA is session level memory. It's session state information, PL/SQL package variables, and other stuff that must always be available to the session.  

In general, Oracle would prefer to keep stuff in the PGA, rather than SGA, since the PGA is private, and no latching is required for accessing any PGA memory.  

Now, think about shared server vs. dedicated server. In the dedicated server world, servers (meaning server or background processes) are NOT shared. A connection is established to the instance, a background process exists for the life of that session on that sustained connection to the database. In the shared server case, this is NOT true. During the life of a session, it may be assigned to many different server processes. So, UGA (that is, session level information) *can't* be in the PGA, cause if it was, when the session switched to another server process, it would lose access to it's session state, and worse, it would get some other session's state. So, in the shared server model, UGA (session level) memory MUST be shared by residing in the SGA, which can be seen by any server process.  

Now, as far a sort_area_size and sort_area_retained_size, what happens there? First, sort_area_size is allocated, as needed, in the PGA. It actually only needs to exist for the duration of a database call, and since (even in shared server mode) a session can't be switched from one server process to another during a database call, so, there is no danger of a session losing access to it's sort_area_size. The sort_area_retained_size is a bit different. This is used in the final step of final sort output, it's the buffer from which rows are fetched. So, if your query sorts 10,000 rows, and your arraysize is set to fetch 100 at a time, what happens to the other 9,900 rows? They are kept in a buffer whose size is bounded by the sort_area_retained_size. Since FETCH is a database call, and it may retrieve only 100 rows, (leaving 9,900 rows in the buffer), that data MUST persist between database calls. Therefore, it MUST reside in the UGA.  

So, if you look at it this way, it becomes clear what the answers to your questions are. If the server is dedicated, the UGA is a sub-heap of the PGA. It can be, cause there is no circumstance where the process would lose access to that sesssion-level data. If the server is shared, the UGA is a sub-heap of the SGA. In this case, it MUST be, otherwise, it would be possible for a session to "lose" the session-level data, if it were in the PGA. sort_area_size and hash_area_size reside in the PGA, sort_area_retained_size resides in the UGA.  

Hope that helps,  

-Mark  

PS I think I got that (mostly) right but comments and corrections are, of course, welcome.    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Tuesday, November 29, 2005 5:20 PM To: Oracle-L Freelists
Subject: UGA and PGA basic question

Hi,  

This is with reference to 10g on solaris 10.

It is clear from the documentation that in Shared Server mode UGA is part of the SGA and in dedicated mode it is in PGA.

1. if we are using workarea=auto and pga_aggregate_target is set In Dedicated mode since UGA is part of PGA all the sorting, hashing will come from PGA area, but if we are using Shared server do sorting, hashing still comes from PGA or since now UGA is part of SGA it comes from SGA?
2. if we are using workarea=manual and sort_area_size and hash_area_size is set Do these areas belongs to the UGA or PGA?

Thanks
--Harvinder
     

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 29 2005 - 17:03:28 CST