-
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';
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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....
Originally posted by Shestakov
1. sort_area --> part of PGA not UGA:
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:
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.
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>
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 Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I also can red Tom Kyte "expert one to one", page 89.
"... 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]
-
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.
PGA (NOT PGA MAX) increased from 1303004 to 2103132.
real (not max!) uga increaced from 70480 to 71456.
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 Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
As I said earlier in this thread:
Originally posted by jmodic
.... Sort area memory is allcated partly from CGA (which is allways part of PGA) ....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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
|