My Oracle Support Banner

Impdp Slow With High Waits On "Direct Path Write Temp" and "Direct Path Read Temp" (Doc ID 1646351.1)

Last updated on JUNE 07, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Datapump import (impdp) performs slowly.
The slowdown is observed while REF_CONSTRAINTS were being imported as shown by the last information in the export log:

...
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39082: Object type VIEW:"WKSYS"."WK$INST_LIST" created with compilation warnings
ORA-39082: Object type VIEW:"WKSYS"."WK$SYS_ADMIN_LIST" created with compilation warnings
ORA-39082: Object type VIEW:"WKSYS"."OUS_INSTANCES" created with compilation warnings
ORA-39082: Object type VIEW:"WKSYS"."OUS_INSTANCES" created with compilation warnings
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT


The AWR and trace file shows high wait on 'Direct Path Write Temp' and 'Direct Path Read Temp'.

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU   114,802   52.83  
direct path read temp 4,477,755 18,306 4 8.42 User I/O
library cache lock 36 17,863 496199 8.22 Concurrency
direct path write temp 4,079,189 14,155 3 6.51 User I/O
db file scattered read 288,902 9,341 32 4.30 User I/O

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.