slow Oracle OLEDB data load

[jdr_7665]

We are seeing slow times when pulling a large amount of rows from an Oracle table.  I have a table with 104 million rows that I can export all rows to a flat file with sqlplus in about 20 minutes.  There are no CLOBS.  We are naming all columns we want so we are not doing a select *.  When we first ran the job, it used a default fetch size of 100 rows per fetch and it ran for over 12 hours.  We have tried running with different fetch sizes and we were able to get the job down to about 2 hours with a fetch size of 125,000.  I have a couple of questions:

1) If I try to increase the fetch size larger then 125,000, I get the following error:  "Error: Did not recognise connector reply".  What could be causing an error when I try to increase the fetch size larger then 125,000?

2) I have noticed that the process slows down as it runs longer.  I am able to get the first 50 million rows very quickly.  I can get them in about 20 minutes but then the process starts slowing down.  By the end of the process, I am only seeing maybe 1 or 2 fetches per minute on the Oracle side.

Any suggestions on what else I could try to fix this?  We have upgrade the oledb driver up to the 19c version.  We are currently running Oracle 12.2 for the database.

[Brett_Bleess]

I would venture you are asking the question on the wrong forum, as I believe the issue is most likely on the Oracle side between the DB and the Connector, and Oracle forums would be the better place to find possible explanations etc.  Sorry I am not more help, but past experience has shown that most of the time these issues are in the connector or the DB side, especially with Oracle sources.  You are likely on the right track with settings, but there would appear to be something you are still missing that needs to be added in the Connector settings given you are doing OLE DB.  You could try ODBC too, and the other thing to rule us out would be to try the same connection via Excel or something to see how that performs, as I would expect the same issues if I am on the right track...

Regards,
Brett

[jaimeaguilar]

Hi,

Like it has being said, first you need to discard that it is not an Oracle issue. I've worked with Oracle databases and specifically in one case we had a trouble with performance when we extracted data through OLEDB Client. 

However, performance improved when we switched to Qlik ODBC connector package. Guessing from the category of this post I suppose you're using QlikView. If that is the case you need to download and install the package from Downloads page (it is located in the Qlik Connectors tab and is called Qlik ODBC Connector).

The advantage of using this connector is that you don't need to configure the odbc through windows DSN and improvement in terms of performance (more efficient and faster extractions). 

If you're using Qlik Sense, then connector is already included in the installation,

You may give it a try,

best regards

[spring101]

We have encountered a similar issue. I found a Qlik KB article that mentioned success with OLE DB version 11g. Surprisingly, this has worked to make our load much quicker when running the QlikView app. manually. We are still running into a slowness issue when running the app. from the QMC that we are still trying to troubleshoot. @jdr_7665 

[jdr_7665 · Accepted solution]

Thank you!  The Qlik ODBC connector was a vast improvement.

[spring101]

Update to our situation: We ended up rebooting the server with our distribution service and it seems that our jobs are running much more quickly. We have found that Oracle's 11g driver for the OLE DB connection is much better than the later versions.