Results 1 to 4 of 4

Thread: Performance of Microsoft Attunity Connector for Oracle

  1. #1
    frederico_fonseca is offline Junior Member
    Join Date
    Apr 2012
    Rep Power

    Performance of Microsoft Attunity Connector for Oracle

    Posting here to see if you guys have any idea of what may be wrong and even to see if you can give me any pointers of other things to look for.

    I am at the moment developing and testing some packages to load data from SQL Server onto Oracle and have bumped into a smallish problem.
    All packages are basically the same.
    Sql command - truncate oracle table
    DataFlow - extract from sqlserver ->derived column ->oracle attunity destination

    Several of our extracts are loading ovr 2 million records each, and we have a few tables with 30 to 50 million records each.
    Rows per second was exacly the same regardless of the size of the table when I was testing.

    To my surprise loading the data using array mode turned out to be significantly faster than using the fast load option.
    As I wasn't happy with timing I created a sample package to read from a file instead to see if it made any difference.
    Timing from this sample package were the same as those extracting data from Sql Server

    Sample package
    flat file, comma delimited with 1152160 records, record size 610
    20 date transformations, 92 columns, mixed datatypes

    Server config
    Microsoft Attunity driver 1.1 and driver 1.2 same behaviour.
    Oracle client 11g 64 bit
    SqlServer 2008R2
    Windows server 2008
    64 GB Ram
    24 Cpus
    1Gbit Network

    Oracle database
    11G (Windows) and 10g (Unix)
    Windows machine (32 bit)
    4 Cpus
    4 GB ram
    Unix Machine (64 bit)
    8 Cpus
    24 GB Ram
    During tests Sql server machine CPU was around 10% all the time, with 46 GB memory being used
    Package running going up to 400MB ram used depending on buffers size defined (see below)
    Network usage - 2.2% average for fast load, 4-5% for array load

    Loading into the unix server or the windows machine made no difference. Timings were the same in either

    Trials done by changing number of records and buffer size on dataflow, and increasing attunity driver values (buffer and batch size/transaction size)
    Values for array mode (Fastload = false)
    tuning - duration in seconds
    default SSIS - 208.886
    20k - 105.519
    40k - 108.483

    values for direct path mode (Fastload = true)
    default SSIS -157.826
    20k + 128k buffer - 154.878
    20k + 256k buffer - 150.915
    20k + 512k buffer - 151.259
    20k + 1024k buffer - 152.741
    20k + 2048k buffer - 158.138

    Loading the same file using SQL*Loader, 20k rows batch, datecache=20000, and streamsize of 16m took 100 seconds

  2. #2
    Join Date
    Sep 2006
    Rep Power

    The results are indeed a little surprising but if I followed your message correctly, you are able to pull an end-to-end copy from a flat file (or SQL Server) to Oracle in about 100 seconds when going via SSIS and the Attunity Connector vs. 100 seconds for standalone loading of a file to Oracle via SQL*Loader. So you more or less get the same performance via SSIS as you get via the highly optimize SQL*Loader - is there a problem now or are you just wondering why the fast load option (Oracle Direct Path) is not showing better numbers?

    Other observations:
    - The network utilization difference may be a hint.
    - What was the network utilization with the SQL*Loader? What options were used?
    By Dror Harari

    To Find Out more About Attunity Technology:
    Contact Us

  3. #3
    frederico_fonseca is offline Junior Member
    Join Date
    Apr 2012
    Rep Power

    Edit - Small correction sqlloader timing were 80 seconds, not 100.

    Yes I am mainly wondering why the fast load option is performing way worst than the array mode, and looking to see if there is something I should be doing different on the settings or other configuration. Not that there are many settings that can be changed on the SSIS connector.
    Based on my prior experience direct path was always faster for big loads.

    Problem with array load is that is has bigger impact on the Oracle side, specially taking in consideration that we will be running a few of these jobs concurrently. (we have a total of 100 tables to load daily)

    Network utilization -
    All tests were done at different times of the day, including hours where there is little trafic going on, including at a weekend. (no backups running!!).

    The timings/network usage in all cases were similar and constant throughout all runs (and they were a lot of them).
    fast load - 2.2 %
    Array load - 4-5%
    Sql*Loader - 5-6%

    sql loader options
    OPTIONS(DATE_CACHE=20000,ERRORS=99999999,rows=2000 0,readsize=7420000,streamsize=22440000,COLUMNARRAY ROWS=20000,SILENT=(HEADER),DIRECT=TRUE)

    Tried a few loader options but wasnt too worried about optimizing this one as with minimal tuning it was behaving at acceptable levels
    Last edited by frederico_fonseca; 04-20-2012 at 06:40 AM.

  4. #4
    frederico_fonseca is offline Junior Member
    Join Date
    Apr 2012
    Rep Power
    any ideas?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts