RE: UGA and PGA basic question
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <Harvinder.Singh@xxxxxxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Nov 2005 18:00:54 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
