PGA leaks

Jump to 2019 update

Here’s a simple script that I created a short time ago while investigating a memory problem on a client site. The purpose of writing the script was, as always, to create a model that stripped the client’s code to the bare minimum in an attempt to work out the root cause of a problem. (Warning: if you want to run this script, your Oracle shadow process will grab about 1GB of PGA memory)

rem
rem     Script:         pga_leak_02.sql
rem     Author:         J P Lewis
rem     Dated@          June 2009
rem
rem     Last tested
rem             11.1.0.6        Mostly fixed
rem             10.2.0.3        Huge memory impact
rem

define m_string_length = 200

drop table t1 purge;
create table t1(v1 varchar2( &m_string_length ));

create or replace procedure p1 (
        i_rowcount      in number       default 1000000,
        i_bulk_pause    in number       default 0,
        i_forall_pause  in number       default 0,
        i_free_pause    in number       default 0
)
as

        type w_type is table of varchar2( &m_string_length );
        w_list          w_type := w_type();
        w_free          w_type := w_type();

begin
        for i in 1..i_rowcount loop
                w_list.extend;
                w_list(i) := rpad('x', &m_string_length );
        end loop;

        dbms_lock.sleep(i_bulk_pause);

        forall i in 1..w_list.count
        insert into t1 values(w_list(i));

        dbms_lock.sleep(i_forall_pause);

        commit;

        w_list := w_free;
        dbms_session.free_unused_user_memory;

        dbms_lock.sleep(i_free_pause);

end;
/

The procedure simply fills a pl/sql array with data then uses the forall insert syntax to write the whole lot into a table. An earlier version of my code used a select with bulk collect to load the array (emulating the job run by the client more closely), but I wanted to make the code even simpler which is why it’s just a simple loop.

After creating the table and procedure I simply called the procedure and, in a slightly more complex version of the code,  introduced some long wait times (using dbms_lock.sleep) between steps. Then I used another session (logged on as SYS) to monitor what demands this procedure made PGA memory as it ran.

This is the code I ran from the monitoring session (supplying the SID from the first session as the input parameter):

rem
rem     Script:         pga_check.sql
rem     Author:         J P Lewis
rem     Dated:          June 2009
rem
rem     Last tested
rem             11.1.0.6
rem             10.2.0.3
rem
rem     Simple script to cross check for a given SID
rem             v$process
rem             v$process_memory
rem             v$sesstat
rem

column name format a40

column  value           format  999,999,999,999

column  category        format  a10
column  allocated       format  999,999,999,999
column  used            format  999,999,999,999
column  max_allocated   format  999,999,999,999

column  pga_used_mem            format  999,999,999,999
column  pga_alloc_mem           format  999,999,999,999
column  pga_freeable_mem        format  999,999,999,999
column  pga_max_mem             format  999,999,999,999

define m_sid = &1

select
        name, value
from
        v$sesstat ss,
        v$statname sn
where
        sn.name like '%ga memory%'
and     ss.statistic# = sn.statistic#
and     ss.sid = &m_sid
;


select 
        pid,
        category,
        allocated,
        used,
        max_allocated
from 
        v$process_memory 
where
        pid = (
                select  pid 
                from    v$process 
                where   addr = (
                                select  paddr 
                                from    V$session 
                                where   sid = &m_sid
                                )
                )
;


select
        pid,
        pga_used_mem,
        pga_alloc_mem,
        pga_freeable_mem,
        pga_max_mem
from
        v$process 
where   addr = (
                select  paddr 
                from    V$session 
                where   sid = &m_sid
                )
;

The figures I’m after are just the session and process memory from v$sesstat, and the two different ways of reporting process memory from v$process and v$process_memory. These are the results I got on a database running 10.2.0.3. The set immediately below reports the state of PGA memory just after after the creation of the array, the set of results further down the page reports the same information just after the call to the procedure has ended.

NAME                                                VALUE
---------------------------------------- ----------------
session uga memory                                221,824
session uga memory max                            221,824
session pga memory                            313,843,284
session pga memory max                        313,843,284


CATEGORY          ALLOCATED             USED    MAX_ALLOCATED
---------- ---------------- ---------------- ----------------
SQL                   1,008              104           55,276
PL/SQL               22,412           17,848           22,412
Other           627,269,497                       627,269,497


    PGA_USED_MEM    PGA_ALLOC_MEM PGA_FREEABLE_MEM      PGA_MAX_MEM
---------------- ---------------- ---------------- ----------------
     235,870,025      627,292,917                0      627,292,917

You’ll notice several key points.

First (obviously) v$sesstat tells us that we’ve used a lot of memory – which isn’t too surprising since we generated 1,000,000 character strings of length 200, so we know we’re going to see at least a couple of hundred megabytes of memory being used.

