Improving OPENQUERY performance from Oracle to SQL Server

Question

I'm currently working on a database migration, from Oracle 11g to SQL Server 2019. We're talking here of about 10-12 GB, in something like 300 tables (so creating them manually is out of reach, as is creating 300 data flows in SSIS).

The idea was to do it in an "ELT" way: in a first step, to "replicate" the content of the Oracle source database onto a staging database (having a Simple recovery model, and located in the SQL Server instance), and in a second step, to operate all necessary transformations in order to fill the destination tables (in the destination database on the same SQL Server instance). This question only deals with the first step of the process.

The "one-to-one extract and load" first phase goes quite well. First of all, I declared the Oracle server as a linked server (the provider is "OraOLEDB"). Then I created an SP that generates "on the fly" all the required tables on the SQL Server staging database (querying Oracle metadata and translating it to corresponding SQL Server DDL, including primary keys if any), running them without any error.

Finally, the same SP also generates all required queries to fetch the data and runs them. All the required data is transferred as required, but performance is completely impractical (the migration would have to be completed from start to finish in a single night).

The queries generated all have this simple shape (as a matter of fact, most queries don't even have the WHERE clause):

INSERT <DestinationTable> WITH (TABLOCKX, HOLDLOCK)
    (<ColumnList>)
FROM Openquery ([<OracleLinkedServer>]
             , 'SELECT <ColumnList>
               FROM <OracleTable>
               WHERE <SimpleCriteria>')
ORDER BY <PrimaryKeyIfAny>;

I expected such a volume to be transferred in, say, 1 or 2 hours, but I'm far from such a performance. At first, this took more than 16 hours.

After some documentation reading, I increased the FetchSize parameter of the linked server to 10,000, which reduced the whole duration to almost 9 hours, but it is still far too long (I also tried to increase the same parameter to 200,000, without any further performance improvement).

Most of the waits I could see during the process were either "OLEDB" or "PREEMPTIVE_COM_GETDATA", which seem to indicate that the destination is waiting for the source. Take note that in the test environment, both servers are in the same datacenter, so it probably is not a network issue (in the final, real migration, the source server will be overseas, though). As far as I can tell, I didn't see any parallelism happening in the real execution plans I could intercept.

If you need any other technical detail in order to help me, please feel free to ask. (And please forgive any English error, as it is not my native language).

My process has extensive logging, I could tell you about any of the hundreds of tables transferred, but of course it would depend a lot on their volume (whether measured in total row length, or in row count). And by the way, the logging is not the cause of the bad performance. When I ask the SP to fetch just the first row of all required tables (I have a parameter for that), the whole thing takes less than a minute.

I went to my log table and took a fairly typical table for example. It has 100K rows, weighs about 25 MB, and the transfer takes about 45 seconds. If I use SQL Developer on the destination machine in order to extract a CSV of the same table from the source server, it takes only about 9 seconds.

I tried to run manually in SSMS the INSERTs generated by my SP, and I find the same (slow) speed. I also tried to reformulate them using the four-parts linked server syntax, with no difference. But when I try to export the destination tables to a CSV in a folder, using the "Export" feature of SQL Developer, the speed I can get is about 4-5 times higher.

The plan for one of my elementary queries, as returned by sp_WhoIsActive, can be found here: https://www.brentozar.com/pastetheplan/?id=HJWj7Oqps

The wait_info column is NULL most of the time (and indicates 1-2 ms minor waits from time to time). But other columns look more worrying to me: First of all, context_switches amount to more than 740,000,000. Second, memory_info indicates that I have 7.3 GB memory available, but that only 10 KB have been granted to my query (and 3 KB are really used), but I'm not sure it's relevant or may be improved.

Answer 1

Linked servers are not optimised for bulk data transport. You may have achieved the best network throughput possible via this method already. It is typically not impressive.

The ORDER BY clause in your generated queries does not function as you might intend. The ORDER BY only affects the order in which any identity values are assigned. The SQL Server optimiser will separately decide whether it is worth sorting the data stream in clustered index order.

For best performance, you will want to achieve minimally logged bulk inserts without sorting.

This is achievable with a clustered table destination if the source dataset is sorted in clustered key order and you can successfully communicate that fact to the optimiser.

There are several ways to implement that using source files rather than directly from the Oracle database. You will need extra time and space to export the files, but this is usually easily compensated for by the increased insert speed at the SQL Server destination.

One option is to use the BULK INSERT table_name FROM 'data_file' statement with the ORDER option matching the primary key, and the TABLOCK option for minimal logging. You may also specify the FORMAT = 'CSV' for the input.

For details and other options, see:

• Bulk Import and Export of Data (SQL Server) in the product documentation
• The Data Loading Performance Guide by Thomas Kejser, Peter Carlin and Stuart Ozer
• Minimal Logging with INSERT…SELECT into Empty Clustered Tables by me