Re: Import slows down to a crawl

Check this thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=ab810584.0205180205.451a1ef4%40posting.google.com&rnum=1&prev=/groups%3Fas_q%3Dlob%2520import%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3D*oracle*%26as_uauthors%3Dnetcomrade%26lr%3D%26hl%3Den

On 5 Apr 2004 05:19:12 -0700, vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote:

>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

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Mon Apr 05 2004 - 15:51:11 CDT