5 Replies Latest reply: Dec 4, 2012 6:48 PM by Seberg RSS

    Logical : paging out memory to disk.

    753017
      Hi,
      We have to reorg the table in production and so I tried to test in test database but everytime I issue move command , logical apply get stopped with error " paging out memory to disk". I tried to increare SGA and allocated more memory for LCR but think this is not a proper solution.

      Can you guys suggest me any better way ? or do you think shall I use skip handler??

      Thanks so much!
        • 1. Re: Logical : paging out memory to disk.
          618702
          Dear user4486815,

          May i see the error code any the accompanying error(s)? Also if you can post the alert.log that'll be great!

          Regards.

          Ogan
          • 2. Re: Logical : paging out memory to disk.
            753017
            Hi , Sorry for late reply . below is the alert log message. paging out to memory error I checked in V$logstdby_stats.
            I know increasing SGA and allocating SGA to LCR is not possible solution because I have tried that by allocating 3GB to LCR with table size 30GB.

            So when I will be re-organising table of size 100+GB , then it will cause issue.


            Tue Sep 28 11:52:47 2010
            Errors in file /u01/app/oracle/admin/TP5/bdump/tp5_p004_25289.trc:
            ORA-00603: ORACLE server session terminated by fatal error
            ORA-04030: out of process memory when trying to allocate 10504 bytes (pga heap,kgh stack)
            ORA-04030: out of process memory when trying to allocate 10504 bytes (pga heap,kgh stack)
            ORA-00604: error occurred at recursive SQL level 2
            ORA-04030: out of process memory when trying to allocate 16408 bytes (QERHJ hash-joi,QERHJ Bit vector)
            ORA-04030: out of process memory when trying to allocate 2576 bytes (knas:sh,knas: rpc arguments)
            Tue Sep 28 11:53:33 2010
            Errors in file /u01/app/oracle/admin/TP5/bdump/tp5_lsp0_25274.trc:
            ORA-12805: parallel query server died unexpectedly
            LOGSTDBY Apply process P005 pid=25 OS id=25291 stopped
            LOGSTDBY Apply process P008 pid=33 OS id=25297 stopped
            LOGSTDBY Apply process P006 pid=31 OS id=25293 stopped
            LOGSTDBY Apply process P007 pid=32 OS id=25295 stopped
            Tue Sep 28 11:53:33 2010
            TLCR process death detected. Shutting down TLCR
            logminer process death detected, exiting logical standby
            LOGSTDBY Analyzer process P003 pid=23 OS id=25287 stopped
            Tue Sep 28 11:53:33 2010

            Thanks,
            • 3. Re: Logical : paging out memory to disk.
              MaxymK
              Since process is terminated by fatal error:
              ORA-00603: ORACLE server session terminated by fatal error
              ORA-04030: out of process memory when trying to allocate 10504 bytes (pga heap,kgh stack)
              it is likely a result of something on OS rather than ORACLE level. Can you check UNIX process ulimits ? (I'm assuming you are running on UNIX).
              • 4. Re: Logical : paging out memory to disk.
                KRMR
                Hello,

                Just re opening this thread since I am getting the same error in one my databases. DB version is 11.2.0.3 and my OS is RHEL 5, X86-64. My ulimit is set to "unlimited" for oracle user. I am seeing the following error and session is being terminated.
                ORA-04030: out of process memory when trying to allocate 16408 bytes (QERHJ hash-joi,QERHJ Bit vector)

                More info from incident files. Any hints will be appreciated. Thanks.

                ========= Dump for incident 360697 (ORA 4030) ========
                ----- Beginning of Customized Incident Dump(s) -----
                =======================================
                TOP 10 MEMORY USES FOR THIS PROCESS
                ---------------------------------------

                *** 2012-12-04 14:29:30.744
                52% 2719 MB, 1991346 chunks: "permanent memory "
                qmxdGetChildNo ds=0x2ab9c31d9620 dsprt=0x2ab9c302dcd0
                37% 1950 MB, 663782 chunks: "free memory "
                qmxdGetChildNo ds=0x2ab9c31d9620 dsprt=0x2ab9c302dcd0
                2% 111 MB, 2897152 chunks: "qmxdplsArrayGetNI1 "
                qmxdpls_subhea ds=0x2ab8725ba6d0 dsprt=0x2ab86dbc27c0
                2% 111 MB, 2897152 chunks: "qmxdplsArrayNI0 "
                qmxdpls_subhea ds=0x2ab8725ba6d0 dsprt=0x2ab86dbc27c0
                2% 101 MB, 29363 chunks: "permanent memory "
                qmxlu subheap ds=0x2ab9c3031d10 dsprt=0x2ab8725ba6d0
                2% 101 MB, 663782 chunks: "qmxdGetChildNodes-subheap "
                qmxdpls_nodeli ds=0x2ab9c302dcd0 dsprt=0x2ab8725ba6d0
                1% 59 MB, 3787 chunks: "pl/sql vc2 " PL/SQL
                koh-kghu call ds=0x2ab86e1d23b0 dsprt=0xbb07ca0
                1% 52 MB, 17734 chunks: "permanent memory "
                ds=0x2ab9c3034780 dsprt=0x2ab8725ba6d0
                1% 34 MB, 466 chunks: "free memory "
                pga heap ds=0xbb07ca0 dsprt=(nil)
                0% 23 MB, 1462 chunks: "pmucalm coll " PL/SQL
                koh-kghu call ds=0x2ab86e1a11a0 dsprt=0xbb07ca0



                =======================================
                PRIVATE MEMORY SUMMARY FOR THIS PROCESS
                ---------------------------------------
                ******************************************************
                PRIVATE HEAP SUMMARY DUMP
                5454 MB total:
                5420 MB commented, 635 KB permanent
                34 MB free (31 MB in empty extents),
                5335 MB, 1 heap: "session heap " 60 KB free held



                ==========================================
                INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY
                ------------------------------------------
                Dumping Work Area Table (level=1)
                =====================================
                Global SGA Info
                ---------------
                global target: 4096 MB
                auto target: 256 MB
                max pga: 819 MB
                pga limit: 1638 MB
                pga limit known: 0
                pga limit errors: 0
                pga inuse: 6581 MB
                pga alloc: 7038 MB
                pga freeable: 276 MB
                pga freed: 2000919 MB
                pga to free: 0 %
                broker request: 0
                pga auto: 20 MB
                pga manual: 0 MB
                pga alloc (max): 10338 MB
                pga auto (max): 1039 MB
                pga manual (max): 0 MB
                # workareas : 0
                # workareas(max): 80

                ================================
                PER-PROCESS PRIVATE MEMORY USAGE
                --------------------------------
                Private memory usage per Oracle process
                -------------------------
                Top 10 processes:
                -------------------------
                (percentage is of 7038 MB total allocated memory)
                78% pid 81: 5420 MB used of 5457 MB allocated <= CURRENT PROC
                7% pid 176: 300 MB used of 486 MB allocated (185 MB freeable)
                1% pid 42: 48 MB used of 55 MB allocated (5952 KB freeable)
                1% pid 36: 41 MB used of 44 MB allocated
                1% pid 38: 41 MB used of 44 MB allocated (1088 KB freeable)
                1% pid 41: 41 MB used of 44 MB allocated (1088 KB freeable)
                1% pid 20: 10 MB used of 42 MB allocated (30 MB freeable)
                0% pid 44: 5570 KB used of 33 MB allocated (1600 KB freeable)
                0% pid 10: 28 MB used of 31 MB allocated (2304 KB freeable)
                0% pid 73: 24 MB used of 26 MB allocated (1280 KB freeable)
                • 5. Re: Logical : paging out memory to disk.
                  Seberg
                  Generally you cannot reopen a thread that this old. This thread is somebody else's question, not yours.

                  What you should do is open a new question.



                  Diagnosing and Resolving ORA-4030 errors [ID 233869.1]