0

Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?

Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.

The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.

So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?

We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.

Test 1: Increase Row Count, Maintain Data/Row Test 1: Increase Row Count, Maintain Data/Row

Test 2: Maintain Row Count, Increase Data Per Row Test 2: Maintain Row Count, Increase Data Per Row

Improve this question
4
  • More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
    – Balazs Papp
    Commented May 4, 2018 at 16:49
  • You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
    – EdStevens
    Commented May 4, 2018 at 18:02
  • How did you "increase row size" in your 2nd test? How did you run your tests? CREATE TABLE statement for the target table might shine some light.
    – Michael Kutz
    Commented May 4, 2018 at 23:45
  • Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
    – neuralgroove
    Commented May 7, 2018 at 12:17

2 Answers 2

Reset to default
0

More likely limited by the fetch size, which is a client side setting.

For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/

Quoting some content of the blog post:

Batch size controls how many rows SSIS retrieves with each “batch”. After retrieving all the rows in a batch, SSIS signals to Oracle that it is ready for another batch. Of course, network latency is introduced during this signaling process. Thus, decreasing the number of total batches also decreases the number of round-trips SSIS has to make. The default batch size is only 100; increasing this to a reasonable number should boost performance.

And:

https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/

If you are experiencing the same or similar issue, then you may wish to experiment with different settings for the FetchSize parameter on the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User ID=User;FetchSize=100000;. The default setting is FetchSize=100. This means that the Oracle OLE DB provider will request and fetch 100 rows of data. SQL Server will then read 100 rows of data. Once all 100 rows have been read, the Oracle provider will request another 100 rows of data. This will continue untill all rows are read.

Improve this answer
0

Use Attunity Connector for Oracle This will definitely boost performance. https://learn.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017 If feasible, hit query on single partiton at a time which will give immense performance enhancement.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.