Malloc errors in stored procedure - Reposted
Date: Wed, 10 Aug 1994 19:06:25 GMT
Message-ID: <940810140625_at_raghu.im.hou.compaq.com>
We are experiencing the following errors quite INCONSISTENTLY. Any pointers to help in troubleshooting or having a workaround would be appreciated.
Environment :
Sco Unix System V/386 Release 3.2
ORACLE7 Server Release 7.0.15.4.0 - Production
With the procedural and distributed options
PL/SQL Release 2.0.17.1.0 - Production
SQL*Plus: Release 3.1.2.3.1
Salient features of program - Stored Procedure :
...
...
pl_reg_nbr registrant.reg_nbr%type;..
....
cursor outer_regt_curs is
select reg_nbr from registrant;
cursor sel_detail is
select country_code, customer_id, intro_date, expire_date, first_name, surname, name_prefix, name_suffix, job_cat, job_title, max_site_emp, min_site_emp,transaction_datetime,transaction_type from registrant where reg_nbr = pl_reg_nbr;... .. .. begin ... for reg in outer_regt_curs LOOP pl_reg_nbr := reg.reg_nbr; FOR det in sel_detail LOOP
....
....<< calls various procedures, which only check
data and insert
....
....
END LOOP; .... END LOOP;
Error generated :
ERROR:
ORA-04030: out of process memory when trying to allocate 32428 bytes (pga
heap,ksm stack)
ORA-07324: smpall: malloc error while allocating pga.
AT&T System V/386 Error: 12: Not enough space
begin xtrac_regt_spo; end;
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 9520 bytes (PLS non-lib hp,PL/SQL STACK) ORA-07324: smpall: malloc error while allocating pga.AT&T System V/386 Error: 12: Not enough space
Alternatives Tried (which all failed):
1. Replaced the FOR LOOP with a WHILE ( and OPEN/FETCH/CLOSE) LOOP. 2. Added / removed indexes. 3. Removed procedures that are being called by the body of the program. 4. Added just one field (reg_nbr) to be selected by the outer cursor. 5. Doubled the memory available by each process running under UNIX. Thisresulted in the `pga' being allowed to increase to 48M before the error was generated.
The result of trying all the above was the same for all - The process just allowed different quantities of data (number of rows from registrant) to be processed before it failed.
Thanks,
R.Raghu Ram
(713) 378 0487
Received on Wed Aug 10 1994 - 21:06:25 CEST