Symptoms

Datapump takes a long time to export a small table, waiting for db file sequential read events many more times than there are blocks in the table.
The table doesn’t have any LOBs.

This problem has been seen in 10.2, 11.2 and 12.1. I couldn’t find a bug for this; the closest to an acknowledgement of the problem is MOS note 1086414.1 .

Cause

The problematic table has a high percentage of migrated rows and Datapump is using the direct path method to export the table, possibly because of the table’s small size.

A trace will show that direct path reads are used to get the first table blocks, but then each migrated row will be fetched by executing a sequential block read. Blocks will be read multiple times, once for each row migrated to it. Subsequent reads are not satisfied from cache, so each migrated row results in a physical block read!

A migrated row has no Header flag, just the First and Last flags in the trace file for a block dump, eg:

fb: —-FL–

Workarounds

  1. Defragment the table’s rows with a CTAS or move.
  2. Use expdp with access_method=external_table

Test Steps

The problem can be replicated by following these steps:

create table test_tab (col1 number, col2 varchar2(10));
begin
for i in 1..1000000
loop
insert into test_tab values (i,null);
end loop;
commit;
update test_tab set col2='xxxxx';
commit;
end;
/
@?/rdbms/admin/utlchain
analyze table test_tab list chained rows;
select count(1) from chained_rows;

expdp / directory=DATA_PUMP_DIR tables=test_tab dumpfile=slow.dmp

expdp / directory=DATA_PUMP_DIR tables=test_tab access_method=external_table dumpfile=fast.dmp
Advertisements

One thought on “Row Migration vs Datapump

  1. Thanks for sharing!

    I have also faced similar behavior but in simple query. The segment of a table was around 4 Gb’s but the table itself had only 1000 entries. So, the simplest query like “select * from table;” took around 5 minutes with lots of db file sequential read; event in trace. The issue was resolved the same way by dropping HWM via moving table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s