Out of Memory while running report
k_17Nov 18 2011 — edited Nov 22 2011Hi All,
DB Version :- 10.1.0.5.0
Our applications is used to genrate reports, where one report has limitations on the date range. This was due to 'out of memory' error which occured before and it was taking huge time to complete before. Now we have tune the SQL and its completeing in mins (performance increased by 80%) :-)
Now we wanted to remove the 10 year limit and run for past 30 years data. when we reomve limitation it throws the below error message.
"when others Error ORA-04030: out of process memory when trying to allocate 1872664 bytes (PLS non-lib hp,DARWIN) with query in ....package" .
While running the report i asked DBA to check the usage and he mentioned that its at peak. Only one report using 42 GB of RAM.
Can anyone provide me how to tackle the issue/start investigation to conclude the reason for the issue.
When the report runs there some dynamic SQL's generated and report is completed.
Regards,
Sunny