1 2 Previous Next 27 Replies Latest reply on May 16, 2008 5:09 PM by 635471 Go to original post
      • 15. Re: ORA-04030 Out of Process Memory
        Billy~Verreynne
        > Memory problems on Windows have sometimes simple yet stupid solutions - RESTART the Machine.

        You reckon? Use that Large Hammer each time around to fix (not solve) the problem?
        • 16. Re: ORA-04030 Out of Process Memory
          637640
          After doing many trials, still my Oracle chicken is not hatching eggs, she keeps throwing out of memory or shared mem realm error.

          Following is the issue i have set my SGA settings as follows.
          (My system RAM is 8GB, Win 2003 32 bit OS with Oracle 32 bit 10G R2)
          SGA_TARGET = 0;
          buffer = 2.5GB,
          large_pool = 1.5GB
          shared_pool = 512mb
          java_pool = 512mb
          pga = 1GB

          I still get the following error. Still no luck

          ORA: 27100: shared memory realm already exits

          Restarted the system several times to resolve this, did not work.
          changed the spfile to take the old parameters, but I end up with out of memory.
          Our system is a warehouse system for DSS.
          • 17. Re: ORA-04030 Out of Process Memory
            Aman....
            Hi,
            As I mentioned earlier too, you need to contact support for 4030/4031.They can become really tricky and as its happening all the time for you, I suggest you raise an SR and let support take the charge. There may be a memory leak and they will be in a best position to support you.
            Aman....
            • 18. Re: ORA-04030 Out of Process Memory
              277993
              Following is the issue i have set my SGA settings as
              follows.
              (My system RAM is 8GB, Win 2003 32 bit OS with Oracle
              32 bit 10G R2)
              SGA_TARGET = 0;
              buffer = 2.5GB,
              large_pool = 1.5GB
              shared_pool = 512mb
              java_pool = 512mb
              pga = 1GB

              I still get the following error. Still no luck

              ORA: 27100: shared memory realm already exits

              Restarted the system several times to resolve this,
              did not work.
              changed the spfile to take the old parameters, but I
              end up with out of memory.
              Our system is a warehouse system for DSS.
              I can see you went for the opposite of my suggestion. Why not set it the way I suggested ? i.e. set SGA_TARGET and leave the rest as zero.

              Your settings so far are still either too big or your changes are not being used. This could be as a result of making changes in pfile when your system is atsrting with spfile.

              Can you confirm how you are making these changes to spfile. Also can you confirm that your Windows is using all 8GB.

              Note: if all fails, you can Delete instance with oradim, Reboot Windows and Recreate instance with oradim.
              • 19. Re: ORA-04030 Out of Process Memory
                Amit_DBA
                I am not sure if you have set /3Gb switch or /PAE switch as of now. In case you have set both also, your memory configuration is not correct


                SGA_TARGET = 0;
                buffer = 2.5GB,
                large_pool = 1.5GB
                shared_pool = 512mb
                java_pool = 512mb
                pga = 1GB


                When you are using /3GB and /PAE switch together , you will have 2.7 gb of memory for SGA+PGA+AWE_WINDOW_SIZE (excluding buffer cache)

                As 1 gb is default value for AWE_WINDOW_SIZE , you are left with 1.7 Gb of memory. In your case you have allocated 3 Gb for shared_pool,large_pool,java pool and PGA which is incorrect

                -Amit
                http://askoracledba.blogspot.com/

                Message was edited by:
                Amit_DBA
                • 20. Re: ORA-04030 Out of Process Memory
                  637640
                  Amit,

                  Your understanding is right.
                  I have set both /3GB and /PAE in my boot.ini file.

                  As of my understanding, /3GB means it's assigning 4GB memory for oracle.
                  Now if this true, then wanted to know how did you
                  arrive at 2.7 Gb of memory for SGA+PGA+AWE_WINDOW_SIZE(excluding buffer cache).

                  I might sound novice, but if you can let me know how should i configure my init.ora considering the fact that I have 8 GB total Physical RAM.
                  I went through the metalink, but could not understand the Windows architecture part related to paging and it's association with oracle memory.

                  Your help is appreciated.

                  /Sachin.
                  • 21. Re: ORA-04030 Out of Process Memory
                    637640
                    I also tried to set the SGA_TARGET and rest everything to 0, but still faced the out of memory error. Some search indicated that it could be due to PGA.
                    I am trying the options that you and others give, but still the error is repeating.
                    Sure, i might be giving some parameter high or low which is causing it. But im unable to trace it out.
                    • 22. Re: ORA-04030 Out of Process Memory
                      Gab2
                      Your settings:

                      large_pool = 1.5GB
                      shared_pool = 512mb
                      java_pool = 512mb

                      Total approx 2.5 Gb. Add to this 1 Gb for the awe window and you get 3.5 Gb (plus overhead) which is more than the 3 Gb max that the oracle process (on Windows) can allocate. Set the parameters to smaller values, say:

                      large_pool = 1GB
                      shared_pool = 512mb
                      java_pool = 256mb

                      and see if you can start your database.

                      The /3Gb means that Windows will allocate 3 Gb of ram to any given process. Usually, Windows allocates 2 gb to OS processes and 2 Gb to other processes. This way it's using only 1 Gb for OS processes. 3 Gb is the maximum, that's where the awe/pae switches come in handy allowing oracle to use anything above 4 Gb for buffers (only buffers). Of course, you should be running the Enterprise edition of Windows, otherwise you're limited to 4 Gb max even if you have more hardware available (Windows 32 bits won't even see it).

                      Or you can just go with Windows 64 bits and forget about the memory limits and the awe/pae tricks.

                      I hope this helps!

                      Gabriel

                      Message was edited by:
                      Gab

                      Message was edited by:
                      Gab
                      • 23. Re: ORA-04030 Out of Process Memory
                        user097815
                        just read this from metalink...i had the same problem untill yesterday..out problem was we had a operating system limit on it..so thats why we were getting the out of process memory error. so changed the O/S memory to unlimited....here is the metalink note. Thanks hope this may help


                        Subject:      Diagnosing and Resolving ORA-4030 errors                    
                             Doc ID:      Note:233869.1     Type:      TROUBLESHOOTING     
                             Last Revision Date:      13-APR-2008     Status:      PUBLISHED     
                        Diagnosing and Resolving ORA-4030 errors
                        What does an ORA-4030 mean?
                        This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

                        The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform its job.

                        What causes this error?
                        Since you run into this error, you can't allocate memory from the operating system. This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomadated for the systems virtual memory (physical memory + swap space). Many operating systems impose limits on the amout of memory a single process can acquire to protect itself.

                        This leads to the following questions:
                        Is there still sufficient memory available?
                        Is_there_an_operating_system_limit_set?
                        Is there an oracle limit set?
                        Which process is requesting too much memory?
                        How to collect information on what the process is actually doing?
                        These will be discussed in the subsequent sections.

                        Additional topics:
                        General suggestions on avoiding this error
                        References
                        Is there still sufficient memory available?
                        To answer this question, we will need to use operating system specific utilities to examine the memory usage.
                        OpenVMS systems : show memory will give you information about physical memory and pagefile usage:
                        Physical Memory Usage (pages): Total Free In Use Modified
                        Main Memory (256.00Mb) 32768 24849 7500 419

                        .....

                        Paging File Usage (blocks): Free Reservable Total
                        DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS 30720 30720 39936
                        DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS 226160 201088 249984
                        DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS 462224 405296 499968

                        As a general guideline, the sum of the free space in the pagefile(s) should not get lower than half the sum of the total space.
                        The swapfile(s) should almost be unused, free space should be about the same as the total space.

                        Windows systems: Check MEM Usage in the performance tab in Task Manager.
                        Unix systems: Every unix flavour usually has its own utilities to check for the global memory usage on the system like top, vmstat,... And memory management does work differently on each OS.
                        top usually displays physical memory and swapspace statistics.
                        swapon -s displays swapspace usage
                        vmstat displays free physical memory
                        Sample top output on Linux:
                        top - 10:17:09 up 1:27, 4 users, load average: 0.07, 0.12, 0.05
                        Tasks: 110 total, 4 running, 105 sleeping, 0 stopped, 1 zombie
                        Cpu(s): 0.3% user, 1.6% system, 0.0% nice, 98.0% idle
                        Mem: 1033012k total, 452520k used, 580492k free, 59440k buffers
                        Swap: 1052248k total, 0k used, 1052248k free, 169192k cached
                        .....


                        When sufficient memory is available, check for operating system enforced limits. When the memory has been depleted, we will have to find out where the memory has been used for.

                        Is there an operating system limit set?
                        When there still seems to be plenty of virtual memory left, we might not be allowed to use the amount of memory we are asking for. Check for limits imposed by the operating system.

                        OpenVMS systems: To check for the amount of physical memory you can use, check the working set quotas and the pagefile quota with the authorize utility. Refer to the reference for OpenVMS section on what quotas are used and how to modify them. Depending on what process and how it is started, the quotas used will not be those of the oracle account. Show process/id=<process id>/quota will show you how much quota is left for a process
                        UAF> show oracle7

                        Username: ORACLE7 Owner: Oracle7 DBA
                        Account: SUPPORT UIC: [200,2] ([SUPPORT,ORACLE7])
                        CLI: DCL Tables: DCLTABLES
                        Default: DISK$BOBBIE_USER1:[ORACLE7]
                        LGICMD: LOGIN
                        Flags:
                        Primary days: Mon Tue Wed Thu Fri
                        Secondary days: Sat Sun
                        No access restrictions
                        Expiration: (none) Pwdminimum: 6 Login Fails: 0
                        Pwdlifetime: (none) Pwdchange: 3-DEC-1997 15:38
                        Last Login: 27-MAY-2003 14:54 (interactive), 26-MAY-2003 16:15 (non-interactive)
                        Maxjobs: 0 Fillm: 1200 Bytlm: 180000
                        Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
                        Maxdetach: 0 BIOlm: 500 JTquota: 8192
                        Prclm: 20 DIOlm: 500 WSdef: 2500
                        Prio: 4 ASTlm: 4000 WSquo: 4096
                        Queprio: 0 TQElm: 4000 WSextent: 30000
                        CPU: (none) Enqlm: 18000 Pgflquo: 750000
                        Authorized Privileges: .....

                        $ sho proc/id=20200139/quota

                        24-JUN-2003 12:30:54.39 User: ORACLE7 Process ID: 20200139
                        Node: BOBBIE Process name: "ORA_BOB901_PMON"

                        Process Quotas:
                        Account name: SUPPORT
                        CPU limit: Infinite Direct I/O limit: 100
                        Buffered I/O byte count quota: 9994816 Buffered I/O limit: 100
                        Timer queue entry quota: 99 Open file quota: 29997
                        Paging file quota: 145968 Subprocess quota: 10
                        Default page fault cluster: 64 AST quota: 496
                        Enqueue quota: 49995 Shared file limit: 0
                        Max detached processes: 0 Max active jobs: 0



                        Windows systems: On Microsoft windows operating systems, oracle processes are implemented as threads in 1 process. The addressable amount of memory is 2Gb (including stack, PGA, SGA). This limit can be increased to 3Gb or higher. See <Note:46001.1> "Oracle Database and the Windows NT memory architecture, Technical Bulletin" for more information. The total memory used by the oracle process, excluding process stack and code, can be determined with this query.
                        Unix systems: use the limit / ulimit shell builtin commands. Be aware that unlimited might not really mean unlimited but actually an historical limit like 2Gb. Setting a real amount is preferred:
                        Sample Linux output:
                        aroelant@aroelant-be:~> ulimit -a
                        core file size (blocks, -c) 0
                        data seg size (kbytes, -d) unlimited
                        file size (blocks, -f) unlimited
                        max locked memory (kbytes, -l) unlimited
                        max memory size (kbytes, -m) unlimited
                        open files (-n) 1024
                        pipe size (512 bytes, -p) 8
                        stack size (kbytes, -s) unlimited
                        cpu time (seconds, -t) unlimited
                        max user processes (-u) 7168
                        virtual memory (kbytes, -v) unlimited

                        It is possible the limit is just set too low and will need to be increased. It might be we just want to use too much.

                        NOTE: There can be issues with other OS parameter settings such as maxuproc

                        For example, Bug 5443448 ORA-4030 (QERHJ HASH-JOI,KLLCQAS:KLLSLTBA)
                        Status: 92,Closed, Not a Bug

                        *** from the bug - "Increased MAXUPROC from 1000 to 2000, restarted the listener and ORA-4030 errors were resolved"

                        Is there an oracle limit set?
                        As from Oracle Version 9i, there is a parameter implemented which limits the total amount of PGA that can be allocated for an instance. <Note:223730.1> "Automatic PGA Memory Managment in 9i" provides more information on this issue. The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:
                        SQL> select
                        sum(value)/1024/1024 Mb
                        from
                        v$sesstat s, v$statname n
                        where
                        n.STATISTIC# = s.STATISTIC# and
                        name = 'session pga memory';

                        Which process is requesting too much memory?

                        Some operations will require lots of process memory like huge PL/SQL tables or big sort operations. In these cases, the processes will run for a certain period of time before getting the ora-4030 error, hopefully allowing us to find out where and why the memory is allocated. You can use the following query to find out oracle's idea of PGA and UGA size for the oracle processes.
                        SQL> col name format a30
                        SQL> select
                        sid,name,value
                        from
                        v$statname n,v$sesstat s
                        where
                        n.STATISTIC# = s.STATISTIC# and
                        name like 'session%memory%'
                        order by 3 asc;
                        This query will show the most hungry process last in the list.

                        It's usually a good idea to confirm the process memory usage from the Operating System point of view. After all, it might no be an oracle server process that is using too much memory.Usually, for server processes, oracle and the operating system more or less agree on memory usage. The following command will allow you to find out the memory usage for processes from the operating system.

                        OpenVMS systems: Show system will give you an overview of processes and resource usage. Processes wich are heavily pagefaulting usually use lots of virtual memory. The pages column indicates the use of physical pages. The show process/continious command gives physical (working set) and virtual memory usage.

                        $ show system/page

                        OpenVMS V7.2-1 on node BOBBIE 13-JUN-2003 09:56:30.44 Uptime 17 18:58:18
                        Pid Process Name State Pri I/O CPU Page flts Pages
                        20200101 SWAPPER HIB 16 0 0 00:00:02.45 0 0
                        20200106 CLUSTER_SERVER HIB 13 104 0 00:00:00.03 87 104
                        20200107 CONFIGURE HIB 10 21 0 00:00:00.06 77 17

                        $ sho process/id=xxx/cont:

                        Process AROELANT 10:00:53

                        State CUR Working set 131

                        Cur/base priority 6/4 Virtual pages 11714

                        Current PC 800D9B28 CPU time 0 00:00:01.28

                        Current PSL 00000003 Direct I/O 178

                        Current user SP 7A5227F0 Buffered I/O 962

                        PID 20200469 Page faults 1312

                        UIC [SUPPORT,AROELANT] Event flags C0000003
                        C0000000



                        Windows systems: On Microsoft windows systems, oracle is implemented by using threads in a single oracle process. Up to now, i didn't find a way to view the memory usage per thread. We can however check if oracle and the operating system agree on the memory used by oracle. For the Operating system point of view, we can use task manager. Use the view pusbutton and select Select Columns... make sure Virtual Memory Size is selected. The size mentioned in the VM Size column for oracle.exe should match the sum of SGA, total PGA memory and the process stack and code size. The following query can be used to get the memory size as viewed by oracle, however, not including the process stack and code size:
                        select sum(bytes)/1024/1024 Mb from
                        (select bytes from v$sgastat
                        union
                        select value bytes from
                        v$sesstat s,
                        v$statname n
                        where
                        n.STATISTIC# = s.STATISTIC# and
                        n.name = 'session pga memory'
                        );

                        MB
                        ----------
                        517.296406

                        On my system, this is about 30 Mb lower than the VM size seen with task manager.
                        When you have determined that oracle is the process using the memory, this query will show you which session uses the most.

                        Unix systems: The top utility is a usefull tool here, as you can customize display and sort key. The ps command will be available on most systems, but the possibilities will vary. For example, on Linux, 'ps -AF --sort resident' will list all processes with those having the biggest resident set size last. Also refer to <Note:174555.1> "UNIX: Determining the Size of an Oracle Process".


                        How to collect information on what the process is actually doing
                        This section will only discuss Oracle server processes. You should have determined with the methods discusses in the previous sections, that one or more oracle server processes is responsible for the memory consumption. Remember it is not always the process getting the ORA-4030 that is responsible for the memory consumption. It happens to be the process not getting the memory it requests.

                        For processes steadily increasing their memory, we can have a look while it runs.
                        You can check in v$sql_area what is beeing executed with the following query:
                        SQL> select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = <SID>;
                        We can force a heapdump and have it examined by oracle support services:
                        SQL> oradebug setorapid 10 (this is for the oracle pid, use setospid for the os process id)
                        SQL> oradebug unlimit
                        SQL> oradebug dump heapdump 5
                        When the problem is intermittend or some process fails too fast to examine and it is most likely the cause of the memory consumption, we can use events to get a heapdump when the process hits the error:
                        SQL> alter session set events '4030 trace name heapdump level 25'; or set this event in the databases init.ora.


                        This dump can be used by Oracle Support analysts to find the cause of the excessive memory allocation.


                        href="http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=21234.1" class="moz-txt-link-rfc2396E"> <Note:21234.1> EVENT: 10261 <Note:21234.1> EVENT: 10261 "Limit the size of the PGA heap"
                        cause the process to dump information when the PGA grows above the specified limit

                        General suggestions on avoiding this error
                        As mentioned before, some operations just require a lot of memory. For sort issues, decreasing SORT_AREA_SIZE can help. The Oracle server process will allocate SORT_AREA_SIZE bytes in the PGA for sort operations. When more memory is required to complete the search, the server process will use a temporary segment. This means that lowering SORT_AREA_SIZE can have a performance impact on queries requiring huge sort operations.
                        With 9i and higher, the automatic SQL execution memory management feature is enabled by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET in the initialization file. Using automatic PGA memory management will help reduce the possibility of ORA-4030 errors. Please note that PGA_AGGREGATE_TARGET is NOT supported on OpenVMS in Oracle 9i, but it is in Oracle 10g. Refer to the following notes for more details:

                        <Note:262946.1> "Performance Issues After Increasing Workload",
                        <Note:223730.1> "Automatic PGA Memory Managment in 9i",
                        <Note:223299.1> "Top Oracle 9i init.ora Parameters Affecting Performance"

                        PL/SQL procedures can also allocate lots of memory, so it might be required to rewrite some parts of your application. While a PL/SQL table is easy to use, it does require memory to be allocated in the PGA.
                        Review the optimizer strategy, some access paths might need more memory due to sort operations, the use of functions on more rows,...
                        On some operating systems, like Microsoft windows, the size of the SGA might be decreased to allow bigger PGA's.
                        Make sure your operating system and oracle limits are set reasonably.
                        Make sure there is enough memory available (physical memory and swapspace)
                        • 24. Re: ORA-04030 Out of Process Memory
                          277993
                          just read this from metalink...
                          Better post only the Note ID. Not all the content from Metalink as you have done. It is supposed to be CSI protected.
                          • 25. Re: ORA-04030 Out of Process Memory
                            Madrid
                            Whatch out when posting Metalink contents, it is copyrighted material and unless you are explicitly authorized to do so you cannot copy/paste metalink information available only by means of a valid CSI. You may get yourself and your company in troubles with Oracle

                            Regards
                            • 26. Re: ORA-04030 Out of Process Memory
                              429475
                              .pga_aggregate_target=1048M
                              *.sga_max_size=1221225472
                              *.sga_target=1221225472
                              Your sga_max_size should be >= pga_aggregate_target + sga_target

                              Try to set sga_max_size as 2.5GB.

                              sp009
                              • 27. Re: ORA-04030 Out of Process Memory
                                635471
                                Your sga_max_size should be >= pga_aggregate_target + sga_target
                                I don't think so.
                                1 2 Previous Next