Why does this happen when inserting thousands of rows into an Oracle table with Node.js?

Question

I am new in Oracle and I am looking for the best way for insert thousands (maybe millions) of records into a table.

I have seen other questions and answers about this situation, but in this answer the PL/SQL code use TWO associate arrays of scalar types (PSL_INTEGER) and works as table columns, I need the same but with ONE nested table of record/complex type for insert in the table as a row.

First of all, I have this code in Node.js (TypeScript) using the oracledb package (v 5.1.0):

let data: Array<DataModel>;

// data's variable is populated with data and 'DataModel' is an interface,
// data is an array with a the exact table's structure: 
// [ 
//     { C_ONE: 'mike',   C_TWO: 'hugman', C_THREE: '34', ... with other 12 columns }, 
//     { C_ONE: 'robert', C_TWO: 'zuck',   C_THREE: '34', ... with other 12 columns }, 
//     { C_ONE: 'john',   C_TWO: 'gates',  C_THREE: '34', ... with other 12 columns } 
// ]

let context;

try {
    
    context = await oracledb.getConnection({
        user: 'admin',
        password: 'admin',
        connectString: 'blabla'
    });

    const result = await context.execute(
        // My SP
        'BEGIN PACKAGE_TEST.SP_TEST_STRESS(:p_data, :p_status); END;',
        {
            // My JSON Array
            p_data: {
                type: 'PACKAGE_TEST.T_STRESS',
                val: data
            },
            // Variable for check if all success or fails... this doesn't matters :)
            p_status: {
                type: oracledb.NUMBER,
                val: 1,
                dir: oracledb.BIND_OUT
            }
        },
        { autoCommit: true }
    );

    console.log(result);

    if ((result.outBinds as { p_status: number }).p_status === 0) {
        // Correct
    }
    else {
        // Failed
    }

} catch (error) {
    
    // bla bla for errors

} finally {

    if (context) {
        
        try {

            await context.close();
            
        } catch (error) {

            // bla bla for errors

        }

    }

}

And the PL/SQL code for my sotore procedure:

CREATE OR REPLACE PACKAGE PACKAGE_TEST
IS
    
    TYPE R_STRESS IS RECORD
    (
        C_ONE VARCHAR(50),
        C_TWO VARCHAR(500),
        C_THREE VARCHAR(10),
        C_FOUR VARCHAR(100),
        C_FIVE VARCHAR(10),
        C_SIX VARCHAR(100),
        C_SEVEN VARCHAR(50),
        C_EIGHT VARCHAR(50),
        C_NINE VARCHAR(50),
        C_TEN VARCHAR(50),
        C_ELEVEN VARCHAR(50),
        C_TWELVE VARCHAR(50),
        C_THIRTEEN VARCHAR(300),
        C_FOURTEEN VARCHAR(100),
        C_FIVETEEN VARCHAR(300),
        C_SIXTEEN VARCHAR(50)
    );
    
    TYPE T_STRESS IS VARRAY(213627) OF R_STRESS;
    
    PROCEDURE SP_TEST_STRESS
    (
        P_DATA_FOR_PROCESS T_STRESS,
        P_STATUS OUT NUMBER
    );
    
END;

/

CREATE OR REPLACE PACKAGE BODY PACKAGE_TEST
IS

    PROCEDURE SP_TEST_STRESS
    (
        P_DATA_FOR_PROCESS T_STRESS,
        P_STATUS OUT NUMBER
    )
    IS
    BEGIN
    
        DBMS_OUTPUT.put_line('started');
        
        BEGIN
        
            FORALL i IN 1 .. P_DATA_FOR_PROCESS.COUNT
        
                INSERT INTO TEST_STRESS
                (
                    C_ONE,
                    C_TWO,
                    C_THREE,
                    C_FOUR,
                    C_FIVE,
                    C_SIX,
                    C_SEVEN,
                    C_EIGHT,
                    C_NINE,
                    C_TEN,
                    C_ELEVEN,
                    C_TWELVE,
                    C_THIRTEEN,
                    C_FOURTEEN,
                    C_FIVETEEN,
                    C_SIXTEEN
                )
                VALUES
                (
                    P_DATA_FOR_PROCESS(i).C_ONE,
                    P_DATA_FOR_PROCESS(i).C_TWO,
                    P_DATA_FOR_PROCESS(i).C_THREE,
                    P_DATA_FOR_PROCESS(i).C_FOUR,
                    P_DATA_FOR_PROCESS(i).C_FIVE,
                    P_DATA_FOR_PROCESS(i).C_SIX,
                    P_DATA_FOR_PROCESS(i).C_SEVEN,
                    P_DATA_FOR_PROCESS(i).C_EIGHT,
                    P_DATA_FOR_PROCESS(i).C_NINE,
                    P_DATA_FOR_PROCESS(i).C_TEN,
                    P_DATA_FOR_PROCESS(i).C_ELEVEN,
                    P_DATA_FOR_PROCESS(i).C_TWELVE,
                    P_DATA_FOR_PROCESS(i).C_THIRTEEN,
                    P_DATA_FOR_PROCESS(i).C_FOURTEEN,
                    P_DATA_FOR_PROCESS(i).C_FIVETEEN,
                    P_DATA_FOR_PROCESS(i).C_SIXTEEN
                );
        
        EXCEPTION
        WHEN OTHERS THEN
            p_status := 1;
        END;
        
        P_STATUS := 0;
        
    END;

END;

And my target table:

CREATE TABLE TEST_STRESS
(
    C_ONE VARCHAR(50),
    C_TWO VARCHAR(500),
    C_THREE VARCHAR(10),
    C_FOUR VARCHAR(100),
    C_FIVE VARCHAR(10),
    C_SIX VARCHAR(100),
    C_SEVEN VARCHAR(50),
    C_EIGHT VARCHAR(50),
    C_NINE VARCHAR(50),
    C_TEN VARCHAR(50),
    C_ELEVEN VARCHAR(50),
    C_TWELVE VARCHAR(50),
    C_THIRTEEN VARCHAR(300),
    C_FOURTEEN VARCHAR(100),
    C_FIVETEEN VARCHAR(300),
    C_SIXTEEN VARCHAR(50)
);

An intersting behavior happens with this scenario:

If I send my JSON Array with 200 rows, this works perfectly, I don't know the exact time it takes to complete successfully, but I can tell it's milliseconds.
If I send my JSON Array with 200,000 rows, this takes three or four minutes to wait, the promise is resolved and it throws me an exception of type: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

This happens when passing the JSON Array to the procedure parameter, it seems that when processing it it will cost too much.

Why does this happen in the second scenario?
Is there a limitation on the number of rows in the NESTED TABLE TYPES or is any configuration (default) with Node.js?
Oracle suggests increasing pga_aggregate_limit but seeing it in my SQLDeveloper with "show parameter pga;" It is 3G, does it mean that the information I am sending is exceeding 3 GB? Is normal?
Is there a more viable solution that does not affect the performance of the database?

Appreciate your help.

The [other answer you referred to](https://stackoverflow.com/a/45572158/5763993) is a little out of date on the PL/SQL side - you haven't needed multiple scalar arrays for `forall` since around Oracle 9i, and I'm not sure why they chose an associative array when a nested table would seem to make things simpler. (`varray` is generally a bit useless in PL/SQL code - it's really for compatibility with multi-value table columns.) — William Robertson, May 29 '21 at 12:50
The column lengths in your target table add up to 1820, so rounding down a bit and multiplying by 200K gives around 300MB for your PL/SQL array. However, the JSON array is probably rather larger, and the procedure will need to have them both in memory at once, plus whatever processing is going on. It looks like that all comes to more than you've got available. — William Robertson, May 29 '21 at 13:00
Thank you very much for your clarification, it seems that I should send my data in parts instead of sending a very heavy load, but I am not sure if my PL / SQL is correct or if the design I propose is efficient. — Julián, May 29 '21 at 14:22
@Julián Can you help me solve this issue. https://stackoverflow.com/questions/71569842/how-to-insert-a-table-type-in-oracle-db-using-node-js — Janaki Narayanan, Apr 09 '22 at 11:57

score 2 · Answer 1 · answered May 29 '21 at 00:26

2

Each server process gets its own PGA, so I'm guessing this is causing the total aggregate PGA, over all the processes currently running, to go over 3 GB.

I assume this is happening because of what's going on inside your package, but you only show the specification, so there's no way to tell what's happening there.

You're not using a nested table type. You're using a varray. A varray has a maximum length of 2,147,483,647.

It sounds like you're doing something inside your procedure to use too much memory. Maybe you need to process the 200,000 rows in chunks? With no more information about what you're trying to do, can you use some other process to load your data, like sqlldr?

answered May 29 '21 at 00:26

eaolson

14,717
7
43
58

Thank you very much for your clarification, however, in my question I show the content of the "SP_TEST_STRESS" procedure, it is simply a FORALL that traverses the array and inserts into the table. If I leave my procedure with its content empty or without any business logic, the problem in the second scenario is the same. This means that if I want to load 200,000 rows, should I do it in parts? – Julián May 29 '21 at 14:16
I have not used "sqlldr" since I would like to send the data directly to Oracle, instead of writing it to a file and then oracle use it, although I could do a proof of concept, what makes me most curious is that my JSON in a text file weighs 45MB and for Oracle it seems to be more than 3GB – Julián May 29 '21 at 14:16

Why does this happen when inserting thousands of rows into an Oracle table with Node.js?

1 Answers1