Import slows down to a crawl

Sun Solaris 2.8, Oracle 8.1.6.3.0

I am trying to run Import of a single table on a test server where nothing
else is running - this is the only database on this server and I am the only user. It starts at a reasonable rate importing something like
4000 rows per minute (the table contains two BLOB columns with each BLOB around 4K), runs like that for 1 hours 30 minutes, and then starts slowing dows to something like 1/50 of its initial rate.
1. To give you some idea about the numbers: - Export contains approx. 400,000 rows, it consists of two files: one 2 GB and second 1.4 GB.
- Each row is approx 8K

2. From V$LOG_HISTORY I can see that at the beginning log switches were happening every 30 seconds but now they are happening every 30 min
(50 MB redo logs, noarchivelog mode, 6 redo log groups, redo buffer
800K)

3. "sar" utility shows that at the beginning CPU usage was 25% and IOwaits 40%,
now CPU usage is 0% and IOwaits 50%.

4. V$SESSION_EVENT shows that the main event is "SQL*Net message from client"
(around 13,000 seconds, i.e. more than 3 hours) and next is "direct
path write" - 600 seconds. In other words it is "imp" utility itself that is holding things up - but why? What prevents it from reading?

5. When I run "top" it shows that "imp" runs with 1% CPU usage and corresponding "oracleSID" process 0.9%.

6. Import is being run with
buffer=10000000
commit=n
indexes=n

Because of "commit=n" Import is generating large transaction (around 3 GB),
rollback segment should be able to accomodate it without problems. For table with BLOB column the only alternative is to commit after every row (commit=y).

7. I also tried "truss -p <pid of imp>". It bunches of "read" and "write" system calls, with big pause (several seconds) after each bunch.

Any idea where else to look?

Thanks

Sev Received on Mon Apr 05 2004 - 07:19:12 CDT