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 .
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:
- Defragment the table’s rows with a CTAS or move.
- Use expdp with access_method=external_table
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