8 Replies Latest reply on Jun 12, 2012 3:49 AM by klnghau

    11g and ORA-04030 problems

      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 right now. I plan to bump it up to 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.
        • 1. Re: 11g and ORA-04030 problems
          Check the metalink note :
          Diagnosing and Resolving ORA-4030 errors - 233869.1

          • 2. Re: 11g and ORA-04030 problems
            Pavan Kumar

            Refer to Metalink : Doc ID: 863562.1
            It will help out for your trouble shooting the Issue.

            - Pavan Kumar N
            • 3. Re: 11g and ORA-04030 problems
              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?
              • 4. Re: 11g and ORA-04030 problems
                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

                http://dioncho.wordpress.com (english)
                http://ukja.tistory.com (korean)
                http://dioncho.blogspot.com (japanese)
                http://ask.ex-em.com (q&a)
                • 5. Re: 11g and ORA-04030 problems
                  Ronny Egner
                  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.

                  Ronny Egner
                  My blog: http://ronnyegner.wordpress.com
                  • 6. Re: 11g and ORA-04030 problems
                    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.
                    Set "_replace_virtual_columns"=false;

                    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.
                    • 7. Re: 11g and ORA-04030 problems
                      This ended up being a problem on the Vendor's side. One of their procedures went crazy and started using up all available memory.
                      • 8. Re: 11g and ORA-04030 problems

                        May I know how to resolve this issue? Because one of our production environment (in AIX) hitting the same error in intermittently.

                        Is set the hidden parameter resolve your issue permanently?