Re: Import slows down to a crawl

"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message news:4f7d504c.0404050419.22e1e31_at_posting.google.com...
> 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

Try: COMMIT=Y

Cheers Chris Received on Mon Apr 05 2004 - 08:01:12 CDT