But there’s much more to see in these three result sets. The report from v$process_memory says we’ve allocated more than 600MB in the “Other” category – which is a little surprising since (a) we know that we’ve used the memory for a pl/sql array – so why is it in “Other” – and (b) it’s twice as much memory as reported in v$sesstat and (c) we don’t see any of it in the “Used” column.

In fact, a dump of the pga and uga heaps (oradebug dump heapdump 5) shows that we have 313mb of RAM in the pga heap, of which 77MB is marked as free (but not yet released from the heap). From this we we can probably infer that the code maintaining v$process_memory is broken. I suspect that we really ought to see the 313MB in both the “Allocated” and “Used” columns with, quite possibly, the 77MB appearing in a separate row (not visible in this report) for the category “Freeable”.

Note that the report from v$process is closer to the actual state given by the heap dump. It shows pga_used_mem at 236M – which means that the 77M free from the heap dump is correctly subtracted from the 313MB total, but possibly that 77MB should be reported as “PGA_Freeable_Mem”, rather than disappearing completely. This view still has a problem, though: the pga_alloc_mem and pga_max_mem are both displaying the same doubling effect as v$process_memory.

Bottom line: v$sesstat seems to show you most of the truth, with v$process helping you to seperate the freeable memory out from the currently allocated memory, but both v$process and v$process_memory are not to be trusted. (Some of these anomalies are still present in 11.1.0.6 by the way).

The next bit of output shows you the report results after the procedure call had completed:

NAME                                                VALUE
---------------------------------------- ----------------
session uga memory                            498,681,064
session uga memory max                        498,681,064
session pga memory                            598,662,740
session pga memory max                        911,924,820


CATEGORY          ALLOCATED             USED    MAX_ALLOCATED
---------- ---------------- ---------------- ----------------
SQL                   3,024              516           55,276
PL/SQL          488,711,516        4,222,224      488,711,516
Other           110,135,753                       736,607,661


    PGA_USED_MEM    PGA_ALLOC_MEM PGA_FREEABLE_MEM      PGA_MAX_MEM
---------------- ---------------- ---------------- ----------------
     498,885,505      598,850,293                0    1,225,374,453

This is extraordinary. The session has allocated – and is still holding – more than 488MB in the PL/SQL category just because of that one million row insert. (My client was processing 3.8 million rows, and they “lost” 7.7GB of memory to this operation – the memory loss gives the appearance of growing geometrically with the number of rows).

In fact the penality is more than that 488MB; the memory that had been allocated for the bulk collect in category “Other” has been released and the 110MB still remaining in that categiry is also a side effect of the forall insert.

Note, by the way, that the pga_max_mem from v$process_memory is still 313M higher than it should be, as is the max_allocated in the “Other” category in v$process_memory.

When I first saw this behaviour I decided it was obviously a bug and spent a good 20 minutes searching Metalink for possible matches without success (I was looking for “forall insert”, if I’d tried “bulk insert” I would have had more luck) – so I forwarded my test case to the rest of the Oak Table to see if they had any thoughts about it and got a fairly prompt reply from Tanel Poder that this was bug 5866410. (He’d done a pga heapdump, and searched Metalink for the label of the memory type that was using most of the space – like all good strategies it was so obvious after it had been explained !)

The bug is a pl/sql memory leak in “forall insert…” It’s fixed in 11.1.0.6 and 10.2.0.5, with backports available to 10.2.0.3 and 10.2.0.4 on a couple of platforms so far (Solaris and IBM ZLinux as I write).

So if your code does very large “forall insert” calls and you see Oracle error ORA-04030 from time to time, or workarea operations dumping to disc unexpectedly, you may temporarily be losing large amounts of memory to this bug – in which case check Metalink for the bug number and see if there’s a patch that’s good for your platform. (And if you’re on 10.2.0.3 or 10.2.0.4 and there isn’t a patch for your platform yet, there’s a reasonable chance you can get one created since it’s already been done on a couple of other platforms.)

Update Sept 2019

I’ve just discovered a nice looking note on MoS describing how to populate a dynamic performance view called v$process_memory_detail, which will allow you to break down the details of where in your PGA Oracle is allocating (and, possibly, leaking) memory. The document is: How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1), the basic command to populate the view for a process is:

alter session set events'immediate trace name PGA_DETAIL_GET level {PID}';

The data is dumped into a structure that’s stored in the SGA, not into a local structure so can be seen across all sessions if different users start dumping the data for different process IDs. The data for the process can be updated by further calls of the same event, and to clear the data from the view you can finally issue:

alter session set events'immediate trace name PGA_DETAIL_CANCEL level {PID}';