Exception of type 'System.OutOfMemoryException' was thrown.
Dear Sir,
Our Oracle Server 10g is with 2GB Ram on Windows 2003 Server
We run little complex sql with two subquery in between. This sql runs perfectly OK on our database when we run sql for
selected units say unit in ('38' ,'84' ,'81' ,'01' ,'20' ,'10') etc. and it approx returns about 15000 Rows and there is no problem
This we have tested for all the units in unit in where clause without any problem.
When we run the same sql for all units i.e ep.unit IN ('38' ,'84' ,'81' ,'01' ,'20' ,'10' ,'02' ,'03' ,'04'
,'06' ,'07' ,'08' ,'09' ,'11' ,'12' ,'33' ,'35' ,'36' ,'40' ,'50' ,'60' ,'61' ,'62' ,'63' ,'64' ,'99' ,'32' ,'18' ,'19' ,'34'
,'15' ,'28' ,'29' ,'39' ,'16' ,'21' ,'22' ,'23' ,'82' ,'00' ,'83' ,'25' ,'26' ,'27' ,'41' ,'42' ,'31' ,'55' ,'14' ,'24' ,'17'
,'80' ,'43' ,'65' ,'44' ,'85' ,'90' ,'37' ,'05' ,'30' ,'13') which returns approx 110000 rows ->It throws this
exception error after executing for say 30 seconds.
It is for sure, there is no problem in the SQL Statement.
Kindly advise as to tune the Database so as to handle this problem.
Suresh Bansal
following is complete sql with error statement
============================
Error:
Exception of type 'System.OutOfMemoryException' was thrown.
select e.emastid ,e.name ,ep.leftd ,ep.trdate ,ep.joind , timeofplan.shift pshift ,'' leave ,'' holiday ,0 timei ,0 timeo
,0 shifttime ,0 autono ,timeofplan.vrdate ,nvl(ep.techcom ,'C') techcom , (ep.basic+ep.vda) salary , nvl(ep.refresh ,'N')
refresh ,desig1.name desig ,ep.gradeid ,ep.Unit ,ep.Ccentreid , ccentre1.abname ccentre_abname , ep.Dmastid ,dmast1.abname
dmast_abname ,dmast1.name dmast_name ,ep.workdepid ,workdep1.name workdep_name ,workdep1.abname workdep_abname ,ep.desigid
,ep.desigid_print ,ep.processid , process1.abname process_abname ,emast.emastid mastid1 ,emast.name name1 ,timeofr.timeofrid
, term1 , term2 , term3 ,term4 ,term5 , rate1 , rate2 , rate3 , rate4 , rate5 ,nvl(umast.ovrate ,0) ovrate ,nvl(ep.fooding
,'N') fooding , nvl(foodingperday ,0) foodingperday ,decode(nvl(ep.kitded ,'N') ,'N' ,0 ,ep.KITPD) KITPD from
PAYROLL.emast e ,PAYROLL.emast_pay ep ,PAYROLL.desig desig1 ,PAYROLL.ccentre ccentre1 ,PAYROLL.dmast dmast1 ,PAYROLL.workdep
workdep1 ,PAYROLL.Process Process1 ,PAYROLL.timeofr ,PAYROLL.umast ,PAYROLL.emast ,
(SELECT timeofplan.emastid , timeofplan.workgroupid , shift , vrdate , autono , timeofs_autono From PAYROLL.TimeOfPlan Where
TimeOfPlan.Emastid Is Not Null and timeofplan.vrdate >='26-Mar-2011' and timeofplan.vrdate <='25-Apr-2011' Union All SELECT
emast.emastid , timeofplan.workgroupid , shift , vrdate , autono , timeofs_autono From PAYROLL.TimeOfPlan ,PAYROLL.emast
Where TimeOfPlan.workgroupid = emast.workgroupid AND timeofplan.EMASTID IS NULL and timeofplan.vrdate >='26-Mar-2011' and
timeofplan.vrdate <='25-Apr-2011' and EMAST.emastid not in(SELECT distinct timeofplan.emastid FROM PAYROLL.timeofplan Where
TimeOfPlan.Emastid Is Not Null and timeofplan.vrdate >='26-Mar-2011' and timeofplan.vrdate <='25-Apr-2011' )) TimeofPlan
Where e.emastid = ep.emastid and ep.joind <= '25-Apr-2011' and (ep.leftd is null or ep.leftd > '26-Mar-2011') and
(ep.trdate is null or ep.trdate > '26-Mar-2011' ) and ep.unit IN ('38' ,'84' ,'81' ,'01' ,'20' ,'10' ,'02' ,'03' ,'04'
,'06' ,'07' ,'08' ,'09' ,'11' ,'12' ,'33' ,'35' ,'36' ,'40' ,'50' ,'60' ,'61' ,'62' ,'63' ,'64' ,'99' ,'32' ,'18' ,'19' ,'34'
,'15' ,'28' ,'29' ,'39' ,'16' ,'21' ,'22' ,'23' ,'82' ,'00' ,'83' ,'25' ,'26' ,'27' ,'41' ,'42' ,'31' ,'55' ,'14' ,'24' ,'17'
,'80' ,'43' ,'65' ,'44' ,'85' ,'90' ,'37' ,'05' ,'30' ,'13') and ep.unit = umast.unit and nvl(ep.dummy ,'N') = 'N' and
decode(nvl(ep.desigid_print ,'aa') ,'aa' ,ep.desigid ,ep.desigid_print) = desig1.desigid(+)
and ep.ccentreid = ccentre1.ccentreid(+) and ep.dmastid = dmast1.dmastid(+) and ep.workdepid = workdep1.workdepid(+)
and ep.Processid = Process1.Processid(+) and emast.emastid = ep.emastid and ep.timeofrid = timeofr.timeofrid(+) and
e.emastid = timeofplan.emastid order by emast.emastid , e.emastid , timeofplan.vrdate