They said that the production server has 12GB available in production and this server has more memory than others. Do you think if a query runs great in other environment and takes only 4 sec. to execute, can it break the production environment and throw out of process memory error?
It is still the DBA's problem.
You said it works fine if you run the SQL on production from SQL*Plus/SQL Developer.
You said it breaks if you run the SQL on production from APEX.
Have the DBA read installation guide for APEX and double check some of the DB parameters.
It sounds like he/she skipped the step regarding Java RAM.
If that didn't solve the issue, your DBA, and only your DBA, can fix the problem by submitting an SR with Oracle support.
This is a database thing, not an APEX thing.
Thanks. That's what I tested day before. I have a page in pre-production and test environment and it takes around 4 seconds to execute. I tested the query from production DB and it took the same amount of time. But he said he tested the query from production DB last night and gave the same error. Can a query throw out of memory error?
Okay, I researched little more. I commented out the tree region and created a SQL report to see if I still get out of memory error. The report ran fine. So, its tree in production, throwing out of memory space issue. We are using Apex :4 in all environments and getting only this issue in production. Are we missing something?
You can get the SQL that is used for the tree.
BTW - Did you (or your DBA) contact Oracle Support yet?