使用带有NETWORK_LINK的IMPDP传输LOB时的性能问题 (Doc ID 1488229.1)

Performance Problems When Transferring LOBs Using IMPDP With NETWORK_LINK (Doc ID 1488229.1)

SYMPTOMS

A severe performance impact is experienced when using IMPDP with the NETWORK_LINK command line option to transfer a table which has 2 CLOB columns (900000 rows, average row length ~5 kB).  当将IMPDP与NETWORK_LINK命令行选项一起使用时，传输具有2个CLOB列 (900000 rows, average row length ~5 kB)的表时，会严重影响性能。

CAUSE

The cause of this problem has been identified in:
Bug 4438573 - DATAPUMP RUNS VERY SLOW OVER NETWORK FOR TABLES WITH CLOBS
closed with status "Not a Bug".

This is expected behavior when dealing with LOBs and the use of the NETWORK_LINK functionality.  在处理LOB和使用NETWORK_LINK功能时，这是预期的行为。
The bug states:  

"IMPDP with NETWORK_LINK ultimately uses SQL of the form:
INSERT INTO <local_tab_name> SELECT ... FROM <remote_tab_name>@<network_link>;

Underneath this, the number of network round trips varies significantly for CLOB versus VARCHAR2 by necessity.
在此之下，CLOB与VARCHAR2的网络往返次数有很大不同。
For a table with VARCHAR2 columns the remote fetches can pull back several rows in one go in a single packet.
对于具有VARCHAR2列的表，远程提取可以在单个数据包中一次提取数几行。
For a table with CLOB columns the remote fetches pull back several rows in one go but the CLOB columns return a LOB LOCATOR. A LOB locator is like a handle to the LOB itself. Each of these LOBs has to be requested and read individually resulting in a lot more network round trips and these add significantly to the time taken.  对于具有CLOB列的表，远程访存一次完成后退几行，但CLOB列返回LOB LOCATOR。LOB定位器就像LOB本身的句柄。这些LOB中的每一个都必须被请求并单独读取，从而导致更多的网络往返，并且这大大增加了所花费的时间。

Example:

In some situation we get 8 rows back in each fetch, so for VARCHAR2 we send a fetch request and get back a large packet with 8 rows of data for all columns.  在某些情况下，每次获取都返回8行，因此对于VARCHAR2，我们发送获取请求并获取一个大数据包，其中包含所有列的8行数据。
In the CLOB case we send a fetch request and get back a packet with 8 rows of data which includes 3 LOB locators per row. We then have to send a LOB READ request for each of these LOBs to the remote site, and get back that LOB data. 8*3 = 24 extra round trips to get that data."  在CLOB的情况下，我们发送一个获取请求，并获取一个包含8行数据的数据包，其中每行包含3个LOB定位符。然后，我们必须将每个LOB的LOB READ请求发送到远程站点，并取回该LOB数据。8 * 3 = 24次额外往返来获取该数据。

SOLUTION

As this is the way LOB access is implemented, the only workaround available is to avoid network access to remote LOBs by using a dump file instead of the NETWORK_LINK functionality.

由于这是实现LOB访问的方式，因此唯一可用的解决方法是通过使用dump文件而不是NETWORK_LINK功能来避免对远程LOB的网络访问。

REFERENCES

BUG:4438573 - DATAPUMP RUNS VERY SLOW OVER NETWORK FOR TABLES WITH CLOBS