DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: size of PGA?

  1. #1
    Join Date
    Mar 2002
    Posts
    303

    Question

    How can we calculate the size of PGA??

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Mar 2002
    Posts
    303
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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]

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by jmodic
    Some additional notes to additional notes....
    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

    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]

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width