Oracle: Slow Data Import With Auto Extend Data File

Product: Oracle Database
Version: 10.x - 11.2 or later

Many developers or junior DBA may over look Oracle database's auto extend default configuration when they create the tablespace, or data file.

It become a critical performance bottleneck when user need to import data from dump file.

Oracle has default of 64 KB (it depends on database block size though) for the dbf file, which will slow down the database import, or table/index population when the dbf file needs to extend and grow

Use following query, and look for following 2 events to detect this bottleneck:
1. control file sequential read
2. Data file init write

select sid, event, total_waits, time_waited from v$session_event
where sid in (select sid from v$session where username='&username');

Replace &username with the user that you would like to monitor

Solution
Use following command to make the dbf file auto grow 1 MB each time

alter database datafile '&filename' autoextend on next 1m;

Use following command to make the dbf file auto grow 5 MB each time

alter database datafile '&filename' autoextend on next 5m;

Use following command to make the dbf file auto grow 300 MB each time

alter database datafile '&filename' autoextend on next 300m;

Kindly donate CAD$12 to appreciate my sharing of intellectual property, experience, research, and writing time