Faster Way to Insert, via Script, in Oracle

Faster way to Insert, via script, in Oracle?

Problem

Parsing time may increase exponentially with certain types of statements, especially INSERT ALL. For example:

--Clear any cached statements, so we can consistently reproduce the problem.
alter system flush shared_pool;
alter session set sql_trace = true;

--100 rows
INSERT ALL
    INTO FileIds(Id,FileTypeGroupId) VALUES(1, 1)
    ...
    repeat 100 times
    ...
select * from dual;

--500 rows
INSERT ALL
    INTO FileIds(Id,FileTypeGroupId) VALUES(1, 1)
    ...
    repeat 500 times
    ...
select * from dual;

alter session set sql_trace = false;

Run the trace file through tkprof, and you can see the Parse time increases dramatically for a large number of rows. For example:

100 rows:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          1          0           0
Execute      1      0.00       0.00          0        100        303         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.05          0        101        303         100

500 rows:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     14.72      14.55          0          0          0           0
Execute      1      0.01       0.02          0        502       1518         500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     14.74      14.58          0        502       1518         500

Solutions

1. Break your large statement into several smaller statements. It's difficult to find the optimal size. On some versions of Oracle there's a magic number of rows that will cause the problem. I usually go for about 100 rows - enough to get most of the benefits of grouping statements, but low enough to avoid the parsing bug. OR...
2. Try the insert into ... select ... from dual union all ... method instead. It usually runs much faster, although it's parsing performance may also degrade significantly with size.
3. Upgrade Oracle. Parsing performance has improved in newer versions. I can no longer reproduce this issue in version 12.2.

Warning

Don't learn the wrong lesson from this. If you're worried about SQL performance, 99% of the time you're better off grouping similar things together instead of splitting them apart. You're doing things the right way, you just ran into a weird bug. (I searched My Oracle Support but couldn't find an official bug for this.)

What is the fastest way to insert data into an Oracle table?

It's much better to insert a few hundred rows at a time, using PL/SQL tables and FORALL to bind into insert statement. For details on this see here.

Also be careful with how you construct the PL/SQL tables. If at all possible, prefer to instead do all your transforms directly in SQL using "INSERT INTO t1 SELECT ..." as doing row-by-row operations in PL/SQL will still be slower than SQL.

In either case, you can also use direct-path inserts by using INSERT /*+APPEND*/, which basically bypasses the DB cache and directly allocates and writes new blocks to data files. This can also reduce the amount of logging, depending on how you use it. This also has some implications, so please read the fine manual first.

Finally, if you are truncating and rebuilding the table it may be worthwhile to first drop (or mark unusable) and later rebuild indexes.

How to make my Oracle update/insert action through Java faster?

Get hold of a copy of Professional Oracle Programming.

It seems a bit on the old side at 2005, but Oracle doesn't change drastically when it comes to optimising performance. I've got this book myself and have used it's advice to speed up seemingly intractable performance issues for many applications. Get it. Read it. Do it.

So what can you do while you wait for express delivery?

• Get the DBA on your side, - you'll need their help and their tools
• Get hold of TOAD and pay for the extra query analysis tools if necessary
• Check your indexes for every query that you run - you need to examine the execution plans carefully (AUTOTRACE and EXPLAIN PLAN are your friends here)
• Consider the type of index you're using (could a functional index do the trick?)
• Consider using transportable tablespaces
• Use the built-in Optimizer to gather information
• Obtain statistics so you can measure performance gains (irrespective of the pre-caching and suchlike)
• Consider stored outlines
• Consider materialized views to allow splitting your data to that which is needed immediately and that which can suffer a delay
• Consider table truncation to reduce the size of the overall tables as older data is farmed off

That should be enough to give you a firm grasp on what is failing and how to fix it.

What is the best way to execute 100k insert statements?

Inserting 100,000 rows with SQL statements is fine. It's not a huge amount of data and there are a few simple tricks that can help you keep the run time down to a few seconds.

First, make sure that your tool is not displaying something for each statement. Copying and pasting the statements into a worksheet window would be horribly slow. But saving the statements into a SQL*Plus script, and running that script can be fast. Use the real SQL*Plus client if possible. That program is available on almost any system and is good at running small scripts.

If you have to use SQL Developer, save the 100K statements in a text file, and then run this as a script (F5). This method took 45 seconds on my PC.

set feedback off
@C:\temp\test1.sql

Second, batch the SQL statements to eliminate the overhead. You don't have to batch all of them, batching 100 statements-at-a-time is enough to reduce 99% of the overhead. For example, generate one thousand statements like this:

INSERT INTO SELLING_CODE (SELC_ID, VALC_ID, PROD_ID, SELC_CODE, SELC_MASK, VALC_ID_STATUS)
select 5000001, 63, 1, '91111111', 'N/A', 107 from dual union all
select 5000001, 63, 1, '91111111', 'N/A', 107 from dual union all
...
select 5000001, 63, 1, '91111111', 'N/A', 107 from dual;

Save that in a text file, run it the same way in SQL Developer (F5). This method took 4 seconds on my PC.

set feedback off
@C:\temp\test1.sql

If you can't significantly change the format of the INSERT statements, you can simply add a BEGIN and END; / between every 100 lines. That will pass 100 statements at a time to the server, and significantly reduce the network overhead.

For even faster speeds, run the script in regular SQL*Plus. On my PC it only takes 2 seconds to load the 100,000 rows.

For medium-sized data like this it's helpful to keep the convenience of SQL statements. And with a few tricks you can get the performance almost the same as a binary format.

Best and Fastest way to insert milions records in oracle

Row-by-row really is slow. Do it at once, if you can; I can't, I don't have enough memory so I'm doing it in loop, but 1 million rows at a time. Here's how:

SQL> create table registration (coupen number);

Table created.

SQL> set timing on
SQL> begin
  2    for i in 1 .. 5 loop
  3      insert into registration (coupen)
  4      select round(dbms_random.value(10000000,99999999),0) coupen
  5      from dual
  6      connect by level <= 1000000;
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.96
SQL> set timing off
SQL> select count(*) From registration;

  COUNT(*)
----------
   5000000

SQL>

Took ~18 seconds on my laptop and 11g XE database.

---

If it has to be 5 million distinct values, then code has to be somewhat changed. I'd suggest you to

• insert some more rows (more than 5 million) (don't try to avoid duplicates at insert stage, it'll take too much time for that many rows)
• delete duplicates
• delete superfluous rows (so that 5 million rows remain)

Here's one option (I included counts and timings to show what's going on with number of rows and to see how much time it takes).

create table registration (coupen number);
set serveroutput on
set timing on

declare
  l_cnt    number;
  l_cntdis number;
begin
  -- initial insert
  dbms_output.put_line('Stage 1: ' || to_char(sysdate, 'hh24:mi:ss'));
  for i in 1 .. 5 loop
    insert into registration (coupen)
    select round(dbms_random.value(10000000,99999999),0) coupen
    from dual
    connect by level <= 1050000;    --> more than 1 million rows per loop iteration (because of duplicates)
  end loop;
  commit;

  select count(*), count(distinct coupen) 
  into l_cnt, l_cntdis
  from registration;
  dbms_output.put_line('Stage 2: ' || to_char(sysdate, 'hh24:mi:ss') || ' total = ' || l_cnt ||', distinct = ' || l_cntdis);

  execute immediate 'create index i1reg_coup on registration (coupen)';
  dbms_output.put_line('Stage 3: ' || to_char(sysdate, 'hh24:mi:ss'));

  -- delete remaining duplicates
  delete from registration a
  where a.rowid > (select min(b.rowid)
                   from registration b
                   where b.coupen = a.coupen
                  );
  select count(*), count(distinct coupen) 
  into l_cnt, l_cntdis
  from registration;
  dbms_output.put_line('Stage 4: ' || to_char(sysdate, 'hh24:mi:ss') || ' total = ' || l_cnt ||', distinct = ' || l_cntdis);

  -- delete superfluous rows (i.e. leave exactly 5.000.000 distinct rows)
  delete from registration r
  where r.coupen in 
  (select x.coupen from 
    (select a.coupen, 
            row_number() over (order by null) rn
     from registration a
    ) x 
   where x.rn > 5000000
  );                 
  select count(*), count(distinct coupen) 
  into l_cnt, l_cntdis
  from registration;
  dbms_output.put_line('Stage 5: ' || to_char(sysdate, 'hh24:mi:ss') || ' total = ' || l_cnt ||', distinct = ' || l_cntdis);
end;
/

Result:

Stage 1: 11:06:49
Stage 2: 11:07:09 total = 5250000, distinct = 5100332
Stage 3: 11:07:18
Stage 4: 11:11:17 total = 5100332, distinct = 5100332
Stage 5: 11:12:02 total = 5000000, distinct = 5000000

PL/SQL procedure successfully completed.

Elapsed: 00:05:13.57
SQL>

Slightly over 5 minutes on the same laptop and 11g XE. See if this is acceptable in your case.

Quick way to run large sql file containing Insert stms - oracle

AFAIK the option 2 is correct.

• Use sed/awk/perl to convert the file into CSV (or fixed width) input file.
• disable constraints, indexes, (possibly drop unique indexes)
• create control file for your input file
• exec sqlldr (turn direct path load on)

And this should finish withing few seconds.

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

---

Related Topics