Home » SQL & PL/SQL » SQL & PL/SQL » Insert 1 million records using SQLPLUS
Insert 1 million records using SQLPLUS [message #267637] Fri, 14 September 2007 01:12 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi All,

I have a .sql file that contains 1 million insert statements for a table. My table have 5 fields 4 of them are varchar2 and one is number that is primary key.
I am execution this file from sqlplus in window by below command

@filename.sql


inserting is going on but it is taking much time.

So my concern is that is there any way that i can insert these records in batch mean in lot of 1000 records so that elapsed time will be reduced.


Thanks

Bhadu Embarassed
Re: Insert 1 million records using SQLPLUS [message #267640 is a reply to message #267637] Fri, 14 September 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have 1000000 inserts then you have 1000000 inserts.
But maybe you have 1 insert statement that inserts 1000000 rows?
Anyway you can't reduce elapsed time batching. After all, you have to insert all rows.
It mostly depends on concurrent treatments.

Regards
Michel
Re: Insert 1 million records using SQLPLUS [message #267643 is a reply to message #267637] Fri, 14 September 2007 01:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I take it the insert-statements are formed equally, which means you could use the script as input for a sql-loader script.
Re: Insert 1 million records using SQLPLUS [message #267655 is a reply to message #267637] Fri, 14 September 2007 02:13 Go to previous messageGo to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Make sure you have no indexes or triggers on the table(s) you insert into. Recreate indexes/triggers afterwards.
Re: Insert 1 million records using SQLPLUS [message #267673 is a reply to message #267655] Fri, 14 September 2007 03:15 Go to previous messageGo to next message
abhilash8@gmail.com
Messages: 5
Registered: September 2006
Junior Member
It will take significant time,
try reducing UNDO_RETENTION for some better performance

regards
abhilash
Re: Insert 1 million records using SQLPLUS [message #267674 is a reply to message #267673] Fri, 14 September 2007 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is this related to INSERT performances?

Regards
Michel
Re: Insert 1 million records using SQLPLUS [message #267686 is a reply to message #267674] Fri, 14 September 2007 04:44 Go to previous messageGo to next message
abhilash8@gmail.com
Messages: 5
Registered: September 2006
Junior Member
UNDO_RETENTION specifies for how many seconds undo information is kept. The default is 900 seconds and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. By example this parameter specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.This could be the reason for your slow performance with multiple inserts.
Revert it back to normal after your insert operation.

regards
abhilash
Re: Insert 1 million records using SQLPLUS [message #267690 is a reply to message #267686] Fri, 14 September 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This could be the reason for your slow performance with multiple inserts.

Why?

Regards
Michel
Re: Insert 1 million records using SQLPLUS [message #267698 is a reply to message #267690] Fri, 14 September 2007 05:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If Oracle has to remember all those undo-actions, it has less room for doing the inserts. Too busy trying to remember the previous actions.
Re: Insert 1 million records using SQLPLUS [message #267718 is a reply to message #267698] Fri, 14 September 2007 06:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Now that is interesting.

I was wondering how an INSERT ALL construct would compare to the single inserts.

1000 Insert statements like :
INSERT INTO testtab VALUES (1, 'This is just a somwhat longish varchar with no real meaning');


Time for the 1000 single inserts :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     3003      0.01       0.17          0          1          0           0
Execute   3004      0.02       0.37         35       1011       3226        3002
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6007      0.03       0.54         35       1012       3226        3002



The INSERT ALL CONSTRUCT :

INSERT ALL 
    INTO testtab VALUES (1, 'This is just a somwhat longish varchar with no real meaning')
    .... 999 more lines ....
SELECT 1 FROM dual;


Time for the INSERT ALL construct :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4    220.34     237.59          0          0          0           0
Execute      5      0.14       0.32          3       1010       3227        1003
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9    220.48     237.91          3       1010       3227        1003


I expected the parse to take longer, but not that much longer.

Complete traces include for the curious.

  • Attachment: tkprof.txt
    (Size: 106.16KB, Downloaded 212 times)
Re: Insert 1 million records using SQLPLUS [message #267949 is a reply to message #267718] Sun, 16 September 2007 22:13 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SQL*Plus is a client-server application. Every INSERT must perform some repeated communication with the database server - this is expensive.

If you placed all of the INSERT statements in a BEGIN/END block, it would pass the whole lot in a single statement without all of that nasty to-and-fro.

This would have a further advantage in that PL/SQL cleverly avoids re-parsing of syntactically similar INSERT statements with constant VALUES.

Having said this, you will probably come up against some limitation on the size of the script. You might have to send them 1000 at a time.

Of course, Frank's original suggestion to use SQL*Loader over the INSERT script (ignoring the non-data components of the script) is still your best-bet by far.

Ross Leishman
Previous Topic: Exception Handling
Next Topic: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0
Goto Forum:
  


Current Time: Fri Dec 09 09:41:23 CST 2016

Total time taken to generate the page: 0.08561 seconds