Datapump DB Link Import Is Very Slow at Estimate Phase When PARALLEL_MAX_SERVERS>0 (Doc ID 392689.1)

Last updated on FEBRUARY 08, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.6 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 23-SEP-2011***

Symptoms

Database import via database link is extremely slow when PARALLEL_MAX_SERVERS > 0 is enabled in the source database. Actually, the situation is caused by the estimate phase itself and not by the actual import.

It could be that the same occurs only for the export operation.

Reproducing test case:

connect test/test@SOURCE
drop table test;
create table test
(
   n number,
   c varchar2(20),
   d date,
   t timestamp
) tablespace users
partition by range(n)
(
   partition test1 values less than (10),
   partition test2 values less than (20),
   partition test3 values less than (30),
   partition testmax values less than(maxvalue)
);
insert into test.test values(1,'aaaaaaaaaaaaaaaaaaa', sysdate, sysdate);

declare
  i number;
begin
  for i in 1..23 loop
    insert into test.test select * from test.test;
    commit;
  end loop;
end;
/
alter table test.test parallel 4;

-- prepare DB link and directory for import in target database and test it

sqlplus system/<password>
create database link src connect to system identified by <password> using 'SRC';
select username from user_users@src;
create directory dpump_dir as '/home/oracle/expdp';
grant read, write on directory dpump_dir to system;

-- perform network import

#> impdp system/<password> LOGFILE=implink.log DIRECTORY=dpump_dir FULL=Y NETWORK_LINK=SRC PARALLEL=4

-- monitor select statements in SOURCE database

select s.username, a.sql_text, s.status
from   v$session s, v$sqlarea a
where  s.username is not null and
       s.sql_address = a.address;

and you will see more PX sessions executing long running statement

SELECT /*+ NESTED_TABLE_GET_REFS */ 0 FROM "TEST"."TEST" "TEST"

This statement is executed during estimation of export size prior exporting of real data. It consumes unacceptable amount of time. PX slaves are waiting on direct path io - doing full table scans. Using ESTIMATE=STATISTICS has no effect.

The event 10046 at level 12 trace file would show lots of waits on "PX qref latch" event for:

PARSING IN CURSOR #29 len=63 dep=0 uid=5 oct=3 lid=5 tim=1126899056543630 hv=1825333222
ad='2bfe6060'
SELECT /*+ NESTED_TABLE_GET_REFS */ 0 FROM "TEST"."TEST" "TEST"

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms