Bulk Insert into Oracle

Posted on 2009-04-20
Last Modified: 2012-05-06
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?

Question by:pcssecure
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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?


    Author Comment

    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.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >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?


    Author Comment

    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.
    LVL 7

    Expert Comment

    I think you can use linked server option of MS SQL server
    LVL 40

    Expert Comment

    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.
    LVL 40

    Accepted Solution

    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. :)


    Author Closing Comment

    Thank all of you for the inputs.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle SQL Nested Select 3 50
    Stay Alert! 13 47
    adding % symbol to a percentage - oracle query 16 25
    SQL query of Oracle 10g database. 8 31
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now