Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Import slows down to a crawl

Re: Import slows down to a crawl

From: NetComrade <>
Date: Mon, 05 Apr 2004 20:51:11 GMT
Message-ID: <4071c66f.1482621787@localhost>

Check this thread:*oracle*%26as_uauthors%3Dnetcomrade%26lr%3D%26hl%3Den

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

>Sun Solaris 2.8, Oracle
>I am trying to run Import of a single table on a test server where
>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
>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
>(50 MB redo logs, noarchivelog mode, 6 redo log groups, redo buffer
>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
>(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
>Because of "commit=n" Import is generating large transaction (around 3
>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
>Any idea where else to look?

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

Original text of this message