If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

PGA = Process Global Area - it is a heap of memory where Oracle server process stores its private informations (accessiblke only to that particular process). It represents a process state.

UGA = User Global Area - it is a heap of memory where user session stores its information. So it represents a session state. Sort area, for example, is allocated in each session's UGA.

Depending on your configuration (multithreaded or dedicated) UGA can be contained in two various supperheaps of memory.

If you are using dedicated server mode, a process and a session are almost a synonym. Each session has its own dedicated process and one process belongs only to one session. So in this case oracle can allocate UGA as a subheap of PGA.

But if you are using MTS, then each process can serve many sessions. In this case UGA can not be allocated as a part of particular process, it must be allocated from a memory part that all server process can access. That common area that each process can acces is SGA, so in MTS mode UGA for each session is allocated as a part of SGA.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

if LAGRE_POOL set to 0 (not allocated) then uga allcocate in shared memory.
if LAGRE_POOL set more then 0 then uga allcocate in large pool.
We should be little bit carifull with size of large area in MTS mode.

Not quite, or at least not so simple. Sort area memory is allcated partly from CGA (which is allways part of PGA) and partly from UGA (which is sometimes part of PGA and sometimes part of SGA. SORT_AREA_RETAINED_SIZE is the size of sort area that must retain its contents over session migration from one shared server to the other in MTS because this sort size might be used later for the fetch phase, so this part of sort memory is allways allocated from UGA. The actual "single sort runs" are finished in one call, so there is no need for them to be available to other processes (even in MTS mode), so they are simply allocated from PGA (up to the SORT_AREA_SIZE).

I think best resources regarding sorting can be found in Steve Adams and/or Jonathan Lewis's book and their web sites. Here are some links to their thoughts about sorting:

Shestakov, regarding your metrics, there are some isues that are unclear.

1. It is not very clear what your pga_uga_sesssize.sql script is actually doing (although I suspect it simply queries v$sesstat for UDA nad PGA statistics) and the output is hard to read.
2. You have set your SORT_AREA_SIZE to a very large number, but you haven't give us any clue what was the size of SORT_AREA_RETAINED_SIZE when the query was run
3. What release are you using? Because your query

select 1 from (select 1 from dba_objects order by object_name) where rownum=1;

in newer releases (8i and higher) will actually not perform any ORDER BY sort! It will only perform ORDER BY STOPKEY sort, which actuall requires almost no sort size at all!

Change the query to

select count(*) from (select 1 from dba_objects order by object_name);

if you wan't to prevent a lenthy query output, that wil actually perform ORDER BY sort. Or if using sqlplus script simply use SET TERMOUT OFF or SET AUTOTRACE TRACEONLY and run simple query

select 1 from dba_objects order by object_name;

to force oracle to perform a sort.

Now, regarding the demonstration which shows in which part of memory sort is performed, here is mine, in a dedicated server mode in 9.0.1. Both SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE are set to 1M.

We can see that UGA size used for the sort was approx 1M (the size of SORT_AREA_RETAINED_SIZE) while the size of PGA increased for 2M. The first meg of PGA was used from CGA for two ore more sort runs (the size of SORT_AREA_SIZE), while the other meg is the one from UGA as explained above, which was needed for the final sort. If this was MTS configuration, then I belive both "uga memory max" and "pga memory max" wold each be raised for approx. 1M, as in that case UGA would not be part of PGA.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

Originally posted by Shestakov I also can red Tom Kyte "expert one to one", page 89.
"... Each dedicated/shared server has PGA. ... It is sort area, hash area ..."

Read also pages 70 to 74, section "PGA and UGA", where Tom is much more specific about this topic: ".... the SORT _AREA_SIZE is generally allocated out of your PGA and SORT_AREA_RETAINED_SIZE will be in your UGA. ....". Which is exactly what I have written in my previous reply.

It is because how various heaps or subheaps of memory are dealocated after they are not used any more. PGA and UGA are treated differently in this regard. When UGA memory allocation (SORT_AREA_RETAINED_SIZE) is not needed any more, it is immediately dealocated and returned to its supperheap (to PGA in dedicated mode, to SGA in MTS). That's why you have too look at "session UGA memory max" memory, not "session UGA memory" if you wan't to know how much memory was allocated for sorting in UGA. On the other hand, portions of memory in PGA are normaly not dealocated from PGA heap immediately, that's why you'll almost allways see "session PGA memory" more or less equal to "session PGA memory max". See in Tom's book, he has very clear analysis of this on pages 71-74.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?