How can we calculate the size of PGA??
Printable View
How can we calculate the size of PGA??
What do you mean by "calculating the size of PGA"? If you mean how to determine the size of the PGA for current sessions then look at V$SESSTAT.
For current PGA size of each session, use:
SELECT s.sid, n.name, s.value FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session pga memory';
For maximum PGA size each session has reached so far, use:
SELECT s.sid, n.name, s.value FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session pga memory max';
Thank's Jurij!
But me is not clear the difference between PGA and UGA!
If u know can u explain me in few words.
Thank's in advance
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.
Some addition notes:
1. sort_area --> part of PGA not UGA:
SQL> @pga_uga_sesssize
Total Total
PGA MAX UGA MAX
Memory PGA Memory UGA
(bytes) Memory (bytes) (bytes) Memory (bytes)
-------------------- -------------------- -------------------- --------------------
824,852 824,852 173,796 315,916
SQL> alter session set sort_area_size=1000000000;
SQL> @pga_uga_sesssize
Total Total
PGA MAX UGA MAX
Memory PGA Memory UGA
(bytes) Memory (bytes) (bytes) Memory (bytes)
-------------------- -------------------- -------------------- --------------------
824,852 824,852 173,796 315,916
SQL> select 1 from (select 1 from dba_objects order by object_name) where rownum=1;
1
----------
1
SQL> @pga_uga_sesssize
Total Total
PGA MAX UGA MAX
Memory PGA Memory UGA
(bytes) Memory (bytes) (bytes) Memory (bytes)
-------------------- -------------------- -------------------- --------------------
4,506,660 4,506,660 171,420 315,916
Oracle allocate sort area (and hash area too) in PGA, only if this memory need.
2) Where oracle allocate UGA in MTS mode:
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.
[Edited by Shestakov on 07-01-2002 at 06:38 PM]
Some additional notes to additional notes....
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).Quote:
Originally posted by Shestakov
1. sort_area --> part of PGA not UGA:
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:
http://www.ixora.com.au/newsletter/2000_11.htm
http://www.ixora.com.au/q+a/0011/08114657.htm
http://www.jlcomp.demon.co.uk/faq/uga.html
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.Code:Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like 'session _ga%';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 68476
session uga memory max 68476
session pga memory 221944
session pga memory max 221944
SQL> select count(*) from (select 1 from dba_objects order by object_name);
COUNT(*)
----------
28006
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like 'session _ga%';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 60008
session uga memory max 1147292
session pga memory 2318488
session pga memory max 2318488
SQL>
I also can red Tom Kyte "expert one to one", page 89.Quote:
Originally posted by jmodic
Some additional notes to additional notes....
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:Quote:
Originally posted by Shestakov
1. sort_area --> part of PGA not UGA:
http://www.ixora.com.au/newsletter/2000_11.htm
http://www.ixora.com.au/q+a/0011/08114657.htm
http://www.jlcomp.demon.co.uk/faq/uga.html
"... Each dedicated/shared server has PGA. ... It is sort area, hash area ..."
and
"Oracle 24x7" by Venkat Devraj, page 328.
So sort area, hash area --> part of PGA, Not UGA.
About script:
I had to used ur scripts. This is results:
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like 'session _ga%';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 76240
session uga memory max 76240
session pga memory 230456
session pga memory max 230456
SQL> select count(*) from (select 1 from dba_objects order by object_name);
COUNT(*)
----------
40617
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like 'session _ga%';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 70480
session uga memory max 631328
session pga memory 1303004
session pga memory max 1303004
SQL> alter session set sort_area_size=4000000;
-- ~ 4M
Session altered.
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like 'session _ga%';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 71456
session uga memory max 631328
session pga memory 1303004
session pga memory max 1303004
SQL> select count(*) from (select 1 from dba_objects order by object_name);
COUNT(*)
----------
40617
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like 'session _ga%';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 71544
session uga memory max 1878200
session pga memory 2103132
session pga memory max 2103132
PGA (NOT PGA MAX) increased from 1303004 to 2103132.
real (not max!) uga increaced from 70480 to 71456.
I have respect for you. But this is my mind.
I have instance, that has ~ 1000 connections
(800-900 thru shared server and 50-100 dedicated)
and 6GB memory.
And PGA/UGA is my headache, sometimes.
[Edited by Shestakov on 07-02-2002 at 12:08 AM]
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.Quote:
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 ..."
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.Quote:
PGA (NOT PGA MAX) increased from 1303004 to 2103132.
real (not max!) uga increaced from 70480 to 71456.
This is an interesting thread. I would add that besides the PGA and the UGA there is one more area used by each Oracle process: the CGA (call global area).
The CGA is a subheap of the PGA and this does not depend on whether the UGA is in the PGA or in the SGA.
Most people who use Java should also remember that Java call memory is allocated in the CGA.
As I said earlier in this thread:Quote:
Originally posted by julian
This is an interesting thread. I would add that besides the PGA and the UGA there is one more area used by each Oracle process: the CGA (call global area).
The CGA is a subheap of the PGA and this does not depend on whether the UGA is in the PGA or in the SGA.
Quote:
Originally posted by jmodic
.... Sort area memory is allcated partly from CGA (which is allways part of PGA) ....
Hi;
Thanks for this interesting deep discussion.
I did not imagine that that this simple act of sorting in Oracle has so complex implemantation.
In future i have to take a closer look on both, PGA and UGA!
Orca