We have a new Dell server, OS Windows 2003 Server Enterprise 32bit with 32GB or RAM in it. Right now 64bit is not available to us because of Vendor issues so I can't go down that road yet. I keep running into ORA-04030 errors whether I put in the /3GB /USERVA=2560 switches or not. I got the /USERVA setting from Oracle's Best Practices for Windows Servers Guide. This new server is our current test box so we can upgrade our main application to 11g. Our current production server is running 10g and is set up the exact same way as this one using the same application, just on 10g. The only difference is that on 11g I am using Automatic Memory Management, it is at 18.104.22.168 right now. I plan to bump it up to 22.214.171.124 soon though.
The problem happens when Oracle.exe uses memory up to the limit of the Memory_target setting, then it throws the ORA-04030 error. It is like the process is reaching the Memory_target and then not using the memory it has, it is trying to grab more from the OS. I have tried setting this target lower and higher with the same results. I have also tried with and without the /3gb switch, same results. Dell hasn't offered much information, they told me to try Large Page Support.
I am at a loss, any help would be appreciated.
Thanks, I will pass that article along to my vendor and maybe they will get me a copy. I get all Oracle support through them so I don't have access to metalink. The reason I posted here was to try to get a jump start on what they might have me try, unfortunately it is a long process of going back and forth with the vendor checking metalink and then getting back to me to try something.
Have there been any noted problems with 11g and Automatic Memory Management?
1. Can you share the exact settings of yours?
- memory_target = ?
- memory_max_target = ?
- ksmggranule_size = ?
- pgamax_size = ?
2. Oracle 11g seems to have some minor problems with the granule size with automatic memory management and you might want consult to Oracle support for detail.
3. You can try traditional PGA heap dump with ORA-04030 error start upon that.
Dion Cho - Oracle Performance Storyteller
From my experience most database instances are too big memory wise. Administrators think "/3GB" means the SGA or MEMORY_TARGET can be set to 3 GB - but on Windows that's not true.
On Windows the whole database including all sessions is one process which consists of several threads. This process can allocate up to 3 GB memory. So the maximum size of everything SGA+PGA+UGA of all session must fit into this 3 GB. If you only have a few database connections you might use up to 2.5 GB for your SGA... but the more sessions you have the less memory is available for your SGA and the more memory is needed for your sessions.
Anyway i have also seen sessions which allocate a pl/sql table (stored in memory) and collect data for faster processing. Depending on the amount of data collected this table can be "huge" and cause your ORA-04040.
My blog: http://ronnyegner.wordpress.com
Thanks for the suggestions so far. This is what I got back from my vendor. It is possibly due to an unpublished Bug 7330813 that has to do with Function Based Indexes. This bug is fixed in 11.2.
-Unpublished Bug 7330813 deails
CBO may incur high hard parse times (as compared to 10g) for queries involving
Tables with functional indexes.
This Fix complements the fix for unpublished bug 6194582.
I have set the above parameter so we will see if it helps. Keep in mind that Oracle says not to use this Initialization parameter unless told to do so by Oracle Support.
May I know how to resolve this issue? Because one of our production environment (in AIX) hitting the same error in 126.96.36.199 intermittently.
Is set the hidden parameter resolve your issue permanently?