4 Replies Latest reply on Apr 21, 2017 12:12 PM by user12050753

    ORA-02055/ORA-04030: out of process memory when trying to process large volume of data via HS

    user12050753

      From our 11g Oracle development and test database

      we're trying to copy data from another oracle database (source) to a MySQL database (target),

      using HS ODBC links. The query (below) works fine for smaller amounts of data,

      but once we hit >2m(2 million) rows it falls over with following errors

      "ORA-02055: distributed update operation failed; rollback required" and "ORA-04030: out of process memory" errors.

      Error as below:-

      ORA-02055: distributed update operation failed; rollback required

      ORA-04030: out of process memory when trying to allocate 1016 bytes (session

      heap,kgsc:kxsc)

      ORA-06512: at line 210

      ORA-04030: out of process memory when trying to allocate 448 bytes (session

      heap,hssr horss3/1)

       

       

      Any and all suggestions much appreciated!

       

       

      oRacle version:-11.2.0.3.0

      MySQL vesrsion:-

      Driver      = /home/oracle/lms/myodbc-5.3.6/lib/libmyodbc5a.so

      DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample

      PORT        = 3306

       

       

       

       

      Please find the below memeory parameters;

       

       

      Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

      Connected as pbatch

       

      SQL> show parameter memory;

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      hi_shared_memory_address             integer     0

      memory_max_target                    big integer 55G

      memory_target                        big integer 55G

      shared_memory_address                integer     0

       

      SQL> show parameter pga;

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      pga_aggregate_target                 big integer 0

       

      SQL> show parameter sga;

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      lock_sga                             boolean     FALSE

      pre_page_sga                         boolean     FALSE

      sga_max_size                         big integer 55G

      sga_target                           big integer 0

       

      SQL> show parameter sort;

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      nls_sort                             string      BINARY

      sort_area_retained_size              integer     0

      sort_area_size                       integer     65536

       

      SQL> show parameter hash;

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      hash_area_size                       integer     131072

       

       

      The code used is pasted below:-

      -------------------------------------------------------------------------------------------------------------

      DECLARE

         PROCEDURE PROC_LOAD_DAILY_TX_DATA_LMS(p_run_id IN NUMBER)

         --  AUTHID CURRENT_USER

         IS

            v_systimestamp       VARCHAR (16) := TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');

            logfilehandle        UTL_FILE.file_type;

            log_file_name        VARCHAR2 (100) := NULL;

            v_count              NUMBER := 0;

            v_csc_lookup_key     NUMBER := 0;

            v_err_message        VARCHAR2 (300) := NULL;

            error_exception      EXCEPTION;

            v_run_id             NUMBER (3) := 0;

            v_t_type_exclude     VARCHAR2 (100) := NULL;

            v_run_date           DATE := NULL;

            v_job_offset_value   NUMBER := 0;

            v_count_id           number:=0;

           

            CURSOR cur_daily_txn_rec

            IS

               SELECT Post_txn_balance AFTER_CARD_BAL,

                      toll_revenue_type APP_SECTOR,

                      Extern_file_id BATCH_NUMBER,

                      Card_issuer_id CARD_ISSUER_ID,

                      Card_no CARD_NUMBER,

                      'ACTIVE' CARD_STATUS,

                      Tx_seq_number CARD_TXN_NUMBER,

                      'C' DATA_PUSH_SOURCE,

                      'D' DEBIT_CREDIT_INDICATOR,

                      '503' ENTRY_LOC_ID,

                      'MALYASY' ENTRY_LOC_NAME,

                      Recv_extn_loc_id EXIT_LOC_ID,

                      'MALYASY' EXIT_LOC_NAME,

                      '06-jan-2016' EXPIRY_DATE,

                      'ABC123' ID_NUMBER,

                      'DFG123' ID_TYPE,

                      Device_no MFG_NUMBER,

                      '06-jan-2016' OPERATIONAL_DATE,

                      '06-jan-2016' POSTED_DATE,

                      '04' PRINT_SP,

                      '01-jan-2016' LMS_PROCESS_DATE,

                      'P' PROCESS_STATUS,

                      Purse_ind PURSE_FLAG,

                      Recv_extn_sp_id SP_ID,

                      Lane_tx_type T_TYPE,

                      Lane_id TERMINAL_ID,

                      Lane_type TERMINAL_TYPE,

                      Full_fare_amount TXN_AMOUNT,

                      '06-jan-2016 12:12:12' TXN_DATE_TIME,

                      'T' TXN_SOURCE,

                      csc_lookup_key TXN_UNIQUE_ID,

                      'P' UR_FLAG,

                      '12345' VECTOR_ACCT_NUMBER,

                      csc_lookup_key

                 FROM t_account_toll

                 where rownum<=3000000;

                    

       

       

            TYPE tab IS TABLE OF cur_daily_txn_rec%ROWTYPE

               INDEX BY BINARY_INTEGER;

       

       

            v_daily_txn_coll     tab;

         BEGIN

            v_run_id := p_run_id;

       

       

       

       

            log_file_name :=

               'PROC_LOAD_DAILY_TX_DATA_LMS_' || v_systimestamp || '.LOG';

            logfilehandle := UTL_FILE.FOPEN ('VECTOR_LOG', log_file_name, 'W');

            UTL_FILE.PUTF (logfilehandle,

                           '\n %s%s',

                           'Vector Load Daily Transaction Process Started @',

                           v_systimestamp);

            v_t_type_exclude := NULL;

            UTL_FILE.FFLUSH (logfilehandle);

       

       

       

       

            OPEN cur_daily_txn_rec ;

       

       

            LOOP

           

               FETCH cur_daily_txn_rec

               BULK COLLECT INTO v_daily_txn_coll

               LIMIT 1000;

       

       

               v_csc_lookup_key := 0;

       

       

               IF v_daily_txn_coll.COUNT > 0

               THEN

                  FOR i IN 1 .. v_daily_txn_coll.COUNT

                  LOOP

                v_count_id:=v_count_id+1;

                     INSERT

                       INTO "vector_staging_txn"@lms(

                                                  "after_card_bal",

                                                          "app_sector",

                                                          "batch_number",

                                                          "card_issuer_id",

                                                          "card_number",

                                                          "card_status",

                                                          "card_txn_number",

                                                          "data_push_source",

                                                          "debit_credit_indicator",

                                                          "entry_loc_id",

                                                          "entry_loc_name",

                                                          "exit_loc_id",

                                                          "exit_loc_name",

                                                          "expiry_date",

                                                          "id_number",

                                                          "id_type",

                                                          "mfg_number",

                                                          "operational_date",

                                                          "posted_date",

                                                          "print_sp",

                                                          "lms_process_date",

                                                          "process_status",

                                                          "purse_flag",

                                                          "sp_id",

                                                          "t_type",

                                                          "terminal_id",

                                                          "terminal_type",

                                                          "txn_amount",

                                                          "txn_date_time",

                                                          "txn_source",

                                                          "txn_unique_id",

                                                          "ur_flag",

                                                          "vector_acct_number",

                                "lms_error_type")

                     VALUES (

                       v_daily_txn_coll (i).AFTER_CARD_BAL,

                             v_daily_txn_coll (i).APP_SECTOR,

                             v_daily_txn_coll (i).BATCH_NUMBER,

                             v_daily_txn_coll (i).CARD_ISSUER_ID,

                             v_daily_txn_coll (i).CARD_NUMBER,

                             v_daily_txn_coll (i).CARD_STATUS,

                             v_daily_txn_coll (i).CARD_TXN_NUMBER,

                             v_daily_txn_coll (i).DATA_PUSH_SOURCE,

                             v_daily_txn_coll (i).DEBIT_CREDIT_INDICATOR,

                             v_daily_txn_coll (i).ENTRY_LOC_ID,

                             v_daily_txn_coll (i).ENTRY_LOC_NAME,

                             v_daily_txn_coll (i).EXIT_LOC_ID,

                             v_daily_txn_coll (i).EXIT_LOC_NAME,

                             '01-mar-2017',      --v_daily_txn_coll (i).EXPIRY_DATE,

                             v_daily_txn_coll (i).ID_NUMBER,

                             v_daily_txn_coll (i).ID_TYPE,

                             v_daily_txn_coll (i).MFG_NUMBER,

                             NULL,          --v_daily_txn_coll (i).OPERATIONAL_DATE,

                             '01-mar-2016',      --v_daily_txn_coll (i).POSTED_DATE,

                             v_daily_txn_coll (i).PRINT_SP,

                             '01-mar-2016', --   v_daily_txn_coll (i).LMS_PROCESS_DATE,

                             v_daily_txn_coll (i).PROCESS_STATUS,

                             v_daily_txn_coll (i).PURSE_FLAG,

                             v_daily_txn_coll (i).SP_ID,

                             v_daily_txn_coll (i).T_TYPE,

                             v_daily_txn_coll (i).TERMINAL_ID,

                             v_daily_txn_coll (i).TERMINAL_TYPE,

                             v_daily_txn_coll (i).TXN_AMOUNT,

                             '01-mar-2016',    --v_daily_txn_coll (i).TXN_DATE_TIME,

                             v_daily_txn_coll (i).TXN_SOURCE,

                             v_daily_txn_coll (i).TXN_UNIQUE_ID,

                             v_daily_txn_coll (i).UR_FLAG,

                             v_daily_txn_coll (i).VECTOR_ACCT_NUMBER,

                   1);

       

       

                     v_count := v_count + SQL%ROWCOUNT;

                     v_csc_lookup_key := v_daily_txn_coll (i).csc_lookup_key;

       

       

                     IF MOD (v_count, 1000) = 0

                     THEN

                        COMMIT;

         v_daily_txn_coll.delete;

                     END IF;

       

       

                      if MOD (v_count, 10000)=0 then

                     UTL_FILE.PUTF (logfilehandle,

                                    '\n No Records processed [%s] @ [%s]',

                                    v_count,

                                    TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'));

       

       

                     UTL_FILE.FFLUSH (logfilehandle);

               end if;

                  END LOOP;

               ELSE

                  EXIT;

               END IF;

            END LOOP;

       

       

            CLOSE cur_daily_txn_rec;

       

       

            COMMIT;

            UTL_FILE.PUTF (logfilehandle,

                           '\n Total No Records Processed [%s] @ [%s]',

                           v_count,

                           TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'));

            --      UTL_FILE.FFLUSH (logfilehandle);

            UTL_FILE.PUTF (

               logfilehandle,

               '\n \n ---------------------END OF DAILY TRANSACTION LOAD FOR LMS---------------------');

            UTL_FILE.FCLOSE (logfilehandle);

         EXCEPTION

            WHEN error_exception

            THEN

               ROLLBACK;

               UTL_FILE.PUTF (logfilehandle,

                              '%s ==> %s',

                              SQLCODE,

                              SQLERRM);

               UTL_FILE.FCLOSE (logfilehandle);

               RAISE_APPLICATION_ERROR (-20001, v_err_message || SQLERRM);

            WHEN OTHERS

            THEN

               ROLLBACK;

               UTL_FILE.PUTF (logfilehandle,

                              '%s ==> %s',

                              SQLCODE,

                              SQLERRM);

               UTL_FILE.FCLOSE (logfilehandle);

               DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLCODE || '==>' || SQLERRM);

               RAISE_APPLICATION_ERROR (-20002, v_err_message || SQLERRM);

         END PROC_LOAD_DAILY_TX_DATA_LMS;

      BEGIN

         PROC_LOAD_DAILY_TX_DATA_LMS(133);

      END;

      /

        • 1. Re: ORA-02055/ORA-04030: out of process memory when trying to process large volume of data via HS
          Gaz in Oz

          There isn't enough process memory (OS limited not database) to hold all the rows... To fix this you need to change your code to be more memory efficient. It looks like you have tried by using bulk collect but I think you've implemented that wrong.

          As a quick and dirty "fix" you could try reducing "LIMIT 1000" to "LIMIT 100"... but I think it may be to do with you using bulk collect to select the rows, but then insert them row by row (slow by slow). I would have expected to see something like

          FORALL i  IN cur.FIRST..cur.LAST LOOP

             INSERT INTO table@dblink VALUES cur(i);

          END LOOP;

          and what is the code on line 210? I can not make out what line 210 is due to you not formatting your code in a consistent manner.

          • 2. Re: ORA-02055/ORA-04030: out of process memory when trying to process large volume of data via HS
            user12050753

            Hi Gaz,

            Thanks for your  reply.

              I already tried reducing the limit to 100,even then the error occurs after processing 2M rows.

              We cannot  use FORALL BULK INSERT to perform insert on Remote Database(MYSQL) tables.

              I already tried it and resulted in "PLS-00739 - FORALL INSERT/UPDATE/DELETE not supported on remote tables".

            I formatted code in pl/sql developer,but when pasted   in community page it appears like this.Any suggestion on the memory parameters at OS level?

            please let me know the line string in 210.

            in My editor line no-210 ,211,212 is as follows.

            WHEN OTHERS

                  THEN

                     ROLLBACK;

                     UTL_FILE.PUTF (logfilehandle,

                                    '%s ==> %s',

                                    SQLCODE,

                                    SQLERRM);

             

            Regards,

            Arun.C

            • 3. Re: ORA-02055/ORA-04030: out of process memory when trying to process large volume of data via HS
              Gaz in Oz

              Does this really need to be done as a pl/sql block? Couldn't you

              INSERT INTO "vector_staging_txn"@lms

              SELECT ...

              FROM...

              ...where the select is the sql the same as the cursor you defined?

              This would likely be quicker anyway.

               

              Maybe another way would be to batch your driving cursor some how, using WHERE predicates to chunk that up into more manageable chunks of less than the number of rows it currently fails on.

              • 4. Re: ORA-02055/ORA-04030: out of process memory when trying to process large volume of data via HS
                user12050753

                Yes GAZ  pl/sql block is included.More logic is involved.

                The requirement is to push around 3-4 million rows to mysql DB from Oracle DB.

                This code i am doing for testing purpose to check the performance and impact .

                After adding all where conditions the data size will be 2-3 million.