12.2.0.1 DataPump Customers: Long Wait Time at the End of DataPump Export Job

刘泽宇

现象：
Long wait time is seen at the end of export and the next line to print is that the master table is exported:

....
30-OCT-17 15:53:08.355: W-1 Completed 15 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 2 seconds <-------here
31-OCT-17 03:08:02.765: W-1 Master table "<SCHEMA_NAME>"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
31-OCT-17 03:08:02.860:
******************************************************************************
31-OCT-17 03:08:02.862: Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
31-OCT-17 03:08:02.864:
<PATH>/<DUMP_FILE>.dmp
31-OCT-17 03:08:03.093: Job "<SCHEMA_NAME>"."SYS_EXPORT_FULL_02" successfully completed at Tue Oct 31 03:08:03 2017 elapsed 0 19:03:52


DataPump export job is slow at the end of job execution when running a merge and/or an update statement on the master table:

MERGE INTO "<SCHEMA_NAME>"."SYS_EXPORT_FULL_01" u USING
"<SCHEMA_NAME>"."SYS_EXPORT_FULL_01" s ON (s.process_order > 0 AND s.duplicate = 0
AND s.object_schema = u.base_object_schema AND NVL(s.name, s.object_name) = u.base_object_name AND s.object_type = u.base_object_type AND
s.partition_name IS NULL AND u.process_order > 0 AND u.duplicate = 0 AND u.base_object_type IS NOT NULL) WHEN MATCHED THEN UPDATE SET
u.base_process_order = s.process_order
UPDATE "<SCHEMA_NAME>"."SYS_EXPORT_FULL_01" mtu SET mtu.base_process_order = (-1000 - (SELECT
MIN(mts.process_order) FROM "<SCHEMA_NAME>"."<TABLE_NAME>" mts WHERE mts.process_order > 0 AND mts.duplicate = 0 AND mts.object_schema =
mtu.base_object_schema AND mts.object_name = mtu.base_object_name AND mts.object_type = mtu.object_type)) WHERE mtu.process_order BETWEEN :1 AND
:2 AND mtu.duplicate = 0 AND mtu.object_type = 'TABLE_DATA' AND mtu.processing_status = :3

The end of a DataPump export job needs to update values in the master table that DataPump creates and maintains. This is needed before that master table is exported and DP job completes.
In some situations, those updates perform pretty badly due to a wrong execution plan. Same performance problem is seen even with content=metadata_only.

The transactions which come with the fixes of:

unpublished Bug 24707852 - APPSST12201::PERFORMANCE ISSUE WITH EXPDP DURING FULL DATABASE EXPORT
Bug 26368590 - EXPDP IS SLOW AFTER UPGRADE TO 12.2
Bug 27144324 - LONG WAIT TIME AT THE END OF 12.2.0.1 DATAPUMP EXPORT JOB
address those performance issues. In some situations, the result looks like a hang or could be a hang.


变通方法：
无.

补丁：
A fix which merges the transactions of unpublished Bug 24707852, Bug 27144324 and Bug 26368590 is highly recommended to be installed in any 12.2.0.1 environment where an expdp job is started.
The following generic merge patch is currently available:

Patch 27589845 -  Merge Patch on top of 12.2.0.1.0 for: Bug 24707852, Bug 26117287, Bug 26368590 and Bug 27144324
Note: Run datapatch after the patch installation.