Expdp of a schema stuck at last stage with high waits in "cell multiblock physical read"
(Doc ID 3020767.1)
Last updated on MAY 13, 2024
Applies to:
Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and laterInformation in this document applies to any platform.
Customer is trying to export an schema, initially he used parallel 4 threads to export and it stuck for more than 16hrs at last stage,
as it was stuck cancelled and cleared job and initiated the expdp of schema again with thread one, it also stuck at the final stage for more than 10hrs.
The analysis of output of script in
SRDC - Diagnostic Collection for DataPump Export Performance Issues (Doc ID 1935743.1)
shows that jobs have high waits kind "cell multiblock physical read "
specially high waits in one query with NESTED TABLES:
SELECT NVL((SELECT /*+ FULL(T) NESTED_TABLE_GET_REFS NO_PARALLEL */ :"SYS_B_0" FROM "
:"SYS_B_2") FROM SYS.DUAL
Same thing in hanganalyze analysis.
Symptoms
Customer is running an export via EXPDP and the process is getting stuck.
The export <SCHEMA_NAME> schema started using parallel 4 threads to export and it stuck for more than 16hrs at last stage,
as it was stuck customer cancelled and cleared job and initiated the expdp of schema again with thread one, it also stuck at the final stage for more than 10hrs.
LOG file example shows:
Export: Release 12.2.0.1.0 - Production on Wed Mar 20 18:40:17 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXP_DIR schemas=<SCHEMA_NAME> dumpfile=exp%U.dmp parallel=1 logfile=exp.log exclude=statistics cluster=n
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
,...
. . exported "<SCHEMA_NAME>"."<TABLE_NAME_STUCK>" 678.9 MB 20370048 rows
....
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/mnt/NFSMigration/daily_exports/maxdb/exp.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 21 07:26:49 2024 elapsed 0 12:46:28 <<<<<<<---NOTE
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 |