0

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.

2
  • As a side note, you may want to look into using Replication to do the migration. It's not officially supported for Oracle 11G (last version the docs say is supported is 10G) but it may still work. It would eliminate the need for a Linked Server, the bottlenecks that come with one, and you wouldn't need any code to set it up.
    – J.D.
    Feb 10, 2023 at 14:42
  • Your problem may purely be constrained to a cardinality estimate issue which is unfortunately a limitation of Linked Servers. Linked Servers are hard-coded to estimate 10,000 rows but in your example execution plan it looks like you're trying to pull ~150x that amount of rows in a single query. This may lead to under-requesting of the resources correctly needed to process the query (such as Memory). Perhaps you should try an actual migration tool or Replication?
    – J.D.
    Feb 20, 2023 at 14:36

1 Answer 1

0

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:

2
  • Thank you very much for answering. If I understand correctly, and if I follow your solution, my next problems would be 1- to get the Oracle source server to generate programatically the 300 CSV's in a network drive, and 2- to get SQL Server to fetch these remote files to be used as sources for as many BULK INSERTs ? (As this is supposed to be a nightly, automated process, I can't really transfer these resulting files by hand).
    – BenSQL
    Mar 14, 2023 at 15:50
  • @BenSQL Something along those lines, yes. By the way, your question mentioned a migration, which is usually a one-off, not a nightly procedure. But that's going beyond the initial scope anyway. My answer is essentially that linked servers are not optimized for bulk transfer.
    – Paul White
    Mar 14, 2023 at 16:04

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.