1

I've a impdp job on Oracle 19c always hanging with 100% CPU (ora_dw00_orclcd process) after the message below:

Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Elaborazione dell'object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Elaborazione dell'object type SCHEMA_EXPORT/VIEW/VIEW

Very strange for me is the output of select NAME,SQL_TEXT from dba_resumable;:

SYSTEM.SYS_IMPORT_SCHEMA_01.1   (null)
SYSTEM.SYS_IMPORT_SCHEMA_01     BEGIN :1 := sys.kupc$que_int.receive(:2); END;

It's a datapump exported from a 11.2.0.1 oracle database with COMPRESSION=ALL and not Full (SCHEMAS=MYSCHEMA1 MYSCHEMA2 MYSCHEMA3)

Any hints?

EDIT2:

top output is:

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                                  
  6313 oracle    20   0 2669888   1,3g 744812 R  91,7 23,9  81:32.14 ora_dw00_ORCLCDB

And this query to find running statement returns null

select
s.username su,
substr(sa.sql_text,1,540) txt,
s.event,
s.SID
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=6313;
"SU"    "TXT"   "EVENT" "SID"
"SYSTEM"    ""  "db file sequential read"   276

Impdp status after 18 hours:

Import> status

Job: SYS_IMPORT_SCHEMA_01
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 8,481,046,744
  Percent Done: 14
  Current Parallelism: 2
  Job Error Count: 0
  Job heartbeat: 904
  Dump File: /u01/DMP/my.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: ORCLCDB
  Host name: lnx-db
  Object start time: Wednesday, 29 April, 2020 14:48:35
  Object status at: Wednesday, 29 April, 2020 14:48:35
  Process Name: DW00
  State: EXECUTING
  Object Schema: MYSCHEMA
  Object Name: V_MYVIEW_xxx
  Object Type: SCHEMA_EXPORT/VIEW/VIEW
  Completed Objects: 80
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: ORCLCDB
  Host name: lnx-db
  Object start time: Wednesday, 29 April, 2020 14:48:13
  Object status at: Wednesday, 29 April, 2020 14:48:31
  Process Name: DW01
  State: WORK WAITING
4
  • Is it possible for you to see what SQL is currently being executed? Depending on your database edition and license options there are various options to get that information. As a long shot - when something burns CPU I always suspect bad statistics. Could you try to gather dictionary and fixed object statistics in the database before starting the import? For troubleshooting it would be great if we can get the current SQL. Regards, Daniel Apr 29, 2020 at 10:25
  • I think you pointed me in the right direction because meanwhile I've imported a very tiny schema flawlessly. I'll start again the huge impdp and check which sql is running with high CPU.
    – sgargel
    Apr 29, 2020 at 11:17
  • What is the size of the impdp file ? What is the size of the source schema ? Did you find anything in the alert log ? If statspack is installed can you take 5 minutes snaphots and run the report - (statspack does not require additional license in any edition but must be installed manually). You could also enable SQL trace on the impdp database session with DBMS_MONITOR (but this can generate very quickly a lot of data).
    – pifor
    Apr 29, 2020 at 15:04
  • You could use SQL Developer instance viewer to have graphical view of the database instance activity: thatjeffsmith.com/archive/2017/01/…
    – pifor
    Apr 29, 2020 at 15:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.