Bulk Insert into Oracle

Hi Experts,
We need to insert records at the speed of min 10,000 records per sec in the following condition.
Source : MS SQL Server.
Dest : Oracle 11g, Running in Solaris 10.
Record size 1.5KB.

What is the best way to achieve?

Who is Participating?
mrjoltcolaConnect With a Mentor Commented:
I would do a simple test.

Write a program to select data in bulk from SQL Server, using the best performance options you can, large cursors, fetch sizes, etc. See what sort of speed you can get just dumping to a file. This might give you an idea of what sort of speed you could theoretically approach if you were writing across a network to an Oracle program doing inserts using APPEND hint.

Then take that number and multiply times the number of partitions you might be able to partition the Oracle destination table (you'll need to know your possible partitioning keys).

Note, I am assuming you will use partitions. With 600,000 rows per minute, I'm sure you don't intend NOT to use partitions. :)

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify:
? are both on the same network?
? what is the context of this data migration?
? what is the key "event" to start a record to be copied?

pcssecureAuthor Commented:
Hi, Thanks for the quick response. Thats very fast.

1. Different segment.
2. Question not clear to me. There is a continuous flow of data into the MS SQL server and we need to copy over to Oracle.
3. The data pumped into MS SQL non-stop at the rate of 10,000 records / sec and our job is to copy these records min at that rate.

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>1. Different segment.
but they can "see" each other, meaning that you can ping/telnet/ftp/whatever from one to the other?

>? what is the context of this data migration?
so, you get +-10K records/sec created in sql server, and want to see them in oracle copied at the same speed? (aka real-time), is that correct?

pcssecureAuthor Commented:
yes the SQL Server database can be connected from Source segment.
10K records/sec created in the SQL Server can be bulk loaded to Oracle as long as the time to load can catch up with the insertion (to the SQL Server) rate.
I think you can use linked server option of MS SQL server
I doubt there is any simple solution to copying 10k records per second in between databases without some sort of bulk load / direct path technology. Depending on your system, you might not get 10k rows/sec from INSIDE the Oracle database, just by doing "insert into ... select * from ..."

Standard SQL, ODBC and/or ADO loads which use standard data / SQL layer will be more along the lines of 1-2k per second, in my experience, but of course this is subjective, since the record structure, the disk speed and the CPU will affect performance. My point is, I doubt taking any standard approach will work. Maybe you can approach this number with parallel inserts, but then you have to partition the Oracle table to use the direct path (append) option.

I'm certain the Oracle-SQL Server gateways, linked servers, etc. will not approach this speed, as they do not use direct path by default, if at all.

Is there a bulk dump tool available in SQL Server? If so, dumping it to a file format, then loading with SQL Loader might be an option.

600,000 records per minute is heavy. Is it non-stop 24x7? Do the records have primary keys or require any Oracle sequences? This rate is doable by SQL Loader, or perhaps direct path inserts, but not by standard, single threaded SQL inserts, using standard platter disks.
pcssecureAuthor Commented:
Thank all of you for the inputs.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.