Oracle Turf

Rajiv Iyer's observations on Oracle Performance

ORA-04030

leave a comment »

ORA-04030 has been a frequent topic of discussion. We have seen people suggesting ways of avoiding it either by setting appropriate process limits in the OS or fixing bugs associated with it in different Oracle Releases.  But recently I saw a stored procedure being responsible for this error.

One of our Non Production Database servers running Oracle 10.2.0.4 was experiencing high memory consumption. The culprit was a session executing a stored procedure and using 3GB of memory. The developers had no reason to suspect any problem with the code which had been working for ages. Indeed nothing had changed in the code. But then, something HAD changed. Let’s explore.

Let’s consider the version of the procedure stripped to the bare minimum.

create table tab_case(
file_id number,
case_id varchar2(255),
submit_date date
);

insert into tab_case(file_id,case_id,submit_date) values (145,100,sysdate-7);
insert into tab_case(file_id,case_id,submit_date) values (146,101,sysdate-7);
insert into tab_case(file_id,case_id,submit_date) values (199,1031,sysdate);
insert into tab_case(file_id,case_id,submit_date) values (200,1405,sysdate);
commit;

Create types to hold the error records.

create or replace type err_rec_type as object (
rec_number number,
err_message varchar2(255)
);
/
create or replace type err_tbl_type as table of err_rec_type;
/

Create the stored procedure.
It’s a very simple procedure which fetches data through a cursor and assigns it to a rowtype variable. Any error is caught in the exception block and the details are captured in the error record type. The loop is supposed to continue till the last record is fetched from the cursor.

create or replace procedure case_prc(p_err_rec out err_tbl_type) as
case_rec tab_case%rowtype;
cs sys_refcursor;
v_err_index number :=1;
v_ora_mesg varchar2(255);
begin
p_err_rec := err_tbl_type();
open cs for select file_id,case_id,submit_date from tab_case where file_id in (145,146);
loop
begin
fetch cs into case_rec;
exit when cs%notfound;
exception
when others then
dbms_output.put_line('excption');
v_ora_mesg := substr(sqlerrm,1,255);
p_err_rec.extend;
p_err_rec(v_err_index) := err_rec_type(case_rec.file_id,v_ora_mesg);
v_err_index := v_err_index + 1;
end;
end loop;
close cs;
end;
/

Execute the procedure. It runs almost instantly

declare
p_err_rec apps.err_tbl_type;
begin
case_prc(p_err_rec);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22

Now let’s assume there is a business requirement and we add a new column to the table TAB_CASE

alter table tab_case add op_unit varchar2(5) default 'IND';

And we rerun the procedure

    Observations:

1) Column mismatch in the following part of the code causes an exception

fetch cs into case_rec;
exit when cs%notfound;

(case_rec is expecting an additional column op_unit)
This error always caught by the exception block and this result in an infinite loop.
4) Finally the session ends with an ORA-4030 error when error_tbl_type is not longer able to extend itself to hold more records.
5) The alert log shows the following messages which confirm the same.
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmuccst: adt/record)
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
So to summarize, an inefficient PL/SQL may cause an ORA-04030 error. Also other accompanying error messages may throw more light on the issue.

Advertisements

Written by rajiviyer

August 17, 2013 at 1:06 am

Posted in PL/SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: