Oracle takes a very long time to insert rows

Question

I have installed Oracle 23c Free in a Docker image. One of the new features is that you can insert multiple values in a single statement, just like all the other DBMSs.

I tested this by adding about 13,692 rows. In other DBMS, such as PostgreSQL, MySQL and MSSQL (with workaround), it really only takes a few seconds. With Oracle, it took over five minutes.

In previous versions, I would take these rows, break it up into about ten groups, and insert from multiple massive UNION ALL statements. It would take about half a minute.

I know it’s the free version, but should it really take a hundred times as long? Is there something I should do to make things work more smoothly?

Here is a brief version of the SQL:

CREATE TABLE saleitems (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    saleid INT REFERENCES sales(id) ON DELETE CASCADE NOT NULL,
    bookid INT REFERENCES books(id) NOT NULL,
    quantity INT,
    price NUMERIC(6,2)
);

INSERT INTO saleitems(id, saleid, bookid, quantity, price)
VALUES
    (15030, 6036, 1896, 2, 25.50), 
    (13753, 5531, 1026, 1, 10.50), 
    (8344, 3359, 1855, 1, 14.00),
    …
;

Answer 1

Oracle 23c takes a very long time to insert more than 1,000 hard-coded rows in a single SQL statement. If you break your inserts into chunks of 1,000 rows at a time, the overall time will shrink significantly.

Oracle can easily handle large SQL statements, but the parser runs into problems with enormous SQL statements. This problem applies to INSERT ALL, UNION ALL, and the new 23 table value constructor. In older versions of Oracle, problems happened around a few hundred concatenated SQL statements or rows, and in 23c the problem only begins after about 1000 rows.

The parse time grows exponentially, as the below chart demonstrates. This means your are much better off splitting your statement into 14 chunks and running them separately, as opposed to running them all at once. Usually, batching is the key to good database performance, but you've batched a row too far.

We can tell the extra time is spent parsing, as opposed to executing or fetching, by tracing the code. This detail is important, because it explains why testing these problems is so difficult. Since the explain plans may be cached, running the test with the exact same values twice likely wouldn't show any problem. But in practice, I assume your insert statements will always have at least one byte that is different, which would force hard parsing the statement and recreating this problem.

---

Code used for testing:

-- Create a table without any foreign key references, to simply the schema.
CREATE TABLE saleitems (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    saleid INT NOT NULL,
    bookid INT NOT NULL,
    quantity INT,
    price NUMERIC(6,2)
);

-- Run INSERT statements of varying number of rows upt to 10K, in increments of 50.
-- Time and display how long it takes to execute each INSERT statement.
declare
    v_before_time number;
    v_after_time number;
    v_sql clob := 'INSERT INTO saleitems(id, saleid, bookid, quantity, price) VALUES ';
begin
    for i in 50 .. 10000 by 50 loop
        for j in 1 .. 50 loop
            if i = 50 and j = 1 then
                v_sql := v_sql || '('||to_char(i*50+j)||','||to_char(i*50+j)||','||to_char(i*50+j)||','||to_char(i*50+j)||',9.99)';
            else
                v_sql := v_sql || ',('||to_char(i*50+j)||','||to_char(i*50+j)||','||to_char(i*50+j)||','||to_char(i*50+j)||',9.99)';
            end if;
        end loop;

        if i = 1 then
            dbms_output.put_line(v_sql);
        end if;

        v_before_time := dbms_utility.get_time;
        execute immediate v_sql;
        v_after_time := dbms_utility.get_time;
        rollback;

        dbms_output.put_line(i || ',' || to_char(v_after_time - v_before_time));
    end loop;
end;
/