Code and think

PL/SQL script with memory leak

For Oracle, you can write PL/SQL scripts. Recently, I prepared one and tested it. All went well, until it reached a large DB. There it failed.

The error was:

ORA-03113: end-of-file on communication channel

However, when inspected, the actual error appeared to be:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

In order to find a root cause, I used some queries to find what is happening with the PGA memory when running my script. The scripts used are:

-- check the pga usage by all session
select s.osuser osuser,s.serial# serial,se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,v$statname n,v$session s
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
and s.sid=se.sid
group by n.name,se.sid,s.osuser,s.serial#
order by 2;

--find PGA usage for a specific session
SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM
v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic#
AND SID = 749;

Another thing that proved to be very useful in the process was a very long-running loop. Originally, I was using cursor, however, I would need a huge DB to be able to reproduce the issue. Instead, of complicating with that, I just modified my loop not to use a cursor, but a counter like this:

FOR j in 1..20000000 loop
-- FOR curs in MY_CURSOR LOOP
-- add a select into statement that will fill your cursors data

This might not always work, as now I am testing on one and the same case. However, if it does, it is a very quick and simple way to simulate a long-running loop.