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 laterOracle 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
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 |