Data Pump Import is Slow While Creating Views, Or View Creation Outside of Data Pump is Slow, Taking Almost Exactly Two Minutes Per View (Doc ID 2860305.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

During view creation either from Data Pump or any other context such as SQL*Plus, view creation is slow. When using SQL Trace and formatting the output with tkprof, the following top statement is observed:

ALTER VIEW "USER1"."MYVIEW" COMPILE

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 60.06 0 0 0 0
Execute 1 0.00 60.06 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 120.13 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 631 () (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
single-task message 1 60.06 60.06

Cause

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