Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Friday, April 20, 2007

PGA memory hungry plsql codes and inevitable "ORA-04030" error

Hi,
Today, we found out memory eating, carnivore unix processes in our
production Oracle database server's operating system.
When we trace them, we realized
that they are Oracle client processes coming from Internet banking
connections. We analyzed the case and these sessions were growing in PGA
Memory usage. When they hit the Operating System memory limit they got
"ORA-04030" error but they did not stop , they continued to eat up the
memory (PGA). So we killed some of them but there were new sessions coming
from the same system behaving similarly.We examined the situation with the
people from development team and they found out that one day ago they
added a new dynamic parameter to one of the database packages generating
html output, they generating the html code by combining data from
different tables but one of these cursors that selects form these tables
gone into an infinite loop and caused that problem, so they corrected the
data that caused this infinite loop and they put extra controls to the
plsql code and user interface programs to prevent the error happening
again.

But after that we, as Oracle DBA team, decided to develop a database
procedure and database job that repeatedly checks for any sessions that
consumes PGA memory and have it email these sessions info to the DBA
Group for being able take early corrective action.

The following is the code that we developed and use for this purpose.

-----------------
select * from v$statname where statistic# = 20
/*
STATISTIC#|NAME|CLASS
20|session pga memory|1
*/

CREATE OR REPLACE PROCEDURE DBUSER01.Dba_Sess_Pga_Mem_Control IS
CURSOR c1 IS
SELECT t1.username, t1.SID, t1.serial#,
TRUNC (a_sess_pga_mem / 1024 / 1024) sess_pga_mem_mb
FROM v$session t1,
(SELECT *
FROM (SELECT   SID, VALUE a_sess_pga_mem
FROM v$sesstat
WHERE statistic# = 20 --session pga memory
AND TRUNC(VALUE / 1024 / 1024) >= 50
ORDER BY VALUE DESC)
WHERE ROWNUM <= 1) t2
WHERE t1.SID = t2.SID;

v_message   VARCHAR2(2000);
v_delimiter VARCHAR2(2);
r1          c1%ROWTYPE;
BEGIN
IF m_tatil_bul2(TRUNC(SYSDATE), 'B') = 0 THEN
-- If it is not on weekend

v_delimiter := '';
OPEN c1;
LOOP
FETCH c1
INTO r1;
EXIT WHEN c1%NOTFOUND;

v_message := v_message || v_delimiter;
v_message := v_message || 'Username '        || CHR(9) || CHR(9) || ' : ' || r1.username                 || CHR(10)
|| 'Sid,Serial# '     || CHR(9) || CHR(9) || ' : ' || r1.sid || ',' || r1.serial# || CHR(10)
|| 'PGA Memory (MB) '           || CHR(9) || ' : ' || r1.sess_pga_mem_mb          || CHR(10)
|| '------------------------------------------------------';
v_delimiter := CHR(10);

END LOOP;
CLOSE c1;

IF v_delimiter = CHR(10) THEN
DBUSER01.smtp.mail('dba@company01.com.tr',
'dba@company01.com.tr',
'There are sessions consuming too much PGA MEMORY (PGA Memory >= 50 MB) Please check !...',
v_message);
END IF;
END IF;
END;
/

Create the job that will call the above procedure in the specified intervals (3 mins in our case)

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job       => X 
,what      => 'DBUSER01.Dba_Sess_Pga_Mem_Control;'
,next_date => SYSDATE
,interval  => 'SYSDATE + 3/(24*60)'
,no_parse  => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

I hope you do not have any PGA memory hungry sessions.

1 comment:

Yuri said...

Hi, Ural
Read your very interesting blog article and code example on PGA and
ORA-04030 error. Do you know any detail or code example on what kind of SQL or PL-SQL code can cause this kind of problem in Oracle server?

Thanks,
Yuri.