Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why is UGA size so large?

Re: Why is UGA size so large?

From: Marcel D?rr <MARCEL.D_at_GMX.CH>
Date: 1 Apr 2005 00:43:46 -0800
Message-ID: <646fd09f.0504010043.1f2e65be@posting.google.com>


Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<8pb6415r1cktb6ftfvcmmo1a1glfhf0av4_at_4ax.com>...
> On 24 Mar 2005 12:08:46 -0800, MARCEL.D_at_GMX.CH (Marcel D?rr) wrote:
>
> >I will post the result of your query as soon as it makes sense, tonight there
> >is only little traffic on the db due to easterv holiday.
>
> Which will render the results highly unrepresentative as you need the
> situation under load.

We now have a epresentative load on the system, at the moment there are
no ora-04030 yet, but we are close to it, because the VM size of the oracle process is now at about 2.75 GB.
The results are:
Connected to Oracle9i Release 9.2.0.5.0 Connected as system

SQL> select sum(pga_used_mem),sum(pga_alloc_mem),sum(pga_max_mem) from v$process;

SUM(PGA_USED_MEM) SUM(PGA_ALLOC_MEM) SUM(PGA_MAX_MEM)

----------------- ------------------ ----------------
        730816624          818277056       1584503216

I made some more investigations.
It seems that the memory consumption could be caused by temporarily allocating
large pl/sql index by tables and then removing most, but not all of the rows in them. It ssems pl/sql is only releasing that memory if the whole table is deleted. See my testcase further down. Does anybody know how to avoid this?

Testcase (need to be connected as sys):

create or replace package NestedTable is procedure AllocateTable;
end NestedTable;
/

create or replace package body NestedTable is

  type Table_t is table of varchar2(1024);    globalTable Table_t := Table_t();

  procedure OutputUGASize( titel_is varchar2)   is

     ugaSize_ln number;
  begin

     select value
     into   ugaSize_ln
     from   v$mystat s
     join   v$statname n on n.statistic# = s.statistic#
     where  n.name = 'session uga memory';
     
     dbms_output.put_line( titel_is || ': ' || ugaSize_ln);
  end OutputUGASize;
     
  

  procedure AllocateTable
  is

     i pls_integer;
     s varchar2(1024) := rpad('', 1024, 'A');
  begin
    /* Allocating a global Table that uses about 12 MB UGA */     OutputUGASize( 'UGA before allocating table');     for i in 1..1000000 loop
         globalTable.extend;
        globalTable(i) := s;
     end loop;

    OutputUGASize( 'UGA after allocating table');     

    /* now we remove all Rows but the last one */     for i in globalTable.first .. globalTable.last -1 loop

       globalTable.delete(i);
    end loop;
    /* the memory is not released, even after DBMS_Session.free_unused_memory */

    OutputUGASize( 'UGA after removing all but last row');     dbms_session.free_unused_user_memory;     OutputUGASize( 'UGA after DBMS_Session.free_unused_memory');

    /* only if we remove the last row, the UGA size will shrink again */

    globalTable.delete( globalTable.last);     OutputUGASize( 'UGA after deleting table');     dbms_session.free_unused_user_memory;     OutputUGASize( 'UGA after DBMS_Session.free_unused_memory');     

    /* now we try the other way around */     OutputUGASize( 'UGA before allocating table');     for i in 1..1000000 loop

        globalTable(i) := s;
     end loop;

    OutputUGASize( 'UGA after allocating table');     

    /* now we remove all Rows but the first one */     for i in globalTable.first+1 .. globalTable.last loop

       globalTable.delete(i);
    end loop;
    /* the memory is not released, even after DBMS_Session.free_unused_memory */

    OutputUGASize( 'UGA after removing all but first row');     dbms_session.free_unused_user_memory;     OutputUGASize( 'UGA after DBMS_Session.free_unused_memory');

    /* only if we remove the last row, the UGA size will shrink again */

    globalTable.delete( globalTable.first);     OutputUGASize( 'UGA after deleting whole table');     dbms_session.free_unused_user_memory;     OutputUGASize( 'UGA after DBMS_Session.free_unused_memory');   end AllocateTable;

end NestedTable;
/

Set serveroutput on;

begin
nestedTable.AllocateTable;
end;
/

drop package NestedTable; Received on Fri Apr 01 2005 - 02:43:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US