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
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('email@example.com', 'firstname.lastname@example.org', '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.