Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why is UGA size so large?
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
globalTable.extend; globalTable(i) := s; end loop;
/* 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;
/* 